pg_sift 3.5.0: point lookups by a foreign key or a unique id read one record of a segment or a few units, a top-N and a count over a range start at the range, the column index picks more of its structures from the statistics of each column, and `sift_table` has the pre-aggregates and text tests of `sift_col`. Update with `ALTER EXTENSION pg_sift UPDATE` in every database; no `REINDEX` is required. ## Highlights - **Lookups by key.** `WHERE key = x` (an `IN` list, a range, `GROUP BY key`) with `count`, `sum`, `min` and `max` of a numeric, date or timestamp column reads one record of each segment through a key table (column format minor 22, `key_table`, on by default): 300 keys over 900,000 rows, `count(*), sum(amount), min(ts), max(ts) WHERE key = 17` 5.20 ms before, 0.107 ms now (100,000 rows: 0.49 and 0.054 ms); a `sift_table` keeps one too (`sift.table_key_table`). A unique `*_id` column keeps key lists, lists of runs of consecutive keys (minor 23) or a bloom filter of up to 5% of the unit, so a lookup by id, an `IN` list of up to 65,536 keys or a range of ids reads the few units that hold the keys. - **Counts over a range or a prefix.** `count(*) WHERE col BETWEEN a AND b` on a column with a global order counts from the entries between the bounds: 800,000 rows, 7,900 in the range 8.7M to 1.3M instructions (0.9 to 0.2 ms). `count(*) WHERE txt LIKE 'abc%'` (or `=`, `<`, `>` in the C collation) is read from two places of the dictionary (`count_columns`, minor 24): 1,000,000 unique strings, 4,012 matching, 5.3 ms to 0.14 ms (a b-tree 0.43 ms). `count(*) WHERE col IS [NOT] NULL` reads the zone counts of the units. - **Scattered columns and top-N.** The build picks `order_array_columns` for scattered columns (1,000 or more distinct values, correlation with the heap order under 0.99, at most two, at most 25% of the index): a range of 790 of 800,000 rows hands the heap its addresses from the sorted keys, 18.4M to 2.4M instructions. An `ORDER BY .. LIMIT n` over a sorted run starts at the first entry of the range (`WHERE ts >= x ORDER BY ts LIMIT 10` over 3,000,000 rows 18.7 to 8.4 million instructions; the page after a key, `LIMIT 50`, 63.6 to 10.4 million) and reads a descending window from its best end (2.67 to 0.65 million instructions). - **The scan wins small statements.** The start of a scan is priced from what it does: on 100,000 to 300,000 rows (PostgreSQL 18, an x86-64 host, 1,000 groups, warm) `WHERE grp = x ORDER BY ts DESC LIMIT 10` 0.23 to 0.10 ms, a keyset page 0.28 to 0.10 ms, `max(ts) WHERE grp = x` 0.36 to 0.10 ms. - **`count(DISTINCT)` in the workers.** `timestamptz`, `float4`, `float8` and `uuid` (1,000,000 rows, 8 groups, 2 workers): `timestamptz` 675 to 201 ms, `float8` 656 to 242 ms, `uuid` 1,542 to 582 ms. Exact, as before. - **`sift_table` catches up with `sift_col`.** Min and max per group in the cube (`GROUP BY g` with `min` and `max` over 500,000 rows 2.860 to 0.140 ms), key lists and sorted runs of the unit, rollups of declared jsonb lists, text tests on the encoded strings (a group-by filtered by `s = 'x'` over a million rows 44 to 15 million instructions, by a prefix 448 to 59), partitioned tables as one `SiftAgg` source; `docs/COL-TAM.md` section 13 lists what works. - **Planner.** Prices measured per row and per group of aggregates that run on PostgreSQL's own transition functions, the dictionary of a wide column, the workers' start, lookups of a few keys and the choice between a shared CTE and its inline form (`sift.agg_verdict_cache`); a forced scan now gets workers. - **Cheaper opens and scans.** Opening the head of a unit of a churned table 86,000 to 40,000 instructions; an ungrouped aggregate the pre-aggregates do not answer counts and sums a chunk in one pass; SIMD kernels for x86-64 (AVX2) and arm64 (NEON) have an automated check of every level. ## What a 3.4 user must know - **No `REINDEX` is required.** `ALTER EXTENSION pg_sift UPDATE` runs `pg_sift--3.4--3.5.sql`, which moves the version, adds `sift.json_build_object(VARIADIC "any")` and replaces `sift.upgrade_commands()`; no other SQL object changes. `sift.version()` says `3.5`; the exact build is in `pg_get_loaded_modules()` on PostgreSQL 18 (`3.5.0+`). - **New column format minors 22 to 24, written after the update** (key table, lists of runs, `count_columns`). 3.5 reads every segment of 3.4. A 3.4 library refuses the newer ones with a clear error (`has a segment of column format 1.22, this build reads 1.3 to 1.21`); going back is not a supported path, `scripts/upgrade-check.sh --to 3.4` lists what it cannot read. - **Defaults chosen by benefit against cost, per column.** A structure is built by default where its measured benefit (a speedup on a common class, at least 100,000 instructions per query) outweighs its index growth and its write cost (growth at most 25%), and the statistics of the column decide. Changed in 3.5: `rollup_dims = 'auto'` (the pair of low-cardinality columns whose combinations fit a unit; `'off'` writes none), `order_array_columns = 'auto'` (scattered columns), `key_columns = 'auto'` (a unique `*_id` column, bloom filter up to 5% of the unit), `count_columns` (text leaves read in place), `sift.table_dict_lookup` (a `sift_table` stores the order of the strings of a dictionary of 1,024 strings or more, so the first test of a string in a new backend reads it in place), `key_table` and `sift.table_key_table`. Each `CREATE INDEX` that chooses says so in a NOTICE with the columns and the MB; docs/OPTIONS.md has gain, growth, write cost and the switch to turn each off, measured for every option. - **Plans can change:** the cost of opening a scan and of the aggregates moved; `sift.explain_why(query)` says why a statement took or refused the scan, and `sift.enable_agg = off` restores PostgreSQL's plan. ## Fixes - **A backend crash in the stock plan of 3.4.0, 3.4.1 and 3.4.2.** A merge join over partitions with a runtime key could plan a bitmap heap path without the outer relations of the index path under it (`add_address_paths`); the executor died in `CheckExprStillValid` (SIGSEGV). Fixed; there is no 3.4.3, upgrade to 3.5.0. - A partitioned table whose partitions did not choose the same rollup of the rows (`rollup_dims = 'auto'` picks it from each partition's data, so a partition attached later with few rows has none) lost the single `Custom Scan (SiftAgg)` over its partitions and ran the normal plan; the scan keeps the rollups every partition has and reads the others by their rows. - `VACUUM` of a `sift_table` with a unit of more than 65,000 rows failed with `could not read blocks N..N` once rows were deleted from every such unit; it finishes. - A `min()` or `max()` planned as a `LIMIT 1` sub-select with `sift.explain_refusals` on crashed the next plan of the session. - `ORDER BY k, expr LIMIT n` over a range of the sort key failed with "sift_col sorted run is corrupted"; `CREATE INDEX` with `hll_columns` and a leaf that is NULL in nearly every row failed with "segment 1 is corrupted"; `sift.col_verify` could report a false difference of a unit's exceptions; a merge of an index with `key_columns = 'auto'` dropped the key lists and the key table of a column the cube holds. - With `sift.enable_agg = off` the normal plan is the 3.3 plan again (3.4.0 planned another one for 92 of 156 statements of a replay corpus). - `sift.upgrade_check('3.3')` did not list an index with a global order (minor 21) as unreadable by 3.3; the build against PostgreSQL 17, 18, 19 and GCC 11 (el9) is clean. ## Compatibility - PostgreSQL 17, 18 and 19; the packages below are for PostgreSQL 18 (other majors build from source with `make USE_PGXS=1`). Column format minors 3 to 24 are read; 3.4.x reads up to 21. SQLite and MariaDB extensions: no change since 3.4.2. ## 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.5.0-1_amd64.deb` - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresql18-server`): `sudo dnf install ./pg_sift_18-3.5.0-1.el9.x86_64.rpm` - Arch Linux: AUR `pg_sift`. Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.5.0/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 3.5.0 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.5.0 release: no change to the SQL interface or the shared format. - **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.5.0-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.5.0/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.5.0/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 3.5.0 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.5.0 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:` 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.5.0-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.5.0/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.5.0/mariadb/CHANGELOG.md).