# Messages: every NOTICE, WARNING and INFO of pg_sift

Generated by `scripts/gen-messages.sh` from `docs/messages.tsv`; do not edit by hand. `scripts/gen-messages.sh --check` (lint) fails when a message of `src/` or `sql/` has no row, when a row names a message that no longer exists, when the DETAIL or HINT of a row differs from the source, and when a number of a row is in none of the measurements it cites.

pg_sift tells what it chose on its own and what it advises, in the message levels the client shows by default: **49 NOTICE**, **12 WARNING** and **10 INFO** (the lines `VACUUM VERBOSE` adds). Every message has one row below: the exact text (`%s`, `%d`, `%u`, `%.0f` are the values it fills in), where and when it appears, the decision pg_sift took, the benefit and the trade-off of that decision with a measured number, and how to change it or switch it off. A cell that says *no measured number yet* is a measurement nobody made yet.

- `SET client_min_messages = warning` hides the NOTICEs and the INFO lines for the session; the choice they report is made either way. `ALTER DATABASE .. SET client_min_messages = warning` or `ALTER ROLE` keeps it.
- `CREATE INDEX` and `REINDEX` of a `sift_col` index print ONE NOTICE for all the choices they made, the advice and the limits they reached: the count, the values on two lines, and a HINT that names `SELECT * FROM sift.index_choices('index')`. That function returns one row of this page for each choice (the **CHOICE**, **ADVICE** and **LIMIT** rows below): its value on the index, the cost measured on it, the reason and the command that turns it off. `SELECT * FROM sift.help('slug')` returns any row of this page by its anchor, and `sift.help()` all of them.
- The DETAIL and HINT of a message are listed in full [at the end](#detail-and-hint-in-full). An ERROR with its HINT is not in this table: the refusals of the aggregate scan are in [USAGE](USAGE.md#why-a-query-kept-the-normal-plan) and the limits in [LIMITS](LIMITS.md). LOG and DEBUG messages go to the server log, not to the client, and are not listed.
- Numbers are in the units of their source: instructions of one backend, milliseconds, bytes, percent of the index; the conditions (rows, columns, version) are in the cited option case of [OPTIONS](OPTIONS.md).

## Decisions at a glance

| Decision | Default | Gain | Cost | Change or switch off | Row |
|---|---|---|---|---|---|
| Lengths of array columns | automatic (array_lengths = auto) | cardinality(x) > 0 count 1,047 M to 10.8 M instructions | +10.2% index, +12.0% build | ALTER INDEX .. SET (array_lengths = 'off'), then REINDEX | [array-lengths](#array-lengths) |
| Paths of a jsonb column | automatic (paths empty or auto) | GROUP BY of an element key 192 to 4.9 ms | index 2.3 MB against 6.2 MB for a GIN | paths = '...' by hand fixes them; SET (paths = 'auto') then REINDEX reads the sample again | [jsonb-paths](#jsonb-paths) |
| Key lists of foreign keys | automatic (key_columns = auto) | count(DISTINCT fk) 16.1 to 1.11 ms | +1.1% to +1.4% index | ALTER INDEX .. SET (key_columns = 'off'), then REINDEX; key_columns = 'a, b' on CREATE INDEX names the columns | [key-lists](#key-lists) |
| Connection to the parent of a foreign key | automatic (sift.col_auto_connect) | no measured number yet | no measured number yet | SET sift.col_auto_connect = off; connect = 'off' on the index, or ALTER INDEX .. SET (connect = 'off') and REINDEX | [connect-auto](#connect-auto) |
| Elements of array columns | automatic (paths = auto) | element tests read a directory (no measured number yet) | bytes of the directory: no measured number yet | ALTER INDEX .. SET (paths = 'auto:'), then REINDEX leaves the elements out. | [array-elements](#array-elements) |
| Cube dimensions ranked by data | opt-in (preagg = 'auto') | GROUP BY a dimension 1.92 to 0.32 ms | +5.7% to +9.0% index | ALTER INDEX .. SET (preagg = 'default'), then REINDEX, gives them in the order of the columns; preagg = 'cube(...)' names them. | [cube-ranking](#cube-ranking) |
| Rollup of two low-cardinality columns | automatic (rollup_columns = auto) | GROUP BY of the pair 171.8 ms to 3.5 ms | +5.7% index, +1.6% build | ALTER INDEX .. SET (rollup_columns = 'off'), then REINDEX; rollup_columns = 'a, b: measure' names the columns | [rows-rollup](#rows-rollup) |
| Units cut by a key (key_cut) | automatic (key_cut = auto) | count/sum of one key 5.06 to 0.35 ms | build +41%, promotion +21% | ALTER INDEX .. SET (key_cut = 'off') and REINDEX, or CREATE INDEX .. WITH (key_cut = 'off'); a list of columns names your own | [key-cut-chosen](#key-cut-chosen) |
| Heap addresses of unique ids (point_tid_columns) | automatic (point_tid_columns = auto) | unique int8 equality 1.9 M to 0.2 M instructions | +96% index, +21% build | ALTER INDEX .. SET (point_tid_columns = 'off') and REINDEX | [point-directory-auto](#point-directory-auto) |
| Order arrays (sorted keys and heap addresses) | automatic (order_array_columns = auto) | range 18.4M to 2.4M instructions; ORDER BY ts LIMIT 10 9.4M to 0.38M | +21% index, +22% build; ends 20.5 vs 45.7 MB | ALTER INDEX .. SET (order_array_columns = 'off'), then REINDEX, drops them; order_array_columns = 'column' keeps the whole order of a column (or builds a text column that was not chosen); order_array_group = 'off' drops the second order; SET sift.col_format_minor = 14 writes none | [order-array-auto](#order-array-auto) |
| Compact order arrays (order_layout) | automatic (order_layout = auto) | order arrays -19.3%: 15.02 to 12.12 MB | segments minor 41 | ALTER INDEX .. SET (order_layout = 'whole') and REINDEX (or a merge) writes exact keys only; 'compact: column' and 'whole: column' choose it for a column | [order-layout](#order-layout) |
| key_order: one name for a table stored in key order | opt-in (key_order) | OFFSET 10,000 22.2M to 5.5M instructions | index -21% (sorted heap) | ALTER INDEX .. RESET (key_order), then REINDEX; the options you wrote stay | [key-order](#key-order) |
| key_order: the order arrays of the time column, grouped by the entity | default (key_order) | latest row of 100 entities 72.7M to 4.3M instructions (sorted by the entity) | index 7% larger (10 entities, random order) | ALTER INDEX .. SET (key_order = 'off'), then REINDEX; the options you wrote stay. | [key-order-auto](#key-order-auto) |
| Choices made again at the rewrite | automatic (key_cut, order_array_columns, point_tid_columns = auto) | 13.41 MB to 9.94 MB after churn | WAL 9.92 MB for 13.43 MB | ALTER INDEX .. SET (key_cut = 'off') (and the same for the others) keeps a choice off; an option you wrote is never changed. | [rewrite-chose-again](#rewrite-chose-again) |
| Why a query kept the normal plan | off (sift.explain_refusals) | n/a: a diagnostic | n/a: none when off | SET sift.explain_refusals = off (the default); sift.explain_why(query) asks without running it; sift.count_refusals counts without the notice. | [siftagg-not-used](#siftagg-not-used) |
| Rows of a joined table shared between backends | automatic (sift.agg_lookup_shared_kb = -1) | first query of a fresh backend 35.6 to 17.0 ms | 32 sessions 254 MB private vs 88 MB shared | Make room for dynamic shared memory (dynamic_shared_memory_type, the size of /dev/shm), or SET sift.agg_lookup_shared_kb = 0 to stop trying (superuser). | [lookup-not-shared](#lookup-not-shared) |

## CREATE INDEX and REINDEX: choices and advice

What the build of a `sift_col` index chose by itself, and what it advises without changing anything. Each choice is made from the statistics and a sample of the table, is written into the options of the index (`auto: ...`), and is kept by a later `REINDEX`. The build says them all in one NOTICE (row `index-choices-summary`); the rows of kind CHOICE, ADVICE and LIMIT are the ones `sift.index_choices` returns, with their value on the index.

| Message | Fires at, and when | Decision | Benefit | Trade-off | Change or switch off | Docs |
|---|---|---|---|---|---|---|
| <a id="index-choices-summary"></a>**NOTICE** `sift_col index "%s": %s` | CREATE INDEX, REINDEX and REINDEX CONCURRENTLY of a sift_col index. the build made a choice, has advice or reached a limit | Said the count of the choices, advice and limits of the build in one NOTICE, with each value on at most two lines; the reason, the cost and the command that turns each off are in sift.index_choices(index), and sift.help(slug) returns a row of this page. | n/a: the NOTICE reports; each choice has its own row | n/a: one NOTICE replaces one for each choice | SET client_min_messages = warning hides the NOTICE; the choices are made either way. | [MESSAGES#create-index-and-reindex-choices-and-advice](MESSAGES.md#create-index-and-reindex-choices-and-advice) |
| <a id="index-format-minor"></a>**NOTICE** `sift_col index "%s" is column format minor %d, which a library before %s cannot read` | CREATE INDEX, REINDEX and REINDEX CONCURRENTLY of a sift_col index. order arrays, their compact layout or a list of heap addresses you wrote make segments of a newer column format minor | Said which column format minor the segments have and which release reads it first. | n/a: a statement of the format | n/a: the older format leaves these choices out of the index | SET sift.col_format_minor = 14 before the build writes the older format, without those choices. | [UPGRADING#5-first-release-that-reads-each-format](UPGRADING.md#5-first-release-that-reads-each-format) |
| <a id="track-changes-minor"></a>**NOTICE** `sift_col index "%s" does not track changes: sift.col_format_minor is below 15` | CREATE INDEX and REINDEX of a sift_col index with track_changes = <column>. sift.col_format_minor is below 15, so that an older library can read the index | Built the index without the log: it keeps no row key and no tombstone log, and track_changes is set to off. | n/a: a refusal that says why | n/a: reads and writes are those of an index with track_changes = off | Raise sift.col_format_minor to 15 or more, or write WITH (track_changes = 'off') to silence it. | [OPTIONS#sift_coltrack_changes](OPTIONS.md#sift_coltrack_changes) |
| <a id="array-lengths"></a>**CHOICE** `array_lengths keeps the lengths of array columns` | CREATE INDEX and REINDEX of a sift_col index. the index has an array column among its key columns | Wrote array_lengths = on: two integer columns (length and null flag) next to the images of every array column. | count with IS NOT NULL AND cardinality(x) > 0 over six array columns, 1,000,000 rows: 1,047 M to 10.8 M instructions (135.9 to 0.91 ms); three arrays at once 1,214 M to 23 M. | +10.2% of the index (73,015,296 to 80,486,400 bytes; six array columns, 1,000,000 rows), CREATE INDEX +12.0% instructions (8,114 M to 9,090 M; 300,000 rows), insert of 20,000 rows +9.0%. | ALTER INDEX .. SET (array_lengths = 'off'), then REINDEX. Default: automatic. | [OPTIONS#sift_colarray_lengths](OPTIONS.md#sift_colarray_lengths) |
| <a id="jsonb-paths"></a>**CHOICE** `paths keeps the common keys of jsonb columns` | CREATE INDEX and REINDEX of a sift_col index. the index has a jsonb column and no paths option | Sampled the documents (2,000 of them), shredded the keys that are a scalar of one type in most of them (a wildcard for a map of more than 4 keys of one type, a group for an array of objects) and wrote them to paths as auto: spec; REINDEX keeps them. | GROUP BY of an array element key 192 to 4.9 ms (100,000 documents); jsonb_each over a map 338 to 9.5 ms (1,000,000 rows). | The declared leaves are stored once more: index 2.3 MB against 56 MB heap and 6.2 MB for a GIN on the same documents; build 609 ms against 651 ms for the GIN (100,000 documents). A sample of 2,000 documents can miss a rare key. | paths = '...' by hand fixes them; SET (paths = 'auto') then REINDEX reads the sample again. Default: automatic. | [OPTIONS#sift_colpaths](OPTIONS.md#sift_colpaths) |
| <a id="key-lists"></a>**CHOICE** `key_columns keeps the keys of id columns in every unit` | CREATE INDEX and REINDEX of a sift_col index; also after a merge that adds a column. a non-unique int2, int4 or date foreign key or *_id column that the cube does not hold and whose key list or bitmap fits 3% of a unit | Wrote key_columns = auto: the lists of the keys of each unit for the columns named. | count(DISTINCT fk) over whole units 16.1 to 1.11 ms (1,000,000 rows, 123 units); 2.0x to 8.6x on the foreign keys of three 200,000-row tables; an equality or IN skips the units that lack the key. | +1.1% to +1.4% of the index on the foreign keys of three 200,000-row tables; build 87 to 80 ms (auto), within noise (100,000 rows). | ALTER INDEX .. SET (key_columns = 'off'), then REINDEX; key_columns = 'a, b' on CREATE INDEX names the columns. Default: auto. | [OPTIONS#sift_colkey_columns](OPTIONS.md#sift_colkey_columns) |
| <a id="connect-auto"></a>**NOTICE** `sift_col index "%s" connected to %s(%s) through foreign key "%s"` | CREATE INDEX and REINDEX of a sift_col index with no connect option (or connect = 'auto'); one NOTICE for each connection. the index holds the integer column of a validated single column foreign key to a heap table that has a sift_col index tracking the referenced key | Wrote connect = 'auto: <connections>': the index depends on the parent index, which keeps a journal of the keys that change and a leaf per connection that says whether the parent row was found; the columns left out are in the DETAIL. | no measured number yet | no measured number yet | SET sift.col_auto_connect = off; connect = 'off' on the index, or ALTER INDEX .. SET (connect = 'off') and REINDEX | [COL-CONNECT#automatic-connections](COL-CONNECT.md#automatic-connections) |
| <a id="connect-auto-skipped"></a>**NOTICE** `sift_col index "%s": foreign key "%s" is not connected: %s` | CREATE INDEX and REINDEX of a sift_col index with no connect option (or connect = 'auto'). a foreign key of the table has a column in the index and its referenced table has a sift_col index, but the key cannot be connected (more than one column, not validated, not an integer, no index of the referenced table that tracks the key) | None: the index is built without that connection; the text after the colon is the reason. | no measured number yet | no measured number yet | Validate the constraint, or name the connection in connect = 'fk -> parent(key)' once the parent index tracks the key; connect = 'off' hides this | [COL-CONNECT#automatic-connections](COL-CONNECT.md#automatic-connections) |
| <a id="array-elements"></a>**CHOICE** `paths keeps the elements of array columns` | CREATE INDEX and REINDEX of a sift_col index. the index has an array column of text or integers; the elements are kept in place of the arrays where the arrays are nearly all different in a sample, and beside them where the arrays repeat and the elements make the index less than a quarter larger | Added the elements of the arrays to paths (a + where the arrays repeat and the image stays): an element test reads a directory of the rows that own each element. | An element test (tg @> ARRAY['t17'], 20 rows of 100,000) reads 14 blocks and 420,103 instructions with the elements kept, against 1,195 blocks and 131,539,088 with the arrays kept (paths = 'auto:'); a GIN index reads 23 blocks and 348,604. | GROUP BY tg reads 1,195 blocks and 1,495,004,087 instructions with the elements kept, against 16 blocks and 254,197,205 with the arrays kept (a GIN index: 1,497,453,365); the index is 1,826,816 bytes against 1,163,264 (GIN 2,981,888). | ALTER INDEX .. SET (paths = 'auto:'), then REINDEX leaves the elements out. | [OPTIONS#sift_colpaths](OPTIONS.md#sift_colpaths) |
| <a id="cube-ranking"></a>**CHOICE** `preagg gave the cube dimensions to the columns that fit` | CREATE INDEX and REINDEX of a sift_col index with preagg = 'auto'. the index has more plain columns than the cube holds dimensions (16) and the sample ranks others first | Ranked the columns by data (8 stretches of 256 blocks) and gave the dimensions of the cube to the columns whose histograms fit most units. | 16 columns, 8 dimensions of 50 values: GROUP BY a dimension 1.92 to 0.32 ms, 5 of 8 dimensions answered to 8 of 8 (1,000,000 rows). | preagg = 'auto' adds 5.7% to 9.0% of the index (40,000 to 300,000 rows, 26 columns); CREATE INDEX +23 to +25 ms ranking at 300,000 rows, REINDEX +8% to +22%. | ALTER INDEX .. SET (preagg = 'default'), then REINDEX, gives them in the order of the columns; preagg = 'cube(...)' names them. | [OPTIONS#sift_colpreagg](OPTIONS.md#sift_colpreagg) |
| <a id="rows-rollup"></a>**CHOICE** `rollup_columns writes a rollup of two columns` | CREATE INDEX and REINDEX of a sift_col index. two columns of a unit have combinations that fit its cells (a tenth of its rows, at most 4,096) in 90% of 8 sampled stretches | Wrote rollup_columns = auto: a, b and a rollup of the rows by that pair in every unit. | GROUP BY of the pair 171.8 ms normal plan, 8.0 scanned, 3.5 ms from the rollup (1,000,000 rows); count 26.5 M to 17.7 M instructions (600,000 rows). | index +5.7% (15,296,488 against 14,466,400 bytes; 600,000 rows), CREATE INDEX +1.6% instructions; a filter on both values of the pair is slower (12.3 M to 16.0 M instructions). | ALTER INDEX .. SET (rollup_columns = 'off'), then REINDEX; rollup_columns = 'a, b: measure' names the columns. Default: auto. | [OPTIONS#sift_colrollup_columns](OPTIONS.md#sift_colrollup_columns) |
| <a id="key-cut-chosen"></a>**CHOICE** `key_cut cuts the units by a key` | CREATE INDEX and REINDEX of a sift_col index on a table of at least 16,384 pages. a key-shaped column (foreign key or *_id, not unique) whose equality reads at least 8 times the units its rows fill and at least 64 units, no other leaf follows the table order, and the predicted index growth is at most 3% | Cut the units by the key instead of by block range and wrote key_cut = auto: key (a date or timestamp is the second key when the index has one). | one key value in 1 to 3 of 419 units: count/sum of one key 5.06 to 0.35 ms, the busiest key 4.53 to 0.31, a list of 10 keys 4.80 to 0.58 (1,500,000 rows, 8 columns). | build +41% (2.30 to 3.25 s serial, 1,500,000 rows), promotion +21% (0.047 to 0.057 s for 50,000 rows); the index was 13% smaller serial and 5% smaller parallel on the measured table; segments are column format minor 17, which 3.2 and older refuse. | ALTER INDEX .. SET (key_cut = 'off') and REINDEX, or CREATE INDEX .. WITH (key_cut = 'off'); a list of columns names your own. Default: auto. | [OPTIONS#sift_colkey_cut](OPTIONS.md#sift_colkey_cut) |
| <a id="key-cut-advised"></a>**ADVICE** `key_cut would help lookups on a key` | CREATE INDEX and REINDEX of a sift_col index. a key qualifies for key_cut but the build does not write it: either the predicted index growth is above 3% (the sample of the build), or the index has no key_cut, at least 16 units, a non-unique integer leaf of point_columns, point_tid_columns or key_columns scattered over them and no other integer leaf in the order of the table (the units the build wrote) | None: key_cut stays off; the NOTICE prints the command and why the build did not write it. | The same as a chosen cut: count/sum of one key 5.06 to 0.35 ms, 4.53 to 0.31 ms for the busiest key (1,500,000 rows, 8 columns, 419 units). A key equality reads 196 units in a random heap, 18 sorted by date, 1 sorted by the key (200,000 rows). | An automatic cut needs a predicted growth of at most 3%; above it the index grows (+5.3% at 11 independent integer columns, 1,000,000 rows) and the build is 41% longer. key_cut makes the build 41% longer (2.30 to 3.25 s serial, 1,500,000 rows) and the segments column format minor 17. | CREATE INDEX .. WITH (key_cut = 'col') or ALTER INDEX .. SET (key_cut = 'col') and REINDEX takes it; ALTER INDEX .. SET (cluster_advice = off) or SET sift.cluster_advice = off silences the advice. | [OPTIONS#sift_colkey_cut](OPTIONS.md#sift_colkey_cut) |
| <a id="key-cut-hot-value"></a>**ADVICE** `key_cut left a key alone: one of its values holds more rows than a segment` | CREATE INDEX and REINDEX of a sift_col index. a key qualifies for the automatic key_cut, or is the group of a key_order that implies one, but its statistics (the first of the most common values) or the sample say one value holds more rows than a segment holds (2^26, fewer under array_bytes) | None: no cut by that key; key_cut = '<column>' of your own builds it, the rows of that value as runs the rewrite orders. | n/a: advice, nothing was chosen | n/a: advice, nothing was chosen | WITH (key_cut = '<column>') cuts by the key anyway; WITH (key_cut = 'off') hides this. | [OPTIONS#sift_colkey_cut](OPTIONS.md#sift_colkey_cut) |
| <a id="point-directory-auto"></a>**CHOICE** `point_tid_columns lists heap addresses of nearly unique columns` | CREATE INDEX and REINDEX of a sift_col index. an integer column (not a date or timestamp) of nearly unique values (distinct values at least half the rows) whose correlation with the table order is under 0.9 (or a key the order array of the column already answers), and the formula benefit beats its cost | Wrote point_tid_columns = auto: col, a directory of the heap addresses of the rows of each key. | an equality on a random unique int8 of a 7-column index, 400,000 rows: 1.9 M instructions without the directory and 0.2 M with it (a b-tree 0.2 M); an IN list of 16 keys 95.6 M to 0.74 M at 1,000,000 rows. A key the order array already answers takes 0.24 M instructions through the order array and 0.14 M through the directory at 1,000,000 rows. | +96% of the index measured (6.50 to 12.74 MB, 400,000 rows, 7 columns); CREATE INDEX +21% instructions (1,756 to 2,126 a row, one int8 column, 1,000,000 rows). | ALTER INDEX .. SET (point_tid_columns = 'off') and REINDEX. Default: auto. | [OPTIONS#autopoint-directory-columns](OPTIONS.md#autopoint-directory-columns) |
| <a id="point-tid-explicit"></a>**CHOICE** `point_tid_columns lists heap addresses of the columns listed` | CREATE INDEX and REINDEX of a sift_col index with point_tid_columns set by hand. segments with a point directory were written | None new: said what the option you wrote does (heap addresses without a unit read, keys of the column sorted; the DETAIL says that a library before 3.7 cannot read the segments). | integer equality 0.013 ms flat from 26,700 to 1,000,000 rows (point_columns 0.015-0.022, b-tree 0.006); 100-id IN 45.7 to 0.25 ms (1,000,000 rows). | build 1.32 against 0.80 s (+65%), promotion of 100,000 rows 92 against 53 ms; the index is within 3% of point_columns (91.4 against 93.7 MB, 29.1 MB without a directory). | Drop the option (RESET), or set sift.col_format_minor to 14, to list the rows of the segment instead. | [OPTIONS#sift_colpoint_tid_columns](OPTIONS.md#sift_colpoint_tid_columns) |
| <a id="point-tid-key-cut"></a>**ADVICE** `point_tid_columns lists rows, not heap addresses, on a key-cut index` | CREATE INDEX and REINDEX of a sift_col index with point_tid_columns and key_cut. both options are set and the index has a key_cut | None: the directory lists the rows of the segment, not heap addresses, because the units of a key-cut segment are not block ranges. | integer equality 0.119 to 0.020 ms, IN of 5 keys 0.193 to 0.028 ms (200,000 rows; a b-tree 0.006 and 0.009), as with point_columns. | 4 bytes a row for keys that repeat (0.8 MB for 200,000 rows), 4.2 MB for a unique key; a lookup reads the unit of each row. | Nothing to change; key_cut = off gives the address form back. | [OPTIONS#sift_colpoint_tid_columns](OPTIONS.md#sift_colpoint_tid_columns) |
| <a id="order-array-auto"></a>**CHOICE** `order_array_columns keeps sorted arrays of columns` | CREATE INDEX and REINDEX of a sift_col index. a column of 1,000 or more distinct values, correlation with the heap order under 0.99, on a table of 100,000 rows or more (a table with no statistics is analyzed first); a text column or a column of fewer than 1,000 values only where the default formula pays | Kept sorted arrays of the keys and heap addresses of the columns it names: the whole order of a segment, or only its first and last entries (ends only) where the zones of the units prune a bound in the middle of the values; for a date or timestamp also a second order sorted by an integer column of 100 or more values that the table is read by (the first or second key column of a b-tree whose other one is a date or timestamp of the index, else an entity: a foreign key or a column named *_id), the NOTICE naming which one; a text column or a column of few values only where benefit log2(S) / log2(1.05) is at least the cost G / 3 + W / 5. One NOTICE for the index lists the columns and, in the DETAIL, why each one has what it has, including the text column it did not choose. | whole order: a range of 790 of 800,000 rows 18.4M to 2.4M instructions, 7,900 rows 44.1M to 19.1M; ORDER BY col LIMIT n reads n entries whatever the number of units; ts >= x ORDER BY ts LIMIT 10 9.4M to 0.38M (a b-tree 0.19M) at 500,000 rows. Ends only: ORDER BY LIMIT 20 and >= recent ORDER BY DESC LIMIT 20 read the same 20 to 50 blocks, and the index is 20.5 MB where the whole order makes it 45.7 MB (5,000,000 rows, random timestamps). Second order by group: WHERE grp = x ORDER BY ts DESC LIMIT 10, 400 to 1,000 groups, 500,000 rows: 3.34M to 0.42M instructions (a b-tree on (grp, ts) 0.27M). Text column: ORDER BY txt LIMIT n 500,000 rows 403M to 0.33M instructions (a b-tree 0.22M). Column of few values: ORDER BY d DESC, id LIMIT 20 OFFSET 10000 over 300,000 rows of 365 dates 14.3 ms, 109 million instructions to 2.0 ms, 10.6 million, 854 blocks. | whole order: +21% of a 7-column index of 30 bytes a row (24.3 to 29.4 MB, 800,000 rows), CREATE INDEX +22% instructions (5.99G to 7.32G); a one-column timestamp index +121% (1.87 to 4.12 MB). Ends only: a range in the middle of the column reads the units (>= middle ORDER BY LIMIT 20 is 2,529 blocks / 9.3 ms against 49 / 0.35 ms with the whole order). Second order: +13.2% of the index (500,000 rows, 9 columns), CREATE INDEX +5.7% instructions (6.347G to 6.708G). Text column: 7.4 bytes a row (+14% of a 9-column index), CREATE INDEX +81% under the C collation and +390% under pt_BR. Column of few values: about 3 bytes a row (+12% of the index) and 11% more build of 7 columns. The segments are of a newer column format (the DETAIL names the minor and the first release that reads it). | ALTER INDEX .. SET (order_array_columns = 'off'), then REINDEX, drops them; order_array_columns = 'column' keeps the whole order of a column (or builds a text column that was not chosen); order_array_group = 'off' drops the second order; SET sift.col_format_minor = 14 writes none. Default: auto. | [OPTIONS#sift_colorder_array_columns](OPTIONS.md#sift_colorder_array_columns) |
| <a id="order-array-listed"></a>**CHOICE** `order_array_columns keeps the sorted arrays of the columns listed` | CREATE INDEX and REINDEX of a sift_col index with order_array_columns set to a list. the option names columns of order_columns | None new: said the bytes the option you wrote adds (the whole order of each named column, in a newer column format named in the DETAIL). | a range of 790 of 800,000 rows 18.4M to 2.4M instructions; ORDER BY col LIMIT n reads n entries and n rows of the heap, whatever the number of units. | +21% of a 7-column index (24.3 to 29.4 MB, 800,000 rows), CREATE INDEX +22% instructions (5.99G to 7.32G). | Drop the option (RESET), or set sift.col_format_minor to 14, to keep the sorted runs only. | [OPTIONS#sift_colorder_array_columns](OPTIONS.md#sift_colorder_array_columns) |
| <a id="order-layout"></a>**CHOICE** `order_layout rounds the keys of order arrays` | CREATE INDEX and REINDEX of a sift_col index with an order array. the build kept a compact chunk, or order_layout is set | Kept a chunk of 512 entries compact (heap address and key rounded to a bucket) where that is at least a fifth smaller and it has no run of 8 equal keys; whole elsewhere. | 15.02 MB whole against 12.12 MB compact (1,000,000 rows, id, timestamp and a small integer; a b-tree on the timestamp alone is 22.49 MB): -19.3%; OFFSET 100,000 of 1,000,000 rows 3.27 against 3.08 ms. | keys that tie much cost more compact (auto keeps them whole); a compact entry holds a bucket, so an index-only page on a microsecond key reads the heap page for the key; segments are column format minor 41, which 3.7 and older cannot read. | ALTER INDEX .. SET (order_layout = 'whole') and REINDEX (or a merge) writes exact keys only; 'compact: column' and 'whole: column' choose it for a column. Default: auto. | [OPTIONS#sift_colorder_layout](OPTIONS.md#sift_colorder_layout) |
| <a id="foreign-key-not-indexed"></a>**ADVICE** `foreign key columns are not columns of the index` | CREATE INDEX and REINDEX of a sift_col index. a column of a foreign key of the table is not a column of the index | None: advice that a join to the referenced table on them keeps the normal plan (join_condition) and the scan cannot filter the table by the keys of the rows another table filters keep. | The join of orders to customers grouped by customers.region reads 17,854,263 instructions and 72 blocks with the foreign key a column of the orders index, against 181,543,272 and 564 blocks (a sequential scan of both tables) without it; with the primary keys and a b-tree on orders(cust_id) it reads 299,406,037 and 100,134 blocks. | The column makes the orders index 344,064 bytes against 180,224 without it (100,000 rows). | Add the columns to the index if queries join on them; nothing to switch off. | [USAGE#what-create-index-decides-for-the-columns](USAGE.md#what-create-index-decides-for-the-columns) |
| <a id="hot-update-columns"></a>**ADVICE** `covered columns change often, so UPDATEs are not HOT` | CREATE INDEX and REINDEX of a sift_col index. a covered column is named like a timestamp of the last change (updated_at, modified_at), a counter (*_count, likes, views) or a version | None: advice that covering the column makes its UPDATEs non-HOT, writing a new row version into every index of the table. | sum(views) by g from the index that covers views reads 8 blocks and 357,257 instructions, against 776 blocks and 110,040,967 without the column (a heap scan) and 277 blocks and 79,327,569 for a b-tree on (g) INCLUDE (views) (100,000 rows). | measured on a 13-column index: 0% HOT updates against 28%, 23.7k against 38.7k updates a second, 2,175 against 1,493 bytes of WAL an update; on a 3-column index, an UPDATE of 10,000 rows writes 978,032 bytes of WAL with the column covered (hot updates: 0 of 10000) and 728,400 without (10000 of 10000). | Leave the column out of the index if it is rewritten far more often than it is aggregated; nothing to switch off. | [USAGE#what-create-index-decides-for-the-columns](USAGE.md#what-create-index-decides-for-the-columns) |
| <a id="key-order"></a>**CHOICE** `key_order sets the options you did not write` | CREATE INDEX and REINDEX of a sift_col index with key_order set. key_order names a column (or group, column) and key_cut, order_array_columns or order_array_group are not set by hand | Set those options on the key column for you; an option you wrote stays. | on a table sorted by the key, ORDER BY key LIMIT 20 OFFSET 10,000 reads 5.5M instead of 22.2M instructions (0.56 against 1.77 ms), 100,000 rows, 11 columns. | the index of a sorted heap is 21% smaller (0.46 against 0.58 MB, 100,000 rows), 14% smaller on an arrival heap; against key_cut alone 12 rounds of churn write 10 to 24% more WAL; with a group the count(*) of one group costs 5 times more. | ALTER INDEX .. RESET (key_order), then REINDEX; the options you wrote stay. Default: off. | [OPTIONS#sift_colkey_order](OPTIONS.md#sift_colkey_order) |
| <a id="key-order-auto"></a>**CHOICE** `key_order picked the time column and its entity` | CREATE INDEX and REINDEX of a sift_col index with an entity column (a foreign key or a column named *_id) and a date or timestamp column, or a default key_cut by a date or timestamp column. neither key_order, order_columns, order_array_columns nor order_array_group is set by hand, key_cut is the build's choice or 'off', and the layout pays: it replaces the second order of the dates the plain default builds (not under a cut by the date), or the units are cut by the entity, or the heap is sorted by it (10 entities or more); a key alone needs a key of 1,000 values or more | Writes key_order = 'auto: entity, ts' (or 'auto: ts') with the build; a REINDEX decides again. | the latest row of each of 100 entities reads 4.3M instead of 72.7M instructions with 100 entities in rows sorted by the entity, 0.9M instead of 9.6M with 10 entities in a random order, and the latest 20 of one tenant 0.3M instead of 0.8M (300,000 rows, records 4281a82244c9-20261103T030747Z-e587, 4281a82244c9-20261103T025224Z-87fb). | the index is 7% larger with 10 entities in a random order and 40% smaller sorted by the entity; ORDER BY entity, ts OFFSET 10,000 takes 30.6 ms instead of 15.6 ms with 10 entities in a random order, and the join of 100 entities 4.8M instead of 2.0M instructions (sorted by the entity). | ALTER INDEX .. SET (key_order = 'off'), then REINDEX; the options you wrote stay. | [OPTIONS#sift_colkey_order](OPTIONS.md#sift_colkey_order) |
| <a id="sift-col-index-does-not-track-changes"></a>**ADVICE** `track_changes found no key to track` | CREATE INDEX and REINDEX of a sift_col index with the default track_changes = auto. the table has no primary key of one column, the index does not hold it, sift.col_format_minor is below 15 (an older library must read the index), or the extension is not updated yet (pg_upgrade restores the indexes of an older release before ALTER EXTENSION UPDATE) | None: the index keeps no row key and no tombstone log; reads and writes are those of an index with track_changes = off. | n/a: advice, nothing was chosen | n/a: advice, nothing was chosen | WITH (track_changes = 'off') silences it and keeps the index as it was before 4.0; WITH (track_changes = 'COLUMN') names the key (the default is 'auto'). | [OPTIONS#sift_coltrack_changes](OPTIONS.md#sift_coltrack_changes) |
| <a id="sift-col-index-installed-two-statement-triggers"></a>**CHOICE** `track_changes records changed rows with two triggers` | CREATE INDEX, REINDEX and ALTER INDEX .. SET of a sift_col index that tracks a key (the default track_changes = auto on a primary key, or a named key). the index has a key to track and the table is not a partition, a parent or the target of a subscription | The index installed the AFTER UPDATE and AFTER DELETE statement triggers (sift_track_update_<index>_<oid> and sift_track_delete_<index>_<oid>, dependent on the index, not written by pg_dump) and wrote the baseline: a pass over the heap that tombstones every version already dead. | n/a: the triggers belong to track_changes | n/a: the triggers belong to track_changes | WITH (track_changes = 'off') builds the index without the triggers; ALTER INDEX i SET (track_changes = 'off') drops them. | [OPTIONS#sift_coltrack_changes](OPTIONS.md#sift_coltrack_changes) |
| <a id="sift-col-index-installed-row-triggers"></a>**CHOICE** `track_changes records changed rows with two row triggers` | CREATE INDEX, REINDEX and ALTER INDEX .. SET of a sift_col index with track_changes = 'ctid'. the option is ctid and the table is a heap table that is not a partition, a parent or the target of a subscription | The index installed the AFTER UPDATE and AFTER DELETE row triggers (sift_track_update_<index>_<oid> and sift_track_delete_<index>_<oid>, dependent on the index, not written by pg_dump) and wrote the baseline: a pass over the heap that tombstones every version already dead. PostgreSQL queues an event for each row a statement updates or deletes, about 42 bytes a row, until the statement ends: 10,000,000 rows raise the peak memory of the backend by about 420 MB. | n/a: the triggers belong to track_changes | n/a: the triggers belong to track_changes | ALTER INDEX i SET (track_changes = 'off') drops the triggers (before a statement that changes millions of rows); SET (track_changes = 'ctid') installs them again with a new baseline. | [OPTIONS#sift_coltrack_changes](OPTIONS.md#sift_coltrack_changes) |
| <a id="track-changes-ctid-queue-memory"></a>**NOTICE** `sift_col index "%s": track_changes = ctid queues about 42 bytes a changed row` | ALTER INDEX .. SET (track_changes = 'ctid') that installs the row triggers. the option changes to ctid on an index whose triggers are not in place | None: the triggers are installed and the baseline written. PostgreSQL queues one event for each row an UPDATE or DELETE changes until the statement ends, about 42 bytes a row, 420 MB for 10,000,000 rows. | n/a: advice, nothing was chosen | n/a: advice, nothing was chosen | ALTER INDEX i SET (track_changes = 'off') drops the triggers; SET (track_changes = 'ctid') installs them again with a new baseline. | [OPTIONS#sift_coltrack_changes](OPTIONS.md#sift_coltrack_changes) |
| <a id="track-changes-concurrent"></a>**NOTICE** `sift_col index "%s": track_changes log is not complete after a concurrent build` | CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY of an index with track_changes. writers run beside the build, so what they ended before the triggers existed is not known | None: the two triggers are created and hear from now on; the log is not marked complete, so no read can rely on it until the next VACUUM of the table or the maintenance worker re-baselines it. | n/a: advice, nothing was chosen | n/a: advice, nothing was chosen | ALTER INDEX .. SET (track_changes = 'off') then SET (track_changes = '<key column>') makes the log complete (a pass over the heap); REINDEX does too, and so does the next VACUUM of the table or the maintenance worker. | [OPTIONS#sift_coltrack_changes](OPTIONS.md#sift_coltrack_changes) |
| <a id="track-changes-key-pending"></a>**NOTICE** `sift_col index "%s": track_changes column "%s" has no unique index of its own yet` | CREATE INDEX and REINDEX of a sift_col index with track_changes = <column> on a table that has no unique index on the column. a dump creates the index before the primary key (pg_restore -j, pg_upgrade), or the constraint was dropped | Built the index with its two triggers and a baseline; reads leave the log until the unique index exists, and a statement that finds none ends its completeness. | n/a: a notice that says what the log waits for | n/a: reads and writes are those of an index with track_changes = off until the key exists | Make the key (UNIQUE or PRIMARY KEY on the column alone), then REINDEX INDEX; or write WITH (track_changes = 'off'). | [USAGE#reading-without-the-heap-track_changes](USAGE.md#reading-without-the-heap-track_changes) |
| <a id="reloption-older-name"></a>**NOTICE** `reloption "%s" is the older name of "%s"` | CREATE INDEX or ALTER INDEX .. SET of a sift or sift_col index with an older reloption name (mutable_limit, rollup, rollup_dims). the option is the older spelling of write_buffer_limit, rollup_leaves or rollup_columns | None: the older name is read as the new one (when the new one is empty or at its default). | n/a: a notice, no cost in time or space | n/a: a notice, no cost in time or space | Write the new name instead; ALTER INDEX .. RESET (old name) drops the older one. | [OPTIONS](OPTIONS.md) |
| <a id="key-cut-rows-deprecated"></a>**ADVICE** `key_cut_rows is ignored: the units aim at the rows of an average superblock` | CREATE INDEX and REINDEX of a sift_col index with key_cut_rows set. the option is not 0 | None: the option is accepted and ignored, existing segments are read as before, and the next major release removes it. | n/a: an ignored option, no cost in time or space | n/a: an ignored option, no cost in time or space | RESET (key_cut_rows) | [USAGE#what-create-index-decides-for-the-columns](USAGE.md#what-create-index-decides-for-the-columns) |
| <a id="point-columns-deprecated"></a>**ADVICE** `point_columns lists the same columns as point_tid_columns, and is removed in the next major release` | CREATE INDEX and REINDEX of a sift_col index with point_columns set. the option names columns | None: the columns are listed in the directory of heap addresses (point_tid_columns), which answers an equality or a short IN list with no unit read; segments already written are read as before. | n/a: an ignored option, no cost in time or space | n/a: an ignored option, no cost in time or space | RESET (point_columns), and name the columns in point_tid_columns | [USAGE#what-create-index-decides-for-the-columns](USAGE.md#what-create-index-decides-for-the-columns) |
| <a id="order-columns-deprecated"></a>**ADVICE** `order_columns is ignored: the order arrays answer a top-N, a range and an OFFSET` | CREATE INDEX and REINDEX of a sift_col index with order_columns set. the option names columns | None: no sorted run is written (the units keep no order of a column); segments already written are read as before, and order_array_columns chooses the arrays. | n/a: an ignored option, no cost in time or space | n/a: an ignored option, no cost in time or space | RESET (order_columns) | [USAGE#what-create-index-decides-for-the-columns](USAGE.md#what-create-index-decides-for-the-columns) |

## Limits reached by a build: units without what was asked

Where a cap of an option left units without a summary, a list or a dictionary, so a query scans them. The build changed no answer; it says how much of the index is affected.

| Message | Fires at, and when | Decision | Benefit | Trade-off | Change or switch off | Docs |
|---|---|---|---|---|---|---|
| <a id="wildcard-keys-overflow"></a>**LIMIT** `keys of a wildcard path passed the limit of the index` | CREATE INDEX and REINDEX of a sift_col index with a jsonb wildcard path (m.*). a segment has more distinct keys of the map than the index keeps (65,536 per segment) in at least one unit | Stored the keys and values past the limit as overflow, read from the heap; a query that reads the leaf keeps the normal plan. | bounds the dictionary of keys of a segment at 65,536 keys. | d->>'k77' = 77 reads 1,646,813 instructions and 76 blocks on a table of 60,000 keys that fit, and 55,699,006 and 825 blocks over the limit (80,000 keys, a sequential scan); a GIN index on the document reads 226,849 and 5 blocks (4,734,976 bytes). | Declare the keys that queries read by name in paths instead of a wildcard, or keep the map out of the index. | [LIMITS#column-index-srccol](LIMITS.md#column-index-srccol) |
| <a id="string-overflow"></a>**NOTICE** `sift_col: leaf %d (attribute %d%s%s) holds strings over %lld bytes; the rest stay in the heap` | any write of a sift_col index (CREATE INDEX, promotion, merge, insert); once for each leaf of a batch, which is a statement. the strings of one leaf in one unit pass 1,073,741,823 bytes | Stored the values after the limit as overflow, read from the heap. | bounds the bytes of the strings of one leaf in one unit at 1,073,741,823 bytes. | with a budget of 64 kB a unit, payload = x reads 16,231,470 instructions and 883 blocks against 363,845 and 13, and count(DISTINCT payload) 66,058,129 and 883 against 32,847,029 and 27 (30,000 rows); the index is 106,496 bytes against 786,432; a b-tree: 153,774 instructions, 5 blocks, 7,536,640 bytes. | Index an expression that keeps the part of the value that is used, such as left(column, 255). | [LIMITS#column-index-srccol](LIMITS.md#column-index-srccol) |
| <a id="cube-dim-no-histogram"></a>**LIMIT** `the cube has no histogram of a dimension in many units` | CREATE INDEX, REINDEX and sift.col_refresh of a sift_col index; one NOTICE names all the leaves it hits. a dimension of cube(...) has no histogram in 10% or more of the units because they hold more groups of it than the cap (256 by default) | None: the units keep no histogram of the dimension; a query that groups or filters by it scans them (sift.col_rollup_misses counts them). | the cap keeps the cube within 40% of the encoded bytes of a unit as built. | GROUP BY a column of 50,000 groups reads 66,354,457 instructions and 39 blocks (the units are scanned) against 230,142 and 7 blocks for a column of 7 groups with its histogram; a b-tree on the column: 159,218,533 and 249 blocks, 2,039,808 bytes against 253,952. | Group by a leaf with fewer values per unit, or sort the table by it before REINDEX. | [OPTIONS#sift_colpreagg](OPTIONS.md#sift_colpreagg) |
| <a id="keys-bloom-filter"></a>**LIMIT** `keys are a bloom filter, not a list, in many units` | CREATE INDEX, REINDEX and sift.col_refresh of a sift_col index; one NOTICE names all the leaves it hits. a leaf has more distinct keys than the exact summary holds (32) in 10% or more of the units, and no key list | Kept a bloom filter of the keys instead of the list; it skips the units that lack the key of an equality or IN test. | equality mid = 3 (250 rows of 100,000) reads 676,343 instructions and 22 blocks, and count(DISTINCT mid) 302,293 and 19 blocks against 5,443,188 and 20 with no keys written; a b-tree: 246,120 and 3 blocks, 53,068,323 and 91. | the filter gives no list: equality reads 22 blocks where a b-tree reads 3; the index is 98,304 bytes with and without the keys of mid (400 keys), 745,472 for a b-tree. | Name the column in key_columns for a key list or bitmap; key_columns = off removes the summaries. | [OPTIONS#sift_colkey_columns](OPTIONS.md#sift_colkey_columns) |
| <a id="keys-no-summary"></a>**LIMIT** `key_columns wrote no summary of a column in many units` | CREATE INDEX, REINDEX and sift.col_refresh of a sift_col index; one NOTICE names all the leaves it hits. a column named in key_columns has keys that are one value, or every integer of a range, or inline text in 10% or more of the units | Wrote no summary: a summary that could not skip a unit is not stored. | a column named in key_columns whose keys fill their range (id, 100,000 rows) writes no summary and costs no byte: the index is 49,152 bytes with and without it. | equality id = 3 reads 531,358 instructions and 7 blocks with the column named, 549,152 and 7 without; a b-tree: 125,802 and 3 blocks, 2,260,992 bytes. | Leave the column out of key_columns, or sort the table by it before REINDEX. | [OPTIONS#sift_colkey_columns](OPTIONS.md#sift_colkey_columns) |
| <a id="keys-no-list"></a>**LIMIT** `key_columns wrote no key list of a column in many units` | CREATE INDEX, REINDEX and sift.col_refresh of a sift_col index; one NOTICE names all the leaves it hits. the key list or bitmap of a column would take more than 3% of the bytes of the unit in 10% or more of the units | Wrote no key list or bitmap for those units; their keys are a bloom filter where there are more than 32, else exact or, where they fill their range, nothing. | a column of 50,000 keys a unit keeps a bloom filter, not a list: the index is 376,832 bytes with it against 253,952 without key_columns, and 2,039,808 for a b-tree on the column. | count(DISTINCT many) scans the units: 5,432,649 instructions and 61 blocks (5,395,510 and 39 without key_columns; a b-tree 64,190,372 and 249); equality reads 544,884 and 34 blocks (a b-tree 126,340 and 3). | Leave the column out of key_columns, or sort the table by it before REINDEX. | [OPTIONS#sift_colkey_columns](OPTIONS.md#sift_colkey_columns) |
| <a id="rollup-not-written"></a>**LIMIT** `rollup_columns wrote no rollup of a pair in many units` | CREATE INDEX, REINDEX and sift.col_refresh of a sift_col index; one NOTICE names all the leaves it hits. the combinations of the columns of rollup_columns exceed the cells a unit may keep (a tenth of its rows, at most 4,096) in 10% or more of the units | Wrote no rollup of the rows for those units. | a rollup is kept within a tenth of the rows of a unit and at most 4,096 cells. | GROUP BY few, many (350,000 combinations, no rollup) reads 179,106,565 instructions and 38 blocks, against 15,520,503 and 31 for a rollup that fits (2,800 combinations, index 172,032 bytes); a b-tree on both columns: 289,843,245 and 276 blocks, 77,231,672 and 93. | Name fewer columns, or columns with fewer values per unit. | [OPTIONS#sift_colrollup_columns](OPTIONS.md#sift_colrollup_columns) |
| <a id="cube-columns-left-out"></a>**LIMIT** `the cube left out columns past its dimensions` | CREATE INDEX, REINDEX and sift.col_refresh of a sift_col index. the index has more plain columns than the cube has dimensions (16) after the calendar cells of the dates, or calendar_columns = 'all' (the default) and it has more date and timestamp columns than the 16 dimensions allow; one NOTICE for both | None: the cube holds 16 dimensions; the plain columns past them (those whose histograms fit first when preagg = auto) got no histogram, and the date columns past them no calendar cells. | the cube stays within 40% of the encoded bytes of a unit as built. GROUP BY date_trunc('month', d) 2.4 to 0.54 ms (200,000 rows) on the columns that got cells. | GROUP BY a column past the 16 dimensions (20 groups, 100,000 rows) reads 2,752,792 instructions and 27 blocks, against 300,508 and 9 for one inside them; a b-tree: 62,892,003 and 87 blocks; +1.0% of the index for one date column with calendar cells, +1.8% for two. | Give preagg = 'cube(...)' the dimensions the queries group by (sift.index_advice lists the columns that qualify), or preagg = 'auto' to give them to the columns whose histograms fit. Name the columns the queries group by in calendar_columns (the list must fit the 16 dimensions), or give preagg = 'cube(...)' only the dims they need. | [OPTIONS#sift_colpreagg](OPTIONS.md#sift_colpreagg) |

## Maintenance, VACUUM and background workers

What the merges, the vacuums and the background workers did or failed to do. A WARNING says a step failed and is retried; `VACUUM VERBOSE` adds the INFO lines.

| Message | Fires at, and when | Decision | Benefit | Trade-off | Change or switch off | Docs |
|---|---|---|---|---|---|---|
| <a id="auto-merge-autovacuum-off"></a>**NOTICE** `sift_col index "%s": sift.col_auto_merge does not vacuum "%s", whose autovacuum_enabled is false` | CREATE INDEX and REINDEX of a sift_col index. sift.col_auto_merge is on and the table has autovacuum_enabled = false | None: sift.col_auto_merge does not vacuum a table that opted out of autovacuum; the merge of the index follows a VACUUM of the table. | n/a: the benefit is control (no background vacuum you did not ask for), not a speed. | the units answer from their pre-aggregates again only after a VACUUM of the table (a grouped sum after 5% of 200,000 rows updated: 10.8 ms before the merge, 0.24 ms after). | Run VACUUM on it from a job, or RESET autovacuum_enabled. | [OPTIONS#siftcol_auto_merge](OPTIONS.md#siftcol_auto_merge) |
| <a id="rewrite-chose-again"></a>**NOTICE** `sift_col index "%s": the rewrite chooses again from the data: %s` | the rewrite of every segment of a sift_col index (a merge, sift.col_refresh, VACUUM through col_auto_merge). the key_cut, order_array_columns or point_tid_columns the build chose now come out differently from the data (rows appended since the build, a different spread of the values) | Wrote the new values of the options the build had chosen; an option you wrote is never changed. The segments written now are the ones a REINDEX would write. | an index built on a table in the order of its tenants and rewritten after rows of every tenant were appended is 9.94 MB where the rewrite kept the build's choices (13.41 MB; a REINDEX 9.75 MB), 300,000 rows after six cycles of 5% updated. | the rows are held in maintenance_work_mem (about 350 bytes a row) and the cut is left to a REINDEX when they do not fit; 9.92 MB of WAL for 13.43 MB and 0.27 s more CPU a 100,000 rows. | ALTER INDEX .. SET (key_cut = 'off') (and the same for the others) keeps a choice off; an option you wrote is never changed. | [OPTIONS#sift_colkey_cut](OPTIONS.md#sift_colkey_cut) |
| <a id="maintenance-failed"></a>**WARNING** `%s index "%s": maintenance failed: %s` | a background step (promotion or merge) of a sift index, run by the worker, VACUUM or an inserting session; the background worker of a sift_col index (merge, rewrite). the step raised an error (a full disk, a cancelled lock, an out-of-memory) | Rolled the step back, recorded the failure for sift.workers and will retry after a growing delay; the error of the step is the text after the colon. | n/a: a failure report; the step is rolled back and retried, so no query or write is faster for it. | n/a: the delay depends on the cause of the failure (a full disk, a cancelled lock) and on the backoff of the retry, not on a choice the message announces. | Fix the cause in the error text; sift.workers shows the failures in a row and the next retry; sift.merge() retries now; SET sift.async_maintenance = off leaves merges to VACUUM. Default: retry. SET sift.col_auto_merge = off stops the worker of a sift_col index. | [USAGE#maintenance-and-operations](USAGE.md#maintenance-and-operations) |
| <a id="write-buffer-stays-open"></a>**WARNING** `sift index "%s": it has %d segments and its write buffer stays open` | an INSERT, COPY or UPDATE that would seal the write buffer of a sift index. the directory has no free entry for the buffer that would follow (128 segments at most) and merges did not make room | Kept the open write buffer and let it take the writes instead of sealing it; the DETAIL gives the last maintenance failure. | n/a: a failure-avoidance report (the write succeeds instead of failing); no query or write is faster for it. | the open buffer grows past its limit until a merge succeeds; writers merge the smallest closed segments from 16 entries below the limit of 128. | Run sift.merge() on the index; sift.workers shows what the background did. | [LIMITS#search-index-storage-and-queries-srccore-srcbm25](LIMITS.md#search-index-storage-and-queries-srccore-srcbm25) |
| <a id="vacuum-table-rows"></a>**INFO** `table "%s": rows: %llu removed in %d batches, %.0f remain, %.0f are dead but not yet removable` | VACUUM VERBOSE of a sift_table (INFO). always with VERBOSE | None: reports the rows removed, the batches of dead rows the indexes were told in (one unless they filled maintenance_work_mem), and the live and dead rows left. | n/a: a report. | n/a: a report. | VACUUM without VERBOSE does not print it (DEBUG2). | [USAGE#maintenance-and-operations](USAGE.md#maintenance-and-operations) |
| <a id="truncate-standby-streams"></a>**INFO** `%s "%s": not truncating while a standby streams: %lld free blocks stay` | VACUUM VERBOSE of a sift_table (INFO), an autovacuum that logs (LOG). vacuum_truncate is on, a free end of the file exists and a physical standby streams | Skipped the cut of the free end of the file and said how many free blocks stay in it: a cut would stall replay on the standby for as long as its longest query on the index runs. | n/a: it avoids a stall of replay on the standby, whose length depends on the queries of the standby; one server cannot measure it. | the free end stays in the file for the next rewrite (the cut returns, in the measured case, 34.7 MiB to 8.1 MiB). | ALTER INDEX .. SET (vacuum_truncate = false) stops the attempt altogether. Default: on. | [OPTIONS#siftvacuum_truncate](OPTIONS.md#siftvacuum_truncate) |
| <a id="truncate-lock-conflict"></a>**INFO** `%s "%s": not truncating, a lock is held on it (waited %d ms): %lld free blocks stay` | VACUUM VERBOSE of a sift_col index or a sift_table (INFO), an autovacuum that logs (LOG). another session still held a lock on the relation when the cut of its free end had asked for it every 50 ms for 5 seconds (lock_timeout, when shorter, ends the wait) | Gave the cut up once per VACUUM and kept the file as it is, with the blocks that stay in it; the next VACUUM tries again. | n/a: a lock-conflict report; the wait avoided is the duration of another session's lock request, not a property of the index. | n/a: the free end stays until a later VACUUM; its size is the space step figure of the row above, not a cost of the message. | ALTER INDEX .. SET (vacuum_truncate = false). Default: on. | [OPTIONS#sift_colvacuum_truncate](OPTIONS.md#sift_colvacuum_truncate) |
| <a id="index-truncate-lock-conflict"></a>**INFO** `index "%s": not truncating, a lock is held on it (waited %d ms): %lld free blocks stay` | VACUUM VERBOSE of a sift_col index (INFO), an autovacuum that logs (LOG). another session holds an ExclusiveLock on the index when the online cut of its free end asks for it | Gave the online cut up once per VACUUM and kept the file as it is, with the blocks that stay in it. | n/a: a lock-conflict report; the wait avoided is the duration of another session's lock request, not a property of the index. | n/a: the rest of the free end stays until a later VACUUM; not a cost of the message. | ALTER INDEX .. SET (vacuum_truncate = false). Default: on. | [OPTIONS#siftvacuum_truncate](OPTIONS.md#siftvacuum_truncate) |
| <a id="index-truncate-held-by-snapshot"></a>**INFO** `index "%s": %lld free blocks stay, held by a snapshot that may still read them` | VACUUM VERBOSE of a sift_col index (INFO), an autovacuum that logs (LOG). the free blocks of the file that the horizon still holds are a worthwhile share of it, and a physical standby streams (the online cut) | Kept the blocks in the file: a snapshot of the server, or of a standby with hot_standby_feedback, may still read what a rewrite released. A later VACUUM cuts them once the snapshot is gone. | n/a: it explains a cut that did not happen; the blocks stay by design until no snapshot can read them. | n/a: the free blocks stay in the file until the snapshot ends; not a cost of the message. | ALTER INDEX .. SET (vacuum_truncate = false). Default: on. | [OPTIONS#siftvacuum_truncate](OPTIONS.md#siftvacuum_truncate) |
| <a id="index-truncate-online-fallback"></a>**INFO** `index "%s": a lock is held on it (waited %d ms): %lld blocks moved, %lld given back online` | VACUUM VERBOSE of a sift_col index (INFO), an autovacuum that logs (LOG). the AccessExclusiveLock of the cut stayed refused for the wait (a reader holds the index), and the VACUUM moved the segments above the end the file can have into the holes below and cut the free end under an ExclusiveLock instead | Reported what the online cut did in place of the cut that waited: the blocks moved and the blocks given back (what a snapshot may still read is cut by a later VACUUM). | n/a: it reports a cut done without the AccessExclusiveLock; the readers it did not keep out are the benefit. | n/a: the report adds one line a VACUUM; the cut of the blocks that left waits for the snapshots that may read them. | ALTER INDEX .. SET (vacuum_truncate = false). Default: on. | [OPTIONS#siftvacuum_truncate](OPTIONS.md#siftvacuum_truncate) |
| <a id="index-truncated-online"></a>**INFO** `index "%s": truncated %u to %u pages online` | VACUUM VERBOSE of a sift index (INFO). the free end of the file was returned in steps without a long exclusive lock | Cut the free end of the file and gave the pages back to the operating system. | index after churn and VACUUM 8.1 MiB against 34.7 before the space step (1,000,000 rows). | n/a: the exclusive lock is brief and the server does not count it; the wait of a session depends on the other sessions. | ALTER INDEX .. SET (vacuum_truncate = false). Default: on. | [OPTIONS#siftvacuum_truncate](OPTIONS.md#siftvacuum_truncate) |
| <a id="index-truncated"></a>**INFO** `index "%s": truncated %u to %u pages` | VACUUM VERBOSE of a sift index (INFO). the free end of the file was returned under a brief exclusive lock | Cut the free end of the file and gave the pages back to the operating system. | index after churn and VACUUM 8.1 MiB against 34.7 before the space step (1,000,000 rows). | n/a: the exclusive lock is brief and the server does not count it; the wait of a session depends on the other sessions. | ALTER INDEX .. SET (vacuum_truncate = false). Default: on. | [OPTIONS#siftvacuum_truncate](OPTIONS.md#siftvacuum_truncate) |
| <a id="truncated"></a>**INFO** `%s "%s": truncated %u to %u pages` | VACUUM VERBOSE of a sift_col index or a sift_table (INFO). the free end of the file was returned | Cut the free end of the file and gave the pages back to the operating system. | table and indexes 200.3 to 154.5 MiB after VACUUM (1,000,000 rows after churn); orders-per-country 11.49 to 7.75 ms. | a brief exclusive lock that VACUUM skips when busy; update/delete throughput unchanged (10% update 139,711 to 185,603 rows/s). | ALTER INDEX .. SET (vacuum_truncate = false), no REINDEX. Default: on. | [OPTIONS#sift_colvacuum_truncate](OPTIONS.md#sift_colvacuum_truncate) |
| <a id="segments-moved-into-holes"></a>**INFO** `index "%s": moved %llu bytes of the segments that end the file into the holes below them` | VACUUM VERBOSE of a sift index (INFO). segments at the end of the file were rewritten into free blocks below them so the file can be cut | Moved the segments that end the file into the holes below them, within sift.vacuum_rewrite_limit. | a rewritten index of 105 MB live: file 210 to 115 MiB after VACUUM, in 12 MB rounds. | the VACUUM that moves the end of the file holds the index under an exclusive lock between rounds; the limit is 128 MB of segments rewritten by one VACUUM. | SET sift.vacuum_rewrite_limit = 0 forbids the move (one merge step, no file move). Default: 128 MB. | [OPTIONS#siftvacuum_rewrite_limit](OPTIONS.md#siftvacuum_rewrite_limit) |
| <a id="analyzer-options-changed"></a>**WARNING** `sift index "%s": it was built with other analyzer options` | ALTER INDEX .. SET or RESET of an option of a sift (text) index. the analyzer options of the index now differ from those its documents were analyzed with | None: warned that queries and inserts on the table fail until the index is rebuilt. | n/a: a failure report: it announces at the ALTER what the next query would fail with; nothing is faster for it. | n/a: queries and inserts fail until REINDEX; no performance figure applies to a failure. | REINDEX; to change the options without downtime, CREATE INDEX CONCURRENTLY a new index with the options and drop this one. | [OPTIONS#siftanalyzer](OPTIONS.md#siftanalyzer) |

## Planning and execution

Why a query kept the normal plan, what the aggregate scan does when a resource it asked for is missing, and the optional GPU path.

| Message | Fires at, and when | Decision | Benefit | Trade-off | Change or switch off | Docs |
|---|---|---|---|---|---|---|
| <a id="siftagg-not-used"></a>**NOTICE** `SiftAgg not used: %s: %s` | planning of a query with sift.explain_refusals = on. the aggregate scan refuses an arm of the query (the DETAIL names the arm) | None: the normal plan is kept; the NOTICE gives the slug and the reason. | n/a: it reports a reason and changes no plan. | n/a: none when off; the plans are the same on or off. | SET sift.explain_refusals = off (the default); sift.explain_why(query) asks without running it; sift.count_refusals counts without the notice. | [USAGE#why-a-query-kept-the-normal-plan](USAGE.md#why-a-query-kept-the-normal-plan) |
| <a id="lookup-not-shared"></a>**NOTICE** `sift: no dynamic shared memory for %zu bytes, so a joined table is not shared between backends` | the first lookup join that loads the rows of a table in a backend. dynamic shared memory for the entry could not be allocated (no free segment, /dev/shm full) | Kept the rows private to this backend, which loads its own copy; said once for the session. | sharing, when it works: first query of a fresh backend, 200,000 mapping rows, one integer key: 35.6 ms to load, 17.0 ms to read shared; 8 fresh connections at once 59.5 to 13.5 ms. | without sharing, 32 sessions joining a 200,000-row table hold 254 MB private against 88 MB shared in all. | Make room for dynamic shared memory (dynamic_shared_memory_type, the size of /dev/shm), or SET sift.agg_lookup_shared_kb = 0 to stop trying (superuser). | [OPTIONS#siftagg_lookup_shared_kb](OPTIONS.md#siftagg_lookup_shared_kb) |
| <a id="dimmap-not-shared"></a>**NOTICE** `sift: no dynamic shared memory for %zu bytes, so a dimension map is not shared between backends` | the first lookup join that reads a dimension map in a backend. dynamic shared memory for the map could not be allocated (no free segment, /dev/shm full) | None | no measured number yet | no measured number yet | sift.agg_dimmap_kb | [OPTIONS#siftagg_dimmap_kb](OPTIONS.md#siftagg_dimmap_kb) |
| <a id="gpu-no-device"></a>**WARNING** `sift.gpu: no usable Vulkan device (%s); the aggregate runs on the CPU` | the first aggregate scan of a session with sift.gpu = on or auto. no Vulkan device can be opened (the error is in the message); a WARNING when sift.gpu = on asked for the device, a NOTICE when it is auto | Ran the aggregate on the CPU; said once per session. | the query still answers on the CPU path: the device against one CPU core was 0.77x to 1.07x at 1,000,000 rows. | no GPU speedup; 0.99x to 1.00x at 10,000,000 rows (3.2.0 pass) so nothing is lost. | SET sift.gpu = off (the default; superuser) to stop asking. | [GPU#settings](GPU.md#settings) |
| <a id="gpu-failed"></a>**WARNING** `sift.gpu: %s; the aggregate runs on the CPU and the device is not used again in this session` | an aggregate scan on the GPU. the device failed during the scan (out of memory, lost device); a WARNING when sift.gpu = on asked for the device, a NOTICE when it is auto | Finished the aggregate on the CPU and dropped the device for the rest of the session; said once. | the query answers; the device against one CPU core was 0.77x to 1.07x at 1,000,000 rows. | no GPU speedup for the rest of the session; a new connection opens the device again (0.2 s and about 100 MB of context per backend). | SET sift.gpu = off (the default; superuser). | [GPU#settings](GPU.md#settings) |
| <a id="gpu-not-built"></a>**WARNING** `sift.gpu = %s: this build has no Vulkan support (build with VULKAN=1); the aggregate runs on the CPU` | the first aggregate scan of a session with sift.gpu = on or auto. the library was built without VULKAN=1; a WARNING when sift.gpu = on asked for the device, a NOTICE when it is auto | Ran the aggregate on the CPU; said once per session. | the query answers on the CPU path: the device against one CPU core was 0.77x to 1.07x at 1,000,000 rows. | no GPU speedup; the 3.2.0 pass shows no gain over the CPU path (0.99x to 1.00x at 10,000,000 rows). | SET sift.gpu = off (the default; superuser) or build with make VULKAN=1. | [GPU#settings](GPU.md#settings) |
| <a id="agg-lookup-max-rows-deprecated"></a>**WARNING** `sift.agg_lookup_max_rows is deprecated and will be removed in 5.0` | the first aggregate plan of a session that joins a sub-select, VALUES list or function, with sift.agg_lookup_max_rows set to 0 or more. the setting limits the rows of the lookup tables | None: warned once per session; the setting keeps limiting the rows alone. | n/a: the log names the replacement before the setting is removed | n/a: a warning, no cost in time or space | RESET sift.agg_lookup_max_rows (or SET it to -1) and, if a limit is wanted, SET sift.agg_lookup_max_kb. | [AGG#joins-to-plans](AGG.md#joins-to-plans) |

## Upgrade and format

Printed by `ALTER EXTENSION pg_sift UPDATE` or by a function of a released script; those scripts are frozen, so these texts do not change.

| Message | Fires at, and when | Decision | Benefit | Trade-off | Change or switch off | Docs |
|---|---|---|---|---|---|---|
| <a id="upgrade-format-minor"></a>**WARNING** `pg_sift 3.3: a 3.2 library cannot read the sift_table tables and sift_col indexes written after this update, unless sift.col_format_minor is lowered to 6 first` | ALTER EXTENSION pg_sift UPDATE to 3.3 from 3.2 or older. always on that path | None: warned that sift_table tables and sift_col indexes written after the update need sift.col_format_minor lowered to 6 for a 3.2 library (4 for 3.1, 3 for 3.0). | 3.3 writes column formats up to minor 15 by default; setting 6 keeps what it writes readable by 3.2 and 4 by 3.1. | options that need a minor above the setting are ignored; a 3.2 library refuses segments of minor 7 and above. | SELECT * FROM sift.upgrade_check('3.2') lists what a 3.2 library cannot read; SET sift.col_format_minor = 6 before writing. Default: 15. | [UPGRADING#4-an-older-release](UPGRADING.md#4-an-older-release) |
| <a id="upgrade-reindex-text"></a>**WARNING** `pg_sift 3.3: REINDEX is needed for %s text index(es): a query for a word over 255 bytes finds nothing in an index built before this release` | ALTER EXTENSION pg_sift UPDATE to 3.3 from 3.2 or older. a sift (text) index has an analyzer other than keyword | None: listed the indexes (DETAIL) to REINDEX; the index does not say whether it holds a long word, so every text index is listed. | a query for a word over 255 bytes finds rows again: since 3.3 such a word is stored as its first 236 bytes and a hash. | n/a: a one-time report of a frozen update script; the cost of the REINDEX is the build cost of the index, not of the message. | SELECT * FROM sift.upgrade_commands() lists them again; the update itself cannot be silenced. | [UPGRADING#2-install-and-update](UPGRADING.md#2-install-and-update) |
| <a id="upgrade-reindex-column"></a>**WARNING** `pg_sift 3.3: REINDEX is worth it for %s sift_col index(es) after this update` | ALTER EXTENSION pg_sift UPDATE to 3.3 from 3.2 or older. a sift_col index has units that a REINDEX would give the sections of 3.3 | None: listed the indexes (DETAIL) to REINDEX; the older units stay readable until then. | n/a: a one-time report of a frozen update script; the gain is that of the options the new sections serve (docs/OPTIONS.md). | n/a: a one-time report of a frozen update script; the cost of the REINDEX is the build cost of the index. | SELECT * FROM sift.upgrade_commands() gives the reason for each index. | [UPGRADING#2-install-and-update](UPGRADING.md#2-install-and-update) |
| <a id="upgrade-rewrite-table"></a>**WARNING** `pg_sift 3.3: a rewrite is worth it for %s sift_table table(s) after this update` | ALTER EXTENSION pg_sift UPDATE to 3.3 from 3.2 or older. a sift_table has segments that a rewrite would give the layout of 3.3 | None: listed the tables (DETAIL) to rewrite with VACUUM FULL; the older segments stay readable until then. | n/a: a one-time report of a frozen update script; the gain is that of the layout it names (docs/OPTIONS.md). | n/a: a one-time report of a frozen update script; the cost is a rewrite of the table, whose time depends on the table. | SELECT * FROM sift.upgrade_commands() gives the reason for each table. | [UPGRADING#2-install-and-update](UPGRADING.md#2-install-and-update) |
| <a id="upgrade-nothing-to-do"></a>**NOTICE** `pg_sift 3.3: nothing to REINDEX or rewrite after this update` | ALTER EXTENSION pg_sift UPDATE to 3.3 from 3.2 or older. no index or table needs a REINDEX or a rewrite | None: the all-clear; it raises the message level to notice for itself so the line is seen. | n/a: a report. | n/a: a report. | None; it is printed once by the update. | [UPGRADING#2-install-and-update](UPGRADING.md#2-install-and-update) |
| <a id="upgrade-check-failed"></a>**WARNING** `pg_sift 3.3: the check for what to REINDEX or rewrite did not run (%): reconnect and run SELECT * FROM sift.upgrade_commands()` | ALTER EXTENSION pg_sift UPDATE to 3.3 from 3.2 or older. the library did not answer (an older one loaded in this session, a relation locked by another session) | None: the update succeeded; the check was skipped and the message says to run it after reconnecting. | n/a: the check never fails the update. | n/a: nothing is listed until you run it. | Reconnect and run SELECT * FROM sift.upgrade_commands(). | [UPGRADING#2-install-and-update](UPGRADING.md#2-install-and-update) |
| <a id="table-paths-column-groups"></a>**NOTICE** `the column groups of "%" written so far may keep "%" as one leaf: its paths are used once every group has their leaves (VACUUM FULL rewrites them)` | sift.table_set_paths on a sift_table (the function is in the scripts of every release since 3.4). the table already has column groups written | Kept the column as one leaf in the groups written so far; its paths are used once every group has their leaves. | n/a: the declaration is accepted; the message has no speed to report. | n/a: the groups are rewritten by VACUUM FULL, whose time is that of rewriting the table. | VACUUM FULL rewrites the groups; sift.table_set_paths(tbl, col, NULL) removes the declaration. | [COL-TAM](COL-TAM.md) |

## DETAIL and HINT in full

The text after the message line, as `psql` prints it (`%s`, `%d` fill in values). A DETAIL or HINT built from pieces at run time shows its fixed sentences.

- **index-choices-summary** (NOTICE)
  - HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('%s');
- **index-format-minor** (NOTICE)
  - HINT: SET sift.col_format_minor = 14 before the build to write a format the older library reads.
- **track-changes-minor** (NOTICE)
  - HINT: Raise sift.col_format_minor to 15 or more to track changes, or write WITH (track_changes = 'off').
- **connect-auto** (NOTICE)
  - HINT: connect = 'off' on the index turns it off.
- **string-overflow** (NOTICE)
  - HINT: Index an expression that keeps the part of the value that is used, such as left(column, 255).
- **auto-merge-autovacuum-off** (NOTICE)
  - DETAIL: The units answer from their pre-aggregates again only after a VACUUM of the table, and the merge of the index follows that VACUUM.
  - HINT: Run VACUUM on it from a job, or RESET autovacuum_enabled.
- **rewrite-chose-again** (NOTICE)
  - DETAIL: Rows appended since the build change what the defaults choose; an option you wrote is never changed.
  - HINT: ALTER INDEX %s SET (key_cut = 'off') keeps a choice off, and the same for the other options.
- **maintenance-failed** (WARNING)
  - DETAIL: It is retried later, after a growing delay.
- **write-buffer-stays-open** (WARNING)
  - DETAIL: The last maintenance step failed: %s
  - HINT: Run sift.merge() on the index, and read sift.workers for what the background did.
- **analyzer-options-changed** (WARNING)
  - DETAIL: Queries and inserts on its table fail until the index is rebuilt with REINDEX.
  - HINT: For no downtime, CREATE INDEX CONCURRENTLY a new index with the options, then drop this one.
- **siftagg-not-used** (NOTICE)
  - DETAIL: arm: %s
- **lookup-not-shared** (NOTICE)
  - HINT: Free dynamic shared memory (the size of /dev/shm), or SET sift.agg_lookup_shared_kb = 0.
- **dimmap-not-shared** (NOTICE)
  - HINT: Free dynamic shared memory (the size of /dev/shm), or SET sift.agg_dimmap_kb = 0.
- **upgrade-reindex-text** (WARNING)
  - HINT: Such words were cut at 255 bytes, and the index does not say whether it holds one, so every text index (analyzer other than keyword) is listed. REINDEX CONCURRENTLY cannot run in a transaction block. SELECT * FROM sift.upgrade_commands() lists them again.
- **upgrade-reindex-column** (WARNING)
  - HINT: The older units stay readable until then. REINDEX CONCURRENTLY cannot run in a transaction block. SELECT * FROM sift.upgrade_commands() gives the reason for each index.
- **upgrade-rewrite-table** (WARNING)
  - HINT: The older segments stay readable until then. VACUUM FULL takes an ACCESS EXCLUSIVE lock and rewrites the table: schedule it. SELECT * FROM sift.upgrade_commands() gives the reason for each table.
- **track-changes-ctid-queue-memory** (NOTICE)
  - HINT: Before a huge UPDATE or DELETE: ALTER INDEX %s SET (track_changes = 'off'), then 'ctid' again.
- **track-changes-concurrent** (NOTICE)
  - HINT: The next VACUUM completes the log; REINDEX INDEX %s without CONCURRENTLY does it now.
- **track-changes-key-pending** (NOTICE)
  - DETAIL: Reads check the heap until the table has a UNIQUE or PRIMARY KEY constraint on the column alone, and a row written before that is not in the log, so the log stays incomplete after it.
  - HINT: REINDEX INDEX %s gives it a baseline once the constraint exists.
- **agg-lookup-max-rows-deprecated** (WARNING)
  - HINT: Use sift.agg_lookup_max_kb, the memory of the lookup tables, and RESET sift.agg_lookup_max_rows.
- **reloption-older-name** (NOTICE)
  - HINT: Use "%s"; the older name keeps working.