-- rollup_columns = 'auto' writes the rollup of the rows of a unit by the columns whose combinations fit the cells a unit may keep, read from a sample of the -- table at CREATE INDEX: the columns that fit the cube's histograms, the fewest values first, as the largest nested set that fits in most of the sampled stretches, -- recorded as 'auto: a, b' (a REINDEX keeps it, ALTER INDEX SET (rollup_columns = 'auto') decides again). Not a default. The answers are the plain plan's. CREATE EXTENSION pg_sift_test CASCADE; NOTICE: installing required extension "pg_sift" CREATE EXTENSION pg_sift_helpers; SET client_min_messages = notice; SET max_parallel_workers_per_gather = 0; SET max_parallel_maintenance_workers = 0; SET cpu_tuple_cost = 0.5; \ir test/agg_preagg_units.sql \set ECHO none -- a, b, c have 5, 7 and 11 values (385 combinations in a unit of about 6,000 rows, 600 cells), e has 97 (too many with them), h one per row CREATE TABLE ra (id int, v bigint, a int, b int, c int, e int, h int, pad text) WITH (autovacuum_enabled = off); ALTER TABLE ra ALTER COLUMN pad SET STORAGE PLAIN; INSERT INTO ra SELECT i, i % 100, i % 5, i % 7, i % 11, (i * 37) % 97, i, repeat('x', 200) FROM generate_series(1, 30000) i; VACUUM (FREEZE, ANALYZE) ra; CREATE FUNCTION ra_option(i text) RETURNS text LANGUAGE sql AS $$ SELECT coalesce((SELECT o FROM pg_class c, unnest(c.reloptions) o WHERE c.relname = i AND o LIKE 'rollup_columns=%'), 'not set') $$; CREATE FUNCTION ra_rows(q text, push bool) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.rows_agg_other_scans_off(q, push) $$; CREATE TABLE ra_q (n int, q text); INSERT INTO ra_q VALUES (1, 'SELECT a, b, count(*), sum(v) FROM ra GROUP BY 1, 2'), (2, 'SELECT count(*), sum(v) FROM ra WHERE a = 2 AND b = 3'), (3, 'SELECT b, c, sum(v) FROM ra GROUP BY 1, 2'); CREATE FUNCTION ra_check() RETURNS TABLE (n int, same bool, from_cells bool) LANGUAGE plpgsql AS $$ DECLARE r record; BEGIN FOR r IN SELECT * FROM ra_q ORDER BY ra_q.n LOOP n := r.n; from_cells := (SELECT coalesce(u.pre, 0) > 0 FROM pa_units(r.q) u); same := ra_rows(r.q, true) = ra_rows(r.q, false); RETURN NEXT; END LOOP; END $$; -- off: the cube holds one column at a time, so a GROUP BY of two scans the units CREATE INDEX ra_none ON ra USING sift_col (id, v, a, b, c, e, h) WITH (rollup_columns = 'off'); NOTICE: sift_col index "ra_none": 1 advice (track_changes) DETAIL: advice: track_changes = the table has no one-column primary key HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ra_none'); SELECT ra_option('ra_none'); ra_option -------------------- rollup_columns=off (1 row) SELECT * FROM ra_check(); n | same | from_cells ---+------+------------ 1 | t | f 2 | t | f 3 | t | f (3 rows) DROP INDEX ra_none; -- no option: the same choice as 'auto' CREATE INDEX ra_default ON ra USING sift_col (id, v, a, b, c, e, h); NOTICE: sift_col index "ra_default": 1 automatic choice (rollup_columns), 1 advice (track_changes) DETAIL: advice: track_changes = the table has no one-column primary key; rollup_columns = a, b HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ra_default'); SELECT ra_option('ra_default'); ra_option --------------------------- rollup_columns=auto: a, b (1 row) SELECT * FROM ra_check(); n | same | from_cells ---+------+------------ 1 | t | t 2 | t | t 3 | t | f (3 rows) DROP INDEX ra_default; CREATE INDEX ra_auto ON ra USING sift_col (id, v, a, b, c, e, h) WITH (rollup_columns = 'auto'); NOTICE: sift_col index "ra_auto": 1 automatic choice (rollup_columns), 1 advice (track_changes) DETAIL: advice: track_changes = the table has no one-column primary key; rollup_columns = a, b HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ra_auto'); SELECT ra_option('ra_auto'); ra_option --------------------------- rollup_columns=auto: a, b (1 row) SELECT sift.col_verify('ra_auto', true); col_verify ------------ (1 row) SELECT * FROM ra_check(); n | same | from_cells ---+------+------------ 1 | t | t 2 | t | t 3 | t | f (3 rows) -- a rebuild keeps the columns, whatever the table holds now (a has 400 values in a unit now); ALTER ... 'auto' decides again, on the columns that fit UPDATE ra SET a = id % 400; VACUUM (FREEZE, ANALYZE) ra; REINDEX INDEX ra_auto; NOTICE: sift_col index "ra_auto": 1 automatic choice, 1 advice, 1 limit DETAIL: advice: track_changes = the table has no one-column primary key; limit: rollup_columns = a, b; rollup_columns = a, b HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ra_auto'); SELECT ra_option('ra_auto'); ra_option --------------------------- rollup_columns=auto: a, b (1 row) ALTER INDEX ra_auto SET (rollup_columns = 'auto'); REINDEX INDEX ra_auto; NOTICE: sift_col index "ra_auto": 1 automatic choice, 1 advice, 1 limit DETAIL: advice: track_changes = the table has no one-column primary key; limit: rollup_columns = b, c; rollup_columns = b, c HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ra_auto'); SELECT ra_option('ra_auto'); ra_option --------------------------- rollup_columns=auto: b, c (1 row) SELECT sift.col_verify('ra_auto', true); col_verify ------------ (1 row) SELECT * FROM ra_check(); n | same | from_cells ---+------+------------ 1 | t | f 2 | t | f 3 | t | t (3 rows) -- columns whose combinations never fit write nothing CREATE TABLE ra_few (id int, x int, y int) WITH (autovacuum_enabled = off); INSERT INTO ra_few SELECT i, i, i % 3 FROM generate_series(1, 2000) i; CREATE INDEX ra_few_col ON ra_few USING sift_col (id, x, y) WITH (rollup_columns = 'auto'); NOTICE: sift_col index "ra_few_col": 1 advice (track_changes) DETAIL: advice: track_changes = the table has no one-column primary key HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ra_few_col'); SELECT ra_option('ra_few_col'); ra_option --------------------- rollup_columns=auto (1 row) DROP TABLE ra, ra_q, ra_few; DROP FUNCTION ra_option(text), ra_rows(text, bool), ra_check(), pa_units(text); DROP EXTENSION pg_sift_helpers; DROP EXTENSION pg_sift_test, pg_sift CASCADE;