pg_sift 3.4.0: the aggregate scan reaches more query shapes and runs them in parallel, a `sift_col` index cuts its units by a key when that pays and keeps answering from its pre-aggregates after churn, top-N gets sorted-run heads and optional order arrays, and `sift_table` closes gaps with `sift_col`. Update the files, restart, then `ALTER EXTENSION pg_sift UPDATE` in every database; no `REINDEX` is required.

## Highlights

- **Units cut by a key (`key_cut`, column format minor 17).** `CREATE INDEX` and `REINDEX` of a `sift_col` index cut the units by a key (or by a date or timestamp column) when a sample of the heap shows that zones cannot skip the units of that key: the first key-shaped column whose equality reads at least 8 times the units its rows fill and at least 64 units, no other leaf follows the heap order, the index has no `order_columns`, and the predicted index growth is at most 3%. Otherwise a `NOTICE` gives the units read, the predicted growth and the command. A lookup of one key value then reads 1 unit instead of all of a table (one value of 600: 1 of 100 units against 8 of 8). `sift.key_cut_decision(index)` says what a `REINDEX` would decide; `key_cut = 'off'` switches it off. A build takes 3.4 to 4.1 times the CPU of the superblock cut when the cut is taken; the memory of a build and of a merge is bounded by `maintenance_work_mem`.
- **Churned tables keep answering from pre-aggregates.** A unit with dead rows is answered from its stored counts and sums minus its dead rows (`sift.agg_dead_delta`, default 15%, 0 turns it off; no stored byte, no format change): after 60 days of churn at 20% a month the grouped sum of 3,000,000 rows goes from 21.6 to 15.0 ms. The overlays that churn leaves are cut by TID range into units of `sift.col_unit_floor` rows (default 2,048): 10,552 to 721 units, a key lookup 35.0 to 7.5 ms at 3,000,000 rows. `VACUUM` no longer moves the large segments of an index that is due for the automatic rewrite, which started the WAL budget of `sift.col_auto_merge_wal_pct` over, so the units stay answerable after every cycle.
- **Top-N.** A top-N for each key an enclosing query sets (`CROSS JOIN LATERAL (.. WHERE device = d.device ORDER BY ts DESC LIMIT 3)`) is one scan started over for each key: 20 devices over 1,000,000 readings take 1.1 ms where the normal plan takes 739 ms. The sorted runs of `order_columns` keep heads (minor 18, +0.6% to +2.4% of the index): `ORDER BY ts DESC LIMIT 1000` over 3,000,000 rows takes 2.6 ms, not 4.2 ms. `ORDER BY a, b LIMIT n` with only `a` in the index, a float key, `ORDER BY <aggregate> LIMIT n` (the groups that cannot be among the first n are dropped: 14.6 against 96.6 ms over 400,000 groups) and `OFFSET` take the scan. `order_array_columns` and `order_array_group` (opt-in, minor 21) keep a global order, so a page of 50 rows of a scattered timestamp reads 0.29 ms instead of 17 ms, at about 6 bytes a row for each array.
- **More shapes in the scan, and in parallel.** `LEFT JOIN`s from a calendar or a spine, hashed sub-plans (`x IN (SELECT ..)`), lookups that read a CTE or a function, and `count(DISTINCT)` of a joined column plan workers (1,600,000 rows, a preserved join: 522 to 79 ms with 8 workers). `EXISTS` and `NOT EXISTS` sub-plans, `LEFT JOIN LATERAL` aggregates, `mode()` and `percentile_cont`/`percentile_disc` on the histogram of a group, a comparison of two columns of one row, and the `sum`/`avg` of a decimal expression of up to 38 digits (exact in 128 bits: 19 ms where it took 200 ms at 400,000 rows) are scan shapes now. A `sum` or `avg` of a `numeric` column without a precision runs in the workers (1,000,000 rows, 16 groups, 2 workers: 50.3 to 23.3 ms while PostgreSQL's parallel plan takes 43.4; 10,000,000 rows 439 to 160 ms against 389). The lookup table of a join is loaded from the leaves of a `sift_col` index when one holds the columns (`sift.agg_lookup_columns`, on).
- **Planning.** The planner prices an IN list by the structure that tests it, the fixed cost of opening a scan is 100,000 operator costs (it was 210,000), a table with several `sift_col` indexes is planned through the cheapest one, and the choice between a shared CTE and its inline plan is kept in the shared memory of the server (`sift.verdict_cache_kb`, 256 kB) so a new backend plans the winning form only. `sift.agg_force` (off) takes the scan wherever it can be built, for diagnosis.
- **`sift_table`.** Integer filters run on the encoded chunks of its column groups, a unit that keeps few rows after the filters is read for those rows alone, a `uuid` column is a dictionary of its values, bulk `COPY` spends 12% fewer instructions, `UPDATE`s of the same keys in one transaction are 3 times faster, and the b-tree asks the table's tombstone log, not each row, before a page splits (40,000 updates of 2,000 rows of a 600,000-row table: 9.0 to 3.1 s; a heap table 0.7 s).
- **Index scans by default (`sift.enable_col_scan` is on).** A `sift_col` index with the `sift.col_*_ops` classes is a planner path (bitmap scan, Index Scan) priced against the b-trees and the sequential scan, so a table whose b-trees were dropped reads its point, range and `IN` lookups from the index and not by a sequential scan (1,000,000 rows: `id = x` 29.5 to 0.21 ms, `id IN (100 ids)` 43.4 to 0.62 ms, a range of 100 rows 32.8 to 0.31 ms, a range of 1,000 rows 40.6 to 2.9 ms). With the b-trees present the plans are the b-tree's (24 of 26 shapes identical at 100,000 and 1,000,000 rows), and a few `count(*)` shapes that the aggregate scan answered take the bitmap scan by cost, up to 0.16 ms slower. `SET sift.enable_col_scan = off` (or `postgresql.conf`) restores the earlier planner.
- **Pre-aggregates that keep more of the columns by default.** The byte budget of the cube of a unit counts what the cube holds (every measure of a kept dimension, with its minimum and maximum) and is 40% of the unit's encoded bytes (it was 2% of the first three measures), so an index of sixteen columns whose dimensions have 50 to 200 values answers a `GROUP BY` of any of them from the pre-aggregates: the last of eight dimensions of 50 values over 500,000 rows goes from 2.3 to 0.28 ms (the normal plan takes 63 ms). The price is index size: at 1,000,000 rows the index grows by 8.9% for the cube instead of 6.7% (16 columns of 50 values), 17.9% instead of 6.7% (16 columns of 100 values) and at most 41% (32 columns of 200 values); the build time stays within 0.1 s; the index of the store benchmark is the same file to the byte. A grouped `sum` or `avg` of a `float8` column is not answered from the cube (float sums are added row by row), whatever the budget. This is above the 3% growth of the default rule, chosen because the index size is not what these tables pay for. No format change: segments written after the update use the new budget, `REINDEX` or `sift.col_refresh(index)` rewrite the older ones.
- **Approximate distinct counts, opt-in.** `sift.approx_count_distinct(x)` returns a HyperLogLog estimate (relative standard error 0.81%); `hll_columns` stores a sketch for each unit (minor 20; opt-in: +23% of an index for four columns). `count(DISTINCT)` stays exact whatever the settings say.
- **Advice.** `sift.cluster_advice()` names `key_cut`, `sift.index_advice` names `preagg = 'auto'` and the columns that look rewritten often (an `UPDATE` of them is not a HOT update), and `CREATE INDEX` says in a `NOTICE` which foreign key columns the index does not hold. [docs/OPTIONS.md](https://github.com/brauliobo/pg_sift/blob/v3.4.0/docs/OPTIONS.md) has one entry for every option, setting and automatic behavior: what it buys and costs, why its default, how to turn it off, with the numbers.
- **arm64.** NEON kernels for the fused range filter over bit-packed values, run expansion and float4 keys of a decimal-scaled chunk (4096 values on a test host: 1750 to 313 ns for the range filter); the kernels that did not beat the scalar loop on that CPU stay scalar.

## What a 3.3 user must know

- **No `REINDEX` is required.** The 3.4 library reads every index of 3.3 as it is. `ALTER EXTENSION pg_sift UPDATE` runs `pg_sift--3.3--3.4.sql`: it adds `sift.approx_count_distinct`, replaces `sift.upgrade_commands()` and `sift.cluster_advice()` and comments `sift.suggest_paths()`; it changes no setting and no stored data. The library starts with the next backend (`session_preload_libraries` needs a reconnect, `shared_preload_libraries` a restart).
- **New column format minors, written only after the update.** `sift.col_format_minor` stays 15. Minor 17 (`key_cut`, and the TID-range cut of churned overlays), 18 (run heads of `order_columns`), 19 (pre-aggregates of the non-NULL rows of a unit whose leaf is NULL in nearly every row), 20 (`hll_columns`) and 21 (`order_array_columns`) are written for segments created by a build, a merge or a `REINDEX` after the update; the segments already there keep their minor, and both live in one index. **A 3.3 library refuses a segment of minor 17 or newer** with a `REINDEX` hint. To keep a 3.3 library able to read new writes, set `sift.col_format_minor = 14` for the server (`ALTER SYSTEM`; a session or role setting does not reach the background worker of `sift.col_auto_merge`) and `key_cut = 'off'` before the first `REINDEX`. `sift.upgrade_check('3.3')` and `scripts/upgrade-check.sh --to 3.3` list the blockers; [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v3.4.0/docs/UPGRADING.md) has the way back.
- **Automatic key cut.** The first `CREATE INDEX` or `REINDEX` of a `sift_col` index after the update may cut its units by a key (the `NOTICE` says so). A build that cuts takes 3.4 to 4.1 times the CPU of one that does not. `ALTER INDEX .. SET (key_cut = 'off')` before the `REINDEX` keeps the 3.3 layout.
- **Defaults that change plans or bytes:** `sift.enable_col_scan` on (was off: no `REINDEX`, no stored change; `off` restores the 3.3 plans), the cube budget 40% of a unit (was 2%: larger indexes, more `GROUP BY`s answered from the pre-aggregates; no setting, no format change), `sift.agg_dead_delta` 15 (new; 0 restores the scan of units with dead rows), `sift.col_unit_floor` 2,048 (new; 0 restores the superblock cut), `sift.agg_lookup_columns` on, `sift.verdict_cache_kb` 256 kB of shared memory. Statements of the shapes above change plans after the update; their rows do not (checked against the normal plan by the differential tests).
- **Plans of 3.3 can change.** The cost of opening a scan and the pricing of joins moved: a statement that kept PostgreSQL's plan in 3.3 may take the scan, and the other way. `sift.explain_why(query)` and `sift.explain_refusals` say why; `sift.enable_agg = off` restores the normal plan for a statement.

## Fixes

- **`count(DISTINCT d)` over a `date` column could return the number of months (or years), not days.** It happened when the column was not one of the sixteen dimensions of the pre-aggregate cube but the units kept the calendar cells of the date: the count was read from the cells' histogram as if it were the column's own (14 for a column of 400 days over 14 months, with every unit answered from the pre-aggregates). The count now reads the histogram of the column itself, and a column without one is scanned. Found on the 3.4 tip while the cube's byte budget was being raised, which keeps the cells of more dates. The same condition is in the code of 3.3.0, where the owner did not reproduce the wrong count; if a 3.3 database counts the distinct dates of a calendar-celled column from the pre-aggregates, compare it with `SET sift.enable_agg = off` until the update.

## Known regressions in 3.4.0 (to be fixed in 3.4.1)

Found by the release benchmark run (1,000,000 orders, PostgreSQL 18.6, an x86-64 host); none changes an answer.

- **Store question 03 (`sum(amount) GROUP BY date_trunc('month', created_at)`) after the maintenance step:** the 3.3 results file has 0.12 to 4.62 ms across the four `sift` lanes (`sift` 0.66, `sift_maint` 0.12, `sift_table` 0.53, `sift_table_maint` 4.62 ms); the 3.4 run has 4.1 to 4.6 ms in all four (`sift` 4.54, `sift_maint` 4.49, `sift_table` 4.12 ms). A local run of the same churn did not reproduce it (1.47 ms against 1.41 ms on 3.3.0). Under investigation.
- **`window_first_rows` (the first 3 rows of each group by a window function, one month of 1,000,000 orders):** the planner now takes the aggregate scan, which is slower than PostgreSQL's own plan: 26.2 ms against 22.2 ms on the release host (3.3.0 kept PostgreSQL's plan: 19.5 against 18.8 ms). `SET sift.enable_agg = off` for the statement restores it.
- **A `numeric` column without a precision with almost unique values** (2,000,000 rows, 6 groups) chooses the scan with the default 2 workers and takes 79.6 ms against 61.7 ms for PostgreSQL's plan; with 8 workers and tuned page costs the scan wins (74.1 against 104.7 ms).
- Cases where the aggregate scan or `sift_table` is slower than a heap table or a plain index, measured on the release host with the 3.3.0 library and the 3.4.0 library back to back (milliseconds, sift against the same statement without it): `oltp_insert_index` 57.7 against 37.5 in 3.3.0 and 63.3 against 42.8 in 3.4.0, `oltp_update_index` 104.8 against 75.7 and 118.5 against 86.1, `tbl_update_keys` 5,314 against 602 and 834 against 631, `bulk_copy_wal` 1,304 against 545 and 1,265 against 620, `verdict_shared_report` 29.1 against 20.9 (a 3.4 setting; no 3.3 figure). The ratios are as in 3.3.0 (both columns moved together with the host), except `tbl_update_keys`, which is 6 times faster than in 3.3.0 and still 1.3 times slower than a heap table.

## Not verified, and limits

- **AVX-512 was not run**: the kernels compile for it and the dispatcher never selects it on a machine without it; the AVX2, scalar and NEON paths are the ones tested. arm64 was verified on one a test host and in CI on a hosted arm64 runner, not on a Graviton-class machine.
- **Pull requests that landed without the corpus replay** (plans, rows and instructions of a fixed corpus of statements, the gate used for earlier changes in this series): , , , , , , , , , , , , , , ,  and  had none; , , ,  and  had only the plans and rows part. The replay runs once on the release and a finding goes to 3.4.1.
- **PostgreSQL 17 deep-tier test reds** (the long soak and fuzz runs of the nightly tier, on 17 only) are known and deferred to 3.4.1.
- Parallel float sums add the lanes in a fixed order, so the last digits of a `float8` sum can differ from a serial plan; the difference is bounded by 2(n-1)·2⁻⁵³ of the sum of the absolute values (the largest seen was 7.8e-10 relative).

## Compatibility

- PostgreSQL 17, 18 and 19; the packages below are for PostgreSQL 18 (other majors build from source with `make USE_PGXS=1`). Extension SQL 3.4; `ALTER EXTENSION pg_sift UPDATE` reaches it from any older version in one command. Column format minor 15 by default; the library reads minors up to 21. The BM25 index format is unchanged. [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v3.4.0/docs/UPGRADING.md) has the preflight and 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.0-1_amd64.deb`
- RHEL, Rocky, Alma 9 and 10 (PGDG `postgresql18-server`): `sudo dnf install ./pg_sift_18-3.4.0-1.el9.x86_64.rpm`
- Arch Linux: AUR `pg_sift`.

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

## SQLite extension

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

- **No change:** the 3.4 work is in the PostgreSQL column index, the aggregate scan 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.3.0 open as they are, nothing to rebuild.

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

## MariaDB extension

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

- **No change:** the 3.4 work is in the PostgreSQL column index, the aggregate scan 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.3.0 opens as it is, nothing to rebuild.

Download `sift-mariadb-3.4.0-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.0/mariadb/README.md).
Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.4.0/mariadb/CHANGELOG.md).