pg_sift 3.6.2: a sift_col index with key_cut and an order array no longer loses rows for a range or an equality on the order-array column through Bitmap and plain Index Scans (a wrong result since 3.5.0). Update with the files of this release and restart the server; no ALTER EXTENSION step, no REINDEX.

Highlights

  • Fixed: wrong result (rows missing) for a range or an equality on a column with an order array, through a Bitmap Index Scan or an Index Scan. An index with key_cut (set in the options, or chosen by auto: the options then show key_cut=auto: column) and a global order array (order_array_columns; auto gives one to dates and timestamps) answered d BETWEEN a AND b, d >= x, d < x and d = x, alone or with deleted_at IS NULL, a key list or another range, from the addresses of the rows in the array when they were at most 16,384 in a segment. It named the unit of each address by the superblock of the heap page, but the units of a key-cut segment are cut by the key and not by pages, so it left the units that hold the rows unread. On the test table 6 of 11 statements returned too few rows, 35% to 55% of them; on a 450,000-row table 25% of a month’s rows. Custom Scan (SiftAgg) (count(*), GROUP BY, top-N), ranges of more than 16,384 rows in a segment, indexes without key_cut and indexes with order_array_columns = 'off' were exact. The range code was added in 3.5.0; 3.6.0 and 3.6.1 were measured, 3.5.x was not. The fix is one condition in prune_range_tids (src/col/scan.c): the addresses of a range are not used in a key-cut segment, whose units are read as for a range too large for the array. Test col_gorder_keyed.

Who is affected and what to do

Affected: a sift_col index with both a key_cut (a value of the option other than off, also an index whose option was set to off but not rebuilt yet) and an order array (order_array_columns not off), read by a statement that returns rows through a Bitmap Index Scan or an Index Scan on that index, with a range or an equality on the column of the array that matches at most 16,384 rows. Not affected: Custom Scan (SiftAgg), index-only scans, ORDER BY .. LIMIT read by the array, lookups of the key columns, and the indexes above.

How to detect (read-only; no object is created): list the exposed indexes, then compare one range through the index with the same range through the heap (replace the table, column and dates):

SELECT i.indexrelid::regclass AS index, c.reloptions
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid JOIN pg_am a ON a.oid = c.relam
WHERE a.amname = 'sift_col'
  AND EXISTS (SELECT 1 FROM unnest(c.reloptions) o WHERE o ~ '^key_cut=' AND o !~ '^key_cut=(off|none)')
  AND NOT EXISTS (SELECT 1 FROM unnest(c.reloptions) o WHERE o ~ '^order_array_columns=off');

BEGIN READ ONLY;
SET LOCAL enable_seqscan = off; SET LOCAL enable_indexscan = off; SET LOCAL enable_indexonlyscan = off; SET LOCAL enable_bitmapscan = on;
EXPLAIN (COSTS OFF) SELECT count(*) FROM (SELECT id FROM t WHERE d >= '2025-09-01' AND d < '2025-09-08' AND deleted_at IS NULL OFFSET 0) s;  -- Bitmap Index Scan on the sift_col index
SELECT count(*) FROM (SELECT id FROM t WHERE d >= '2025-09-01' AND d < '2025-09-08' AND deleted_at IS NULL OFFSET 0) s;                       -- through the index
SET LOCAL enable_bitmapscan = off; SET LOCAL enable_seqscan = on;
SELECT count(*) FROM (SELECT id FROM t WHERE d >= '2025-09-01' AND d < '2025-09-08' AND deleted_at IS NULL OFFSET 0) s;                       -- through the heap
COMMIT;

Two different counts show the fault; equal counts of one range prove nothing, so try several ranges of a few thousand rows. (OFFSET 0 keeps a plain count(*) from being answered by Custom Scan (SiftAgg), which was exact.)

Until the update: SET sift.enable_col_scan = off for the role or the database (PostgreSQL 17 and 18: the planner builds no bitmap and no index scan on sift_col indexes and Custom Scan (SiftAgg) keeps working; on PostgreSQL 19 it removes the bitmap paths only). SET enable_bitmapscan = off is not enough, the plain Index Scan loses rows too. The statements that used the index scans fall back to the other indexes or a sequential scan. The permanent alternatives are ALTER INDEX .. SET (order_array_columns = 'off') or SET (key_cut = 'off') and REINDEX, which this release makes unnecessary.

  • No format change, no REINDEX: the stored index is valid; the fault is in the scan that reads it. Install the files and restart the server (a standby too), or reconnect every pool: a backend that already loaded the old library keeps it until its connection ends.
  • Nothing else changes: plans, answers and formats are those of 3.6.1.

Regression tests

The test col_gorder_keyed fails on a build of the tag v3.6.1 and passes on this release (table: docs/releases/3.6.2-REGRESSIONS.md); PostgreSQL 17, 18 and 19 are those of the Full-scope run of the release.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A drop-in replacement of 3.6.1: column format minors 3 to 32 are read, extension SQL 3.6 (ALTER EXTENSION pg_sift UPDATE has nothing to do and there is no pg_sift--3.6--3.6.2.sql), no setting changes its default. The SQLite and MariaDB extensions: no change since 3.6.1.

Packages

16 packages are attached below (SHA256SUMS covers every asset): x86-64 and arm64 .deb and .rpm for PostgreSQL 17, 18 and 19 (12), and the Amazon Linux 2023 .rpm for PostgreSQL 17 and 18 on both architectures (4); other versions build from source with make USE_PGXS=1. The packages depend on liblz4 and libzstd. The 19 ones are built and tested against PGDG’s 19~rc1. Each package was installed in fresh containers of its architecture, where CREATE EXTENSION, an update from 1.0, a sift index and its queries ran: the .deb in Debian 12, Debian 13, Ubuntu 22.04 and Ubuntu 24.04 (built on Ubuntu 22.04, glibc 2.35), the .rpm in Rocky 9, Alma 9 and Alma 10, the Amazon Linux 2023 one in amazonlinux:2023 (the arm64 ones on a hosted arm64 runner). The arm64 libraries are built for plain ARMv8-A and NEON, and the smoke test of each arm64 package runs scripts/check-aarch64-baseline.sh on the installed library.

  • Debian 12 and 13, Ubuntu 22.04 and 24.04 (PGDG postgresql-N, N = 17, 18 or 19): sudo apt install ./postgresql-N-pg-sift_3.6.2-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-3.6.2-1.el9.x86_64.rpm (.aarch64.rpm on arm64)
  • Amazon Linux 2023 (the distribution’s postgresqlN-server, N = 17 or 18): sudo dnf install ./pg_sift_N-3.6.2-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

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

  • No change: the changes of this release are in the PostgreSQL column index, the aggregate planner and the packages, 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 to 3.6.1 open as they are, nothing to rebuild.

Download sift-sqlite-3.6.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.6.2 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.6.2 release: no change to the SQL files.

  • No change: the changes of this release are in the PostgreSQL column index, the aggregate planner and the packages, 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 to 3.6.1 opens as it is, nothing to rebuild.

Download sift-mariadb-3.6.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.