-- sift.index_advice(index): the profile of the columns of a sift_col index (kind, what the index holds, the share of 32 evenly spaced units that satisfy the -- rule of a dimension or of a pair of them) and the advice where a rule holds in 90% of them. Advice only: the same rows after a REINDEX, nothing changed. CREATE EXTENSION pg_sift; SET client_min_messages = warning; SET max_parallel_maintenance_workers = 0; -- about 8 units of 7,500 rows. dim_a_id: 100 values, a few in each unit (a foreign key shaped column the cube holds); source: 4 values; region: 300 values in every -- unit (past the 256 groups of a histogram); parent_id: a key list; val: 100,000 values; label: 20 text values; d: a date CREATE TABLE ad (id int PRIMARY KEY, dim_a_id int, source int, region int, parent_id int, val int, d date, label text, pad text) WITH (autovacuum_enabled = off); ALTER TABLE ad ALTER COLUMN pad SET STORAGE PLAIN; INSERT INTO ad SELECT i, i / 600, i % 4, (hashint4(i) & 2147483647) % 300, (hashint4(i + 1) & 2147483647) % 900, (hashint4(i + 2) & 2147483647) % 100000, date '2024-01-01' + i / 200, 'l' || i % 20, repeat('x', 200) FROM generate_series(1, 60000) i; VACUUM (FREEZE, ANALYZE) ad; CREATE INDEX ad_col ON ad USING sift_col (id, dim_a_id, source, region, parent_id, val, d, label) WITH (rollup_columns = 'off'); SELECT * FROM sift.index_advice('ad_col'); leaf | kind | held | sampled | share | advice ------------------+-----------+-----------------------------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------- id | unique | | 8 | 0 | dim_a_id | key | cube histogram in 100% of the units | 8 | 100 | source | dimension | cube histogram in 100% of the units | 8 | 100 | region | measure | | 8 | 0 | parent_id | key | key list or bitmap | 8 | 0 | val | measure | | 8 | 0 | d | date | cube histogram in 100% of the units, calendar cells | 0 | | label | dimension | cube histogram in 100% of the units | 8 | 100 | dim_a_id, source | pair | | 8 | 100 | rollup_columns = 'dim_a_id, source: ' would answer 100% of the units from cells, within 3% more index (rollup_columns = 'auto' writes the cheapest pair) dim_a_id, label | pair | | 8 | 100 | source, label | pair | | 8 | 100 | rollup_columns = 'source, label: ' would answer 100% of the units from cells, within 3% more index (rollup_columns = 'auto' writes the cheapest pair) (11 rows) -- the same rows after a REINDEX (the decisions are a function of the data) CREATE TEMP TABLE ad_before AS SELECT * FROM sift.index_advice('ad_col'); REINDEX INDEX ad_col; SELECT (SELECT count(*) FROM (TABLE ad_before EXCEPT SELECT * FROM sift.index_advice('ad_col')) x) + (SELECT count(*) FROM (SELECT * FROM sift.index_advice('ad_col') EXCEPT TABLE ad_before) y) AS rows_that_differ; rows_that_differ ------------------ 0 (1 row) -- a rollup that holds a pair is not advised again; key_columns = 'off' leaves the foreign key column to the advice CREATE INDEX ad_roll ON ad USING sift_col (id, dim_a_id, source, region, parent_id, val, d, label) WITH (rollup_columns = 'dim_a_id, source', key_columns = 'off'); SELECT leaf, kind, held, share, advice FROM sift.index_advice('ad_roll') WHERE kind = 'pair' OR leaf = 'parent_id' ORDER BY leaf; leaf | kind | held | share | advice -----------------+------+------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------- dim_a_id, label | pair | | 100 | parent_id | key | | 0 | key_columns = 'auto' keeps a key list or bitmap of it for count(DISTINCT) source, label | pair | | 100 | rollup_columns = 'source, label: ' would answer 100% of the units from cells, within 3% more index (rollup_columns = 'auto' writes the cheapest pair) (3 rows) -- the advice is what the build then finds: the advised pair is written in every unit, a pair with a column the advice leaves out (300 values in a unit) is not SET client_min_messages = notice; CREATE INDEX ad_pair ON ad USING sift_col (id, source, label, val) WITH (rollup_columns = 'source, label: val', key_columns = 'off', point_tid_columns = 'off'); NOTICE: sift_col index "ad_pair": 1 advice (track_changes) DETAIL: advice: track_changes = point_tid_columns is off HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ad_pair'); CREATE INDEX ad_over ON ad USING sift_col (id, source, region, val) WITH (rollup_columns = 'source, region: val', key_columns = 'off', point_tid_columns = 'off'); NOTICE: sift_col index "ad_over": 1 advice (track_changes), 1 limit (rollup_columns) DETAIL: advice: track_changes = point_tid_columns is off; limit: rollup_columns = source, region HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ad_over'); SET client_min_messages = warning; -- a covered column that looks rewritten often: its UPDATEs are not HOT CREATE TABLE ad_vol (id int, set_id int, updated_at timestamptz, a_count int, val int) WITH (autovacuum_enabled = off); INSERT INTO ad_vol SELECT i, i % 7, now(), i % 5, i FROM generate_series(1, 2000) i; CREATE INDEX ad_vol_col ON ad_vol USING sift_col (id, set_id, updated_at, a_count, val) WITH (rollup_columns = 'off'); SELECT leaf, kind, advice FROM sift.index_advice('ad_vol_col') WHERE advice LIKE '%HOT%' ORDER BY leaf; leaf | kind | advice ------------+-----------+------------------------------------------------------------------------------------------------------------------------------------- a_count | dimension | an UPDATE of it is not a HOT update while the index covers it: leave it out if it is rewritten far more often than it is aggregated updated_at | date | an UPDATE of it is not a HOT update while the index covers it: leave it out if it is rewritten far more often than it is aggregated (2 rows) -- no unit yet, a wrong object CREATE TABLE ad_empty (id int, k int) WITH (autovacuum_enabled = off); CREATE INDEX ad_empty_col ON ad_empty USING sift_col (id, k) WITH (rollup_columns = 'off'); SELECT leaf, kind, held, sampled, share, advice FROM sift.index_advice('ad_empty_col'); leaf | kind | held | sampled | share | advice ------+---------+------+---------+-------+-------- id | unknown | | 0 | | k | unknown | | 0 | | (2 rows) SELECT * FROM sift.index_advice('ad_pkey'); ERROR: "ad_pkey" is not a sift_col index DROP TABLE ad, ad_empty, ad_vol; DROP EXTENSION pg_sift CASCADE;