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): ```sql 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](https://github.com/brauliobo/pg_sift/blob/v3.6.2/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](https://github.com/brauliobo/pg_sift/blob/v3.6.2/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.6.2/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:` 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//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.6.2/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.6.2/mariadb/CHANGELOG.md).