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 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.

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. Every change: 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. Every change: mariadb/CHANGELOG.md.