pg_sift 3.4.2: fixes to plan choices for a lookup of a few keys and for a numeric column read by workers, a faster top-N over a sorted run with a filter, a cheaper heap check of a table no VACUUM covers, a parallel plan with a `SiftAggRows` sub-plan that no longer errors in the worker, the build against PostgreSQL 19, `UPDATE` of a row of a `sift_table` that is the referencing side of a foreign key, and `CREATE INDEX` of a `sift_col` index with `hll_columns` over a leaf that is almost always NULL. Library only: install the new files and restart the server (or reconnect, when the library is preloaded per session); the extension SQL does not change (it stays 3.4), so there is no `ALTER EXTENSION` step and no `REINDEX`.

## Highlights

- **Fixed: the price of a lookup of keys.** A CTE of the keys a request names (`WHERE id IN (7, 12) AND id IN (SELECT ..)`) was estimated at the product of two selectivities, so a hundred keys joined to a hundred came out as one row and every plan that keeps the CTE was priced for one key. It is estimated at what the list owns of the column of the indexed table it joins (3,000,000 rows, one key that owns 6% of them: the normal plan took 3,400 ms where the scan took 363 ms, and 100 keys 3,159 ms where the scan took 397 ms; a key of the mean size stays at one row and keeps the normal plan). A lookup built by a join of two lists (ten ids semi-joined to forty) no longer counts fewer keys than the smaller list: PostgreSQL takes that join to one or two rows, and the few-keys rule read it as a request for a few keys and kept a normal plan that read the rows of the largest of the ten ids through an index nested loop. Tests `agg_cte_key_list` and `agg_few_keys_pick`. On an index cut by the key (`key_cut`) the keys of a few-keys lookup leave a handful of units (3,000,000 rows, 441 units: one key reads 0.09 units on the cut index, 35 on a plain one), so the scan reads them in a millisecond or two while the btree probes cost the time of the rows of the largest keys; the few-keys rule now applies only when the keys leave more than 8 units or at least half of the index, and counts the keys as PostgreSQL expects them. One key, normal plan then scan, warm medians with 4 workers: largest key 16.0 ms to 1.8 ms, a typical key 5.2 to 1.4 ms, a monthly-dimension shape 62.7 to 10.7 ms; the smallest keys (under about 1,000 rows) lose 0.3 to 0.8 ms (0.38 to 0.99 ms); an index not cut by the key keeps its choice (30 cells, none slower). The lookups that 3.4.1 sent to the btree probes on such an index, and the splits of a shared CTE that followed, go back to the plans they had in 3.4.0.
- **Fixed: a numeric column without a precision read by workers.** A worker inflates (zstd) and indexes the dictionary of images of every unit it takes, which the price of the parallel scan left out, so a column of almost unique values scaled to 1.7x with three processes where PostgreSQL's plan scales to 3.4x and the planner took the slower scan. 2,000,000 rows, `GROUP BY` 6 groups, `count`, `sum`, `avg`, default costs, milliseconds: 2 workers allowed 103 in 3.4.1, 79 now (PostgreSQL's plan 79); 4 workers 91, now 62; 8 workers 86, now 62; 500,000 rows 27.6, 28.4 and 25.8 ms, now 25.6, 24.0 and 24.2. Without workers the scan is still taken (135 against 236 ms).
- **Fixed: a top-N over a sorted run with a filter.** `ORDER BY .. LIMIT n` over an index with `order_columns` read the sort key of every entry of a window of the run, whether the row passed the filter or not, at 83 instructions a value where decoding the unit's column costs about 8. `WHERE ts >= x ORDER BY ts LIMIT 10` over 1,000,000 rows of a scattered timestamp: 113.8 million instructions and 14.7 ms in 3.4.1 (10 ms in 3.4.0), 56.4 million and 5.8 ms now (3.3.0: 59.2 million and 6.4 ms; the plain index 17.8 million and 2.6 ms); 100,000 rows 11.7 million instructions, now 6.0 million.
- **Improved: the heap check of a table no VACUUM covers.** The check of the rows of a page that is not all-visible (a table loaded and not vacuumed, or one an old transaction holds back) built an item pointer and called a test of it for every row, and called `TransactionIdPrecedes` for each: it tests a row inline now. The grouped sum of 1,000,000 rows with 1% dead rows and no `VACUUM` (`SELECT k, sum(v), count(*) .. GROUP BY k`, PostgreSQL 18, user instructions of the backend) runs 164.5 million instructions where 3.4.1 ran 206.3 million (3.3.0: 180.2 million); `SELECT count(*), sum(v), sum(m)` 197.1 million where 3.4.1 ran 238.9 million. A table with its pages all-visible does not check its rows and is unchanged; no format change, no setting.
- **Fixed: a parallel plan with a `SiftAggRows` sub-plan.** A statement whose scan has workers and whose filter starts a sub-plan, beside a sub-plan that holds a `SiftAggRows` node (a CTE of per-row aggregates grouped with `count(DISTINCT)`), failed in the worker with `ExtensibleNodeMethods "SiftAggRows" was not registered`, in every version since the node exists. The worker registers the methods of the node now. Test `agg_rows`.
- **Fixed: the build against PostgreSQL 19.** The planner's read of a lookup's plan down to its inputs named `UpperUniquePath`, which PostgreSQL 19 folded into `UniquePath`, so the extension did not compile there; it builds against PostgreSQL 19 again.
- **Fixed: `UPDATE` of a `sift_table` row under a foreign key.** An `UPDATE` of a row of a `sift_table` that is the referencing table of a foreign key failed with "don't have transaction information for this type of tuple"; the slot of the table now answers from the `xmin` of the row. Test `tbl_foreign_keys` (`INSERT`, `UPDATE` and `DELETE` of either side, with `NO ACTION`, `CASCADE` and `SET NULL`, on heap and `sift_table` pairs, outcomes compared).
- **Fixed: `hll_columns` over a leaf that is almost always NULL.** A segment with exception cubes (a soft-delete stamp is NULL in nearly every row) and sketches but no exception element blobs got the sketches' offset advanced by a directory of blobs that was never written, so the section check refused the segment: `CREATE INDEX` failed, and an index created before its rows had segments written by `INSERT` refused `VACUUM` and every scan of them. Test `col_approx`.

## Who is affected and what to do

- **Lookups of keys, top-N over a sorted run, numeric column:** plans and times only; no answer changes. Install 3.4.2; nothing else.
- **`sift_table` under a foreign key and `hll_columns` with a nullable stamp:** a statement that failed with an error works now. An index of `hll_columns` whose build failed is created by repeating the `CREATE INDEX`; one that refuses `VACUUM` is rebuilt with `REINDEX`.

## Known limits of 3.4.2

- A numeric column without a precision of 10,000,000 rows still takes the scan with workers (4 workers: 551 ms against 278 ms for PostgreSQL's plan; no workers 1,662 against 1,226 ms): its cost per row is twice that of 2,000,000 rows, which the price does not follow. `SET sift.enable_agg = off` for the statement restores PostgreSQL's plan.
- One key of a very large entity takes the normal plan, and lookups of 10 keys or more keep the charge (3.4.1 limits): a choice made at run time is 3.5 work.

## Compatibility

- PostgreSQL 17, 18 and 19; the packages below are for PostgreSQL 18 (other majors build from source with `make USE_PGXS=1`). A drop-in replacement of 3.4.1: extension SQL 3.4, no change of the BM25, `sift_col` or `sift_table` formats, no setting changes its default. Install the new files and restart the server; going back to 3.4.1 needs nothing. [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v3.4.2/docs/UPGRADING.md) has the steps.

## Packages

x86-64 packages for PostgreSQL 18 are attached below (`SHA256SUMS` covers every asset); other versions build from source with `make USE_PGXS=1`. The packages depend on `liblz4` and `libzstd`.

- Debian 13, Ubuntu 24.04 (PGDG `postgresql-18`): `sudo apt install ./postgresql-18-pg-sift_3.4.2-1_amd64.deb`
- RHEL, Rocky, Alma 9 and 10 (PGDG `postgresql18-server`): `sudo dnf install ./pg_sift_18-3.4.2-1.el9.x86_64.rpm`
- Arch Linux: AUR `pg_sift`.

Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.4.2/CHANGELOG.md).

## SQLite extension

pg_sift for SQLite 3.4.2 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.4.2 release: no change to the SQL interface or the shared core.

- **No change:** the fixes of this release are in the PostgreSQL column index, the aggregate planner and `sift_table`, which are not in this extension. No speed-up or slow-down is claimed.
- Needs SQLite 3.38 or newer; Linux x86-64, glibc 2.34 or newer. Built on Debian 12, loaded into the SQLite of Debian 12, Ubuntu 24.04 and AlmaLinux 10 before release.
- Storage format 2: tables of 3.4.0 open as they are, nothing to rebuild.

Download `sift-sqlite-3.4.2-linux-x86_64.tar.gz` (below, covered by `SHA256SUMS` with the packages), unpack it and `.load ./sift` from the `sqlite3`
shell (`sha256sum --ignore-missing -c SHA256SUMS`). Documentation: [sqlite/README.md](https://github.com/brauliobo/pg_sift/blob/v3.4.2/sqlite/README.md).
Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.4.2/sqlite/CHANGELOG.md).

## MariaDB extension

pg_sift for MariaDB 3.4.2 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.4.2 release: no change to the SQL files.

- **No change:** the fixes of this release are in the PostgreSQL column index, the aggregate planner and `sift_table`, which are not in this extension. No speed-up or slow-down is claimed.
- One library per MariaDB series, because it calls the server's own symbols and refuses to load into another series: 10.11, 11.4, 11.8, 12.1 and 13.0; Linux x86-64. Each was built in the stock `mariadb:<series>` image against that server's headers and loaded into a server of the same image before release. `BUILD-INFO` in the tarball lists the server version and the glibc each library needs; a server of another distribution or of a series not listed is built from source (`make -C mariadb`).
- Index format 2: an index of 3.4.0 opens as it is, nothing to rebuild.

Download `sift-mariadb-3.4.2-linux-x86_64.tar.gz` (below, covered by `SHA256SUMS` with the packages), unpack it, copy `lib/<series>/libsift_udf.so` of your server's series
(`SELECT VERSION()`) into its `plugin_dir`, then `SOURCE sql/functions.sql` once per server and `sql/sift.sql` once per database (`sha256sum --ignore-missing -c SHA256SUMS`).
Documentation: [mariadb/README.md](https://github.com/brauliobo/pg_sift/blob/v3.4.2/mariadb/README.md).
Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.4.2/mariadb/CHANGELOG.md).