-- the elements of an array column (docs/COL-SHRED.md): an index with a path name[] text keeps the elements of a text[] column as the list of a jsonb array, with a -- point directory that lists the rows that own a value. Every query runs with and without the pushdown and the rows must be the same; the shapes with 'scan' -- ran the pushdown. The same after every kind of write, and col_verify checks the directory against the chunks each time. About 12 s: 54 shapes after each of 9 states -- on ae, the index that keeps no image of the arrays, and on ad, the one that does in the states built, orders, vacuum and reindex (ad gets the same writes in between). CREATE EXTENSION pg_sift; CREATE EXTENSION pg_sift_helpers; SET max_parallel_workers_per_gather = 0; SET seq_page_cost = 1000; SET random_page_cost = 1000; SET client_min_messages = warning; CREATE FUNCTION gen(a int, b int) RETURNS TABLE(id int, tags text[], ids int[]) LANGUAGE sql AS $$ SELECT i, CASE WHEN i % 13 = 0 THEN NULL WHEN i % 17 = 0 THEN '{}' WHEN i % 19 = 0 THEN ARRAY['t1', NULL, 'u1', 't1'] ELSE ARRAY['t' || i % 500, 'u' || i % 3, 'w' || i % 1000] END, CASE WHEN i % 11 = 0 THEN NULL WHEN i % 23 = 0 THEN '{}' WHEN i % 29 = 0 THEN ARRAY[1, NULL, 7] ELSE ARRAY[i % 400, i % 5 + 1000, i % 7 + 2000] END FROM generate_series(a, b) i $$; CREATE TABLE ae (id int, tags text[], ids int[]) WITH (autovacuum_enabled = false); INSERT INTO ae SELECT * FROM gen(1, 30000); CREATE INDEX ae_col ON ae USING sift_col (id, tags, ids) WITH (paths = 'tags[] text, ids[] int4'); VACUUM (FREEZE, ANALYZE) ae; SELECT sift.col_verify('ae_col', true); col_verify ------------ (1 row) CREATE TABLE ad (LIKE ae) WITH (autovacuum_enabled = false); INSERT INTO ad SELECT * FROM gen(1, 30000); -- the plus keeps the image of tags beside its elements: equality and GROUP BY of whole arrays are answered, the element tests by the directory CREATE INDEX ad_col ON ad USING sift_col (id, tags, ids) WITH (paths = '+tags[] text, ids[] int4'); VACUUM (FREEZE, ANALYZE) ad; SELECT sift.col_verify('ad_col', true); col_verify ------------ (1 row) SELECT leaf, path, type FROM sift.col_leaves('ad_col') ORDER BY leaf; leaf | path | type ------+--------+------------------ 0 | id | integer 1 | tags | text[] 2 | tags#1 | integer (lane 1) 3 | tags#2 | integer (lane 2) 4 | tags[] | bigint 5 | tags[] | text 6 | ids | integer[] 7 | ids#1 | integer (lane 1) 8 | ids#2 | integer (lane 2) 9 | ids[] | bigint 10 | ids[] | integer (11 rows) CREATE FUNCTION chk(q text) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.agg_check_outcome(q) $$; CREATE FUNCTION shapes(tag text, t text) RETURNS TABLE(shape text, result text) LANGUAGE sql AS $$ SELECT tag || ' ' || s.name, chk(format(s.q, t)) FROM (VALUES ('rare', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags @> ARRAY['t7']$q$), ('common', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags @> ARRAY['u1']$q$), ('two', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags @> ARRAY['t7', 'u1']$q$), ('absent', $q$SELECT count(*) AS c FROM %1$I WHERE tags @> ARRAY['zz']$q$), ('overlap', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags && ARRAY['t7', 'w999', 'zz']$q$), ('any', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE 'w5' = ANY (tags)$q$), ('not any', $q$SELECT count(*) AS c FROM %1$I WHERE NOT ('u2' = ANY (tags))$q$), ('contained', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags <@ ARRAY['t1', 'u1', 't7', 'w7']$q$), ('not two', $q$SELECT count(*) AS c FROM %1$I WHERE NOT (tags @> ARRAY['t7', 'u1'])$q$), ('not overlap', $q$SELECT count(*) AS c FROM %1$I WHERE NOT (tags && ARRAY['t7', 'u1'])$q$), ('not contained', $q$SELECT count(*) AS c FROM %1$I WHERE NOT (tags <@ ARRAY['t1', 'u1', 't7', 'w7'])$q$), ('null element', $q$SELECT count(*) AS c FROM %1$I WHERE tags @> ARRAY['t1'] AND id %% 19 = 0$q$), ('group', $q$SELECT tags[2] AS u, count(*) AS c FROM %1$I WHERE tags @> ARRAY['t7'] GROUP BY 1$q$), ('empty', $q$SELECT count(*) AS c FROM %1$I WHERE tags = '{}'$q$), ('length', $q$SELECT count(*) AS c FROM %1$I WHERE cardinality(tags) = 2 OR tags IS NULL$q$), ('whole array', $q$SELECT tags, count(*) AS c FROM %1$I GROUP BY 1$q$), ('equal', $q$SELECT count(*) AS c FROM %1$I WHERE tags = ARRAY['t7', 'u1', 'w7']$q$), ('equal two', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags = ARRAY['t7', 'u1']$q$), ('equal swapped', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags = ARRAY['u1', 't7']$q$), ('equal repeated', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags = ARRAY['u1', 'u1']$q$), ('equal 2-D', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags = ARRAY[['t7'], ['u1']]$q$), ('equal bounds', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE tags = '[0:1]={t7,u1}'$q$), ('not equal', $q$SELECT count(*) AS c FROM %1$I WHERE NOT (tags = ARRAY['t7', 'u1'])$q$), ('equal group', $q$SELECT id %% 3 AS g, count(*) AS c FROM %1$I WHERE tags = ARRAY['t7', 'u1'] GROUP BY 1$q$), ('int equal', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE ids = ARRAY[7, 1001]$q$), ('int equal swapped', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE ids = ARRAY[1001, 7]$q$), ('int contains', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE ids @> ARRAY[7]$q$), ('int two', $q$SELECT count(*) AS c FROM %1$I WHERE ids @> ARRAY[7, 2003]$q$), ('int two listed', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE ids @> ARRAY[2003, 2004]$q$), ('int two dense', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE ids @> ARRAY[1001, 2003]$q$), ('int overlap listed', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE ids && ARRAY[2003, 2004, 99999]$q$), ('int overlap', $q$SELECT count(*) AS c, sum(id) AS s FROM %1$I WHERE ids && ARRAY[7, 1001, 99999]$q$), ('int any', $q$SELECT count(*) AS c FROM %1$I WHERE 2004 = ANY (ids)$q$), ('int contained', $q$SELECT count(*) AS c FROM %1$I WHERE ids <@ ARRAY[7, 1001, 2003, 1, 2, 1000, 2000, 1002, 2001, 2002, 3, 4]$q$), ('int not contained', $q$SELECT count(*) AS c FROM %1$I WHERE NOT (ids <@ ARRAY[7, 1001, 2003, 1, 2, 1000, 2000, 1002, 2001, 2002, 3, 4])$q$), ('int null element', $q$SELECT count(*) AS c FROM %1$I WHERE ids @> ARRAY[1]$q$), ('unnest group', $q$SELECT e, count(*) AS c FROM %1$I, unnest(tags) e GROUP BY e$q$), ('unnest total', $q$SELECT count(*) AS c, count(e) AS n, min(e) AS lo, max(e) AS hi FROM %1$I, unnest(tags) e$q$), ('unnest distinct', $q$SELECT e, count(DISTINCT id) AS c FROM %1$I, unnest(tags) e GROUP BY e$q$), ('unnest like', $q$SELECT count(*) AS c FROM %1$I, unnest(tags) e WHERE e LIKE 't1%%'$q$), ('unnest null', $q$SELECT count(*) AS c FROM %1$I, unnest(tags) e WHERE e IS NULL$q$), ('unnest filtered', $q$SELECT e, count(*) AS c, sum(id) AS s FROM %1$I, unnest(tags) e WHERE id %% 7 = 1 GROUP BY e$q$), ('unnest contains', $q$SELECT e, count(*) AS c FROM %1$I, unnest(tags) e WHERE tags @> ARRAY['u1'] GROUP BY e$q$), ('unnest top', $q$SELECT e, count(*) AS c FROM %1$I, unnest(tags) e GROUP BY e ORDER BY 2 DESC, 1 LIMIT 5$q$), ('unnest lateral', $q$SELECT e, count(*) AS c FROM %1$I p CROSS JOIN LATERAL unnest(p.tags) e GROUP BY e$q$), ('unnest left', $q$SELECT e, count(*) AS c FROM %1$I p LEFT JOIN LATERAL unnest(p.tags) e ON true GROUP BY e$q$), ('unnest ordinality', $q$SELECT e, count(*) AS c FROM %1$I, unnest(tags) WITH ORDINALITY u(e, n) WHERE n = 1 GROUP BY e$q$), ('unnest int', $q$SELECT e, count(*) AS c FROM %1$I, unnest(ids) e GROUP BY e$q$), ('unnest int sum', $q$SELECT count(*) AS c, sum(e) AS s, min(e) AS lo, max(e) AS hi, count(e) AS n FROM %1$I, unnest(ids) e WHERE e > 1000$q$), ('unnest int dist', $q$SELECT e %% 7 AS k, count(DISTINCT id) AS c FROM %1$I, unnest(ids) e GROUP BY 1$q$), ('unnest two', $q$SELECT e, count(*) AS c FROM %1$I, unnest(tags) e, unnest(ids) f GROUP BY e$q$), ('keys', $q$SELECT id %% 5 AS k, (tags @> ARRAY['t3']) AS t, count(*) AS c FROM %1$I WHERE tags IS NULL AND id %% 17 = 3 GROUP BY 1, 2$q$), ('count', $q$SELECT count(tags) AS c, count(DISTINCT tags) AS d FROM %1$I$q$)) s(name, q) $$; -- the index keeps no image of the arrays: a statement that needs the whole array reads the table, the others the elements SELECT leaf, path, type FROM sift.col_leaves('ae_col') ORDER BY leaf; leaf | path | type ------+--------+------------------ 0 | id | integer 1 | tags | text[] 2 | tags#1 | integer (lane 1) 3 | tags#2 | integer (lane 2) 4 | tags[] | bigint 5 | tags[] | text 6 | ids | integer[] 7 | ids#1 | integer (lane 1) 8 | ids#2 | integer (lane 2) 9 | ids[] | bigint 10 | ids[] | integer (11 rows) SELECT * FROM shapes('built', 'ae') UNION ALL SELECT * FROM shapes('built', 'ad'); shape | result --------------------------+------------ built rare | scan same built common | scan same built two | scan same built absent | scan same built overlap | scan same built any | scan same built not any | plain same built contained | scan same built not two | plain same built not overlap | plain same built not contained | plain same built null element | scan same built group | plain same built empty | scan same built length | scan same built whole array | plain same built equal | scan same built equal two | scan same built equal swapped | scan same built equal repeated | scan same built equal 2-D | plain same built equal bounds | plain same built not equal | plain same built equal group | scan same built int equal | scan same built int equal swapped | scan same built int contains | scan same built int two | scan same built int two listed | scan same built int two dense | scan same built int overlap listed | scan same built int overlap | scan same built int any | scan same built int contained | scan same built int not contained | plain same built int null element | scan same built unnest group | scan same built unnest total | scan same built unnest distinct | scan same built unnest like | scan same built unnest null | scan same built unnest filtered | scan same built unnest contains | scan same built unnest top | scan same built unnest lateral | scan same built unnest left | plain same built unnest ordinality | plain same built unnest int | scan same built unnest int sum | scan same built unnest int dist | scan same built unnest two | plain same built keys | plain same built count | plain same built rare | scan same built common | scan same built two | scan same built absent | scan same built overlap | scan same built any | scan same built not any | scan same built contained | scan same built not two | scan same built not overlap | scan same built not contained | scan same built null element | scan same built group | scan same built empty | scan same built length | scan same built whole array | scan same built equal | scan same built equal two | scan same built equal swapped | scan same built equal repeated | scan same built equal 2-D | scan same built equal bounds | scan same built not equal | scan same built equal group | scan same built int equal | scan same built int equal swapped | scan same built int contains | scan same built int two | scan same built int two listed | scan same built int two dense | scan same built int overlap listed | scan same built int overlap | scan same built int any | scan same built int contained | scan same built int not contained | plain same built int null element | scan same built unnest group | scan same built unnest total | scan same built unnest distinct | scan same built unnest like | scan same built unnest null | scan same built unnest filtered | scan same built unnest contains | scan same built unnest top | scan same built unnest lateral | scan same built unnest left | scan same built unnest ordinality | plain same built unnest int | scan same built unnest int sum | scan same built unnest int dist | scan same built unnest two | plain same built keys | scan same built count | scan same (106 rows) -- the same on arrays that cannot be NULL, where a NOT may be pushed CREATE TABLE an (id int, tags text[] NOT NULL, ids int[] NOT NULL) WITH (autovacuum_enabled = false); INSERT INTO an SELECT * FROM gen(1, 30000) WHERE tags IS NOT NULL AND ids IS NOT NULL; CREATE INDEX an_col ON an USING sift_col (id, tags, ids) WITH (paths = 'tags[] text, ids[] int4'); VACUUM (FREEZE, ANALYZE) an; SELECT * FROM shapes('not null', 'an'); shape | result -----------------------------+------------ not null rare | scan same not null common | scan same not null two | scan same not null absent | scan same not null overlap | scan same not null any | scan same not null not any | plain same not null contained | scan same not null not two | scan same not null not overlap | scan same not null not contained | scan same not null null element | scan same not null group | plain same not null empty | scan same not null length | scan same not null whole array | plain same not null equal | scan same not null equal two | scan same not null equal swapped | scan same not null equal repeated | scan same not null equal 2-D | plain same not null equal bounds | plain same not null not equal | plain same not null equal group | scan same not null int equal | scan same not null int equal swapped | scan same not null int contains | scan same not null int two | scan same not null int two listed | scan same not null int two dense | scan same not null int overlap listed | scan same not null int overlap | scan same not null int any | scan same not null int contained | scan same not null int not contained | scan same not null int null element | scan same not null unnest group | scan same not null unnest total | scan same not null unnest distinct | scan same not null unnest like | scan same not null unnest null | scan same not null unnest filtered | scan same not null unnest contains | scan same not null unnest top | scan same not null unnest lateral | scan same not null unnest left | plain same not null unnest ordinality | plain same not null unnest int | scan same not null unnest int sum | scan same not null unnest int dist | scan same not null unnest two | plain same not null keys | plain same not null count | plain same (53 rows) DROP TABLE an; INSERT INTO ae SELECT * FROM gen(30001, 34000); INSERT INTO ad SELECT * FROM gen(30001, 34000); SELECT * FROM shapes('insert', 'ae'); shape | result ---------------------------+------------ insert rare | scan same insert common | scan same insert two | scan same insert absent | scan same insert overlap | scan same insert any | scan same insert not any | plain same insert contained | scan same insert not two | plain same insert not overlap | plain same insert not contained | plain same insert null element | scan same insert group | plain same insert empty | scan same insert length | scan same insert whole array | plain same insert equal | scan same insert equal two | scan same insert equal swapped | scan same insert equal repeated | scan same insert equal 2-D | plain same insert equal bounds | plain same insert not equal | plain same insert equal group | scan same insert int equal | scan same insert int equal swapped | scan same insert int contains | scan same insert int two | scan same insert int two listed | scan same insert int two dense | scan same insert int overlap listed | scan same insert int overlap | scan same insert int any | scan same insert int contained | scan same insert int not contained | plain same insert int null element | scan same insert unnest group | scan same insert unnest total | scan same insert unnest distinct | scan same insert unnest like | scan same insert unnest null | scan same insert unnest filtered | scan same insert unnest contains | scan same insert unnest top | scan same insert unnest lateral | scan same insert unnest left | plain same insert unnest ordinality | plain same insert unnest int | scan same insert unnest int sum | scan same insert unnest int dist | scan same insert unnest two | plain same insert keys | plain same insert count | plain same (53 rows) UPDATE ae SET tags = ARRAY['t7', 't' || id % 500], ids = ARRAY[7, id % 400] WHERE id % 11 = 0; UPDATE ad SET tags = ARRAY['t7', 't' || id % 500], ids = ARRAY[7, id % 400] WHERE id % 11 = 0; SELECT * FROM shapes('update', 'ae'); shape | result ---------------------------+------------ update rare | scan same update common | scan same update two | scan same update absent | scan same update overlap | scan same update any | scan same update not any | plain same update contained | scan same update not two | plain same update not overlap | plain same update not contained | plain same update null element | scan same update group | plain same update empty | scan same update length | scan same update whole array | plain same update equal | scan same update equal two | scan same update equal swapped | scan same update equal repeated | scan same update equal 2-D | plain same update equal bounds | plain same update not equal | plain same update equal group | scan same update int equal | scan same update int equal swapped | scan same update int contains | scan same update int two | scan same update int two listed | scan same update int two dense | scan same update int overlap listed | scan same update int overlap | scan same update int any | scan same update int contained | scan same update int not contained | plain same update int null element | scan same update unnest group | scan same update unnest total | scan same update unnest distinct | scan same update unnest like | scan same update unnest null | scan same update unnest filtered | scan same update unnest contains | scan same update unnest top | scan same update unnest lateral | scan same update unnest left | plain same update unnest ordinality | plain same update unnest int | scan same update unnest int sum | scan same update unnest int dist | scan same update unnest two | plain same update keys | plain same update count | plain same (53 rows) -- arrays of two elements in both orders, repeated, of two dimensions and from another bound: the list of a row holds the same elements in all of them UPDATE ae SET tags = ARRAY['t7', 'u1'], ids = ARRAY[7, 1001] WHERE id % 61 = 0; UPDATE ae SET tags = ARRAY['u1', 't7'], ids = ARRAY[1001, 7] WHERE id % 67 = 0; UPDATE ae SET tags = ARRAY['u1', 'u1'] WHERE id % 71 = 0; UPDATE ae SET tags = ARRAY[['t7'], ['u1']] WHERE id % 73 = 0; UPDATE ae SET tags = '[0:1]={t7,u1}' WHERE id % 79 = 0; UPDATE ad SET tags = ARRAY['t7', 'u1'], ids = ARRAY[7, 1001] WHERE id % 61 = 0; UPDATE ad SET tags = ARRAY['u1', 't7'], ids = ARRAY[1001, 7] WHERE id % 67 = 0; UPDATE ad SET tags = ARRAY['u1', 'u1'] WHERE id % 71 = 0; UPDATE ad SET tags = ARRAY[['t7'], ['u1']] WHERE id % 73 = 0; UPDATE ad SET tags = '[0:1]={t7,u1}' WHERE id % 79 = 0; SELECT * FROM shapes('orders', 'ae') UNION ALL SELECT * FROM shapes('orders', 'ad'); shape | result ---------------------------+------------ orders rare | scan same orders common | scan same orders two | scan same orders absent | scan same orders overlap | scan same orders any | scan same orders not any | plain same orders contained | scan same orders not two | plain same orders not overlap | plain same orders not contained | plain same orders null element | scan same orders group | plain same orders empty | scan same orders length | scan same orders whole array | plain same orders equal | scan same orders equal two | scan same orders equal swapped | scan same orders equal repeated | scan same orders equal 2-D | plain same orders equal bounds | plain same orders not equal | plain same orders equal group | scan same orders int equal | scan same orders int equal swapped | scan same orders int contains | scan same orders int two | scan same orders int two listed | scan same orders int two dense | scan same orders int overlap listed | scan same orders int overlap | scan same orders int any | scan same orders int contained | scan same orders int not contained | plain same orders int null element | scan same orders unnest group | scan same orders unnest total | scan same orders unnest distinct | scan same orders unnest like | scan same orders unnest null | scan same orders unnest filtered | scan same orders unnest contains | scan same orders unnest top | scan same orders unnest lateral | scan same orders unnest left | plain same orders unnest ordinality | plain same orders unnest int | scan same orders unnest int sum | scan same orders unnest int dist | scan same orders unnest two | plain same orders keys | plain same orders count | plain same orders rare | scan same orders common | scan same orders two | scan same orders absent | scan same orders overlap | scan same orders any | scan same orders not any | scan same orders contained | scan same orders not two | scan same orders not overlap | scan same orders not contained | scan same orders null element | scan same orders group | scan same orders empty | scan same orders length | scan same orders whole array | scan same orders equal | scan same orders equal two | scan same orders equal swapped | scan same orders equal repeated | scan same orders equal 2-D | scan same orders equal bounds | scan same orders not equal | scan same orders equal group | scan same orders int equal | scan same orders int equal swapped | scan same orders int contains | scan same orders int two | scan same orders int two listed | scan same orders int two dense | scan same orders int overlap listed | scan same orders int overlap | scan same orders int any | scan same orders int contained | scan same orders int not contained | plain same orders int null element | scan same orders unnest group | scan same orders unnest total | scan same orders unnest distinct | scan same orders unnest like | scan same orders unnest null | scan same orders unnest filtered | scan same orders unnest contains | scan same orders unnest top | scan same orders unnest lateral | scan same orders unnest left | scan same orders unnest ordinality | plain same orders unnest int | scan same orders unnest int sum | scan same orders unnest int dist | scan same orders unnest two | plain same orders keys | scan same orders count | scan same (106 rows) DELETE FROM ae WHERE id % 7 = 0; DELETE FROM ad WHERE id % 7 = 0; SELECT * FROM shapes('delete', 'ae'); shape | result ---------------------------+------------ delete rare | scan same delete common | scan same delete two | scan same delete absent | scan same delete overlap | scan same delete any | scan same delete not any | plain same delete contained | scan same delete not two | plain same delete not overlap | plain same delete not contained | plain same delete null element | scan same delete group | plain same delete empty | scan same delete length | scan same delete whole array | plain same delete equal | scan same delete equal two | scan same delete equal swapped | scan same delete equal repeated | scan same delete equal 2-D | plain same delete equal bounds | plain same delete not equal | plain same delete equal group | scan same delete int equal | scan same delete int equal swapped | scan same delete int contains | scan same delete int two | scan same delete int two listed | scan same delete int two dense | scan same delete int overlap listed | scan same delete int overlap | scan same delete int any | scan same delete int contained | scan same delete int not contained | plain same delete int null element | scan same delete unnest group | scan same delete unnest total | scan same delete unnest distinct | scan same delete unnest like | scan same delete unnest null | scan same delete unnest filtered | scan same delete unnest contains | scan same delete unnest top | scan same delete unnest lateral | scan same delete unnest left | plain same delete unnest ordinality | plain same delete unnest int | scan same delete unnest int sum | scan same delete unnest int dist | scan same delete unnest two | plain same delete keys | plain same delete count | plain same (53 rows) SELECT sift.col_verify('ae_col', true), sift.col_verify('ad_col', true); col_verify | col_verify ------------+------------ | (1 row) VACUUM ae; VACUUM ad; SELECT * FROM shapes('vacuum', 'ae') UNION ALL SELECT * FROM shapes('vacuum', 'ad'); shape | result ---------------------------+------------ vacuum rare | scan same vacuum common | scan same vacuum two | scan same vacuum absent | scan same vacuum overlap | scan same vacuum any | scan same vacuum not any | plain same vacuum contained | scan same vacuum not two | plain same vacuum not overlap | plain same vacuum not contained | plain same vacuum null element | scan same vacuum group | plain same vacuum empty | scan same vacuum length | scan same vacuum whole array | plain same vacuum equal | scan same vacuum equal two | scan same vacuum equal swapped | scan same vacuum equal repeated | scan same vacuum equal 2-D | plain same vacuum equal bounds | plain same vacuum not equal | plain same vacuum equal group | scan same vacuum int equal | scan same vacuum int equal swapped | scan same vacuum int contains | scan same vacuum int two | scan same vacuum int two listed | scan same vacuum int two dense | scan same vacuum int overlap listed | scan same vacuum int overlap | scan same vacuum int any | scan same vacuum int contained | scan same vacuum int not contained | plain same vacuum int null element | scan same vacuum unnest group | scan same vacuum unnest total | scan same vacuum unnest distinct | scan same vacuum unnest like | scan same vacuum unnest null | scan same vacuum unnest filtered | scan same vacuum unnest contains | scan same vacuum unnest top | scan same vacuum unnest lateral | scan same vacuum unnest left | plain same vacuum unnest ordinality | plain same vacuum unnest int | scan same vacuum unnest int sum | scan same vacuum unnest int dist | scan same vacuum unnest two | plain same vacuum keys | plain same vacuum count | plain same vacuum rare | scan same vacuum common | scan same vacuum two | scan same vacuum absent | scan same vacuum overlap | scan same vacuum any | scan same vacuum not any | scan same vacuum contained | scan same vacuum not two | scan same vacuum not overlap | scan same vacuum not contained | scan same vacuum null element | scan same vacuum group | scan same vacuum empty | scan same vacuum length | scan same vacuum whole array | scan same vacuum equal | scan same vacuum equal two | scan same vacuum equal swapped | scan same vacuum equal repeated | scan same vacuum equal 2-D | scan same vacuum equal bounds | scan same vacuum not equal | scan same vacuum equal group | scan same vacuum int equal | scan same vacuum int equal swapped | scan same vacuum int contains | scan same vacuum int two | scan same vacuum int two listed | scan same vacuum int two dense | scan same vacuum int overlap listed | scan same vacuum int overlap | scan same vacuum int any | scan same vacuum int contained | scan same vacuum int not contained | plain same vacuum int null element | scan same vacuum unnest group | scan same vacuum unnest total | scan same vacuum unnest distinct | scan same vacuum unnest like | scan same vacuum unnest null | scan same vacuum unnest filtered | scan same vacuum unnest contains | scan same vacuum unnest top | scan same vacuum unnest lateral | scan same vacuum unnest left | scan same vacuum unnest ordinality | plain same vacuum unnest int | scan same vacuum unnest int sum | scan same vacuum unnest int dist | scan same vacuum unnest two | plain same vacuum keys | scan same vacuum count | scan same (106 rows) SELECT sift.col_merge('ae_col', true) > 0 AS merged, sift.col_merge('ad_col', true) > 0 AS merged; merged | merged --------+-------- t | t (1 row) SELECT * FROM shapes('merge', 'ae'); shape | result --------------------------+------------ merge rare | scan same merge common | scan same merge two | scan same merge absent | scan same merge overlap | scan same merge any | scan same merge not any | plain same merge contained | scan same merge not two | plain same merge not overlap | plain same merge not contained | plain same merge null element | scan same merge group | plain same merge empty | scan same merge length | scan same merge whole array | plain same merge equal | scan same merge equal two | scan same merge equal swapped | scan same merge equal repeated | scan same merge equal 2-D | plain same merge equal bounds | plain same merge not equal | plain same merge equal group | scan same merge int equal | scan same merge int equal swapped | scan same merge int contains | scan same merge int two | scan same merge int two listed | scan same merge int two dense | scan same merge int overlap listed | scan same merge int overlap | scan same merge int any | scan same merge int contained | scan same merge int not contained | plain same merge int null element | scan same merge unnest group | scan same merge unnest total | scan same merge unnest distinct | scan same merge unnest like | scan same merge unnest null | scan same merge unnest filtered | scan same merge unnest contains | scan same merge unnest top | scan same merge unnest lateral | scan same merge unnest left | plain same merge unnest ordinality | plain same merge unnest int | scan same merge unnest int sum | scan same merge unnest int dist | scan same merge unnest two | plain same merge keys | plain same merge count | plain same (53 rows) INSERT INTO ae SELECT * FROM gen(40001, 44000); INSERT INTO ad SELECT * FROM gen(40001, 44000); SELECT sift.col_merge('ae_col', true) > 0 AS merged, sift.col_merge('ad_col', true) > 0 AS merged; merged | merged --------+-------- t | t (1 row) SELECT * FROM shapes('merge2', 'ae'); shape | result ---------------------------+------------ merge2 rare | scan same merge2 common | scan same merge2 two | scan same merge2 absent | scan same merge2 overlap | scan same merge2 any | scan same merge2 not any | plain same merge2 contained | scan same merge2 not two | plain same merge2 not overlap | plain same merge2 not contained | plain same merge2 null element | scan same merge2 group | plain same merge2 empty | scan same merge2 length | scan same merge2 whole array | plain same merge2 equal | scan same merge2 equal two | scan same merge2 equal swapped | scan same merge2 equal repeated | scan same merge2 equal 2-D | plain same merge2 equal bounds | plain same merge2 not equal | plain same merge2 equal group | scan same merge2 int equal | scan same merge2 int equal swapped | scan same merge2 int contains | scan same merge2 int two | scan same merge2 int two listed | scan same merge2 int two dense | scan same merge2 int overlap listed | scan same merge2 int overlap | scan same merge2 int any | scan same merge2 int contained | scan same merge2 int not contained | plain same merge2 int null element | scan same merge2 unnest group | scan same merge2 unnest total | scan same merge2 unnest distinct | scan same merge2 unnest like | scan same merge2 unnest null | scan same merge2 unnest filtered | scan same merge2 unnest contains | scan same merge2 unnest top | scan same merge2 unnest lateral | scan same merge2 unnest left | plain same merge2 unnest ordinality | plain same merge2 unnest int | scan same merge2 unnest int sum | scan same merge2 unnest int dist | scan same merge2 unnest two | plain same merge2 keys | plain same merge2 count | plain same (53 rows) SELECT sift.col_verify('ae_col', true); col_verify ------------ (1 row) REINDEX INDEX ae_col; REINDEX INDEX ad_col; SELECT * FROM shapes('reindex', 'ae') UNION ALL SELECT * FROM shapes('reindex', 'ad'); shape | result ----------------------------+------------ reindex rare | scan same reindex common | scan same reindex two | scan same reindex absent | scan same reindex overlap | scan same reindex any | scan same reindex not any | plain same reindex contained | scan same reindex not two | plain same reindex not overlap | plain same reindex not contained | plain same reindex null element | scan same reindex group | plain same reindex empty | scan same reindex length | scan same reindex whole array | plain same reindex equal | scan same reindex equal two | scan same reindex equal swapped | scan same reindex equal repeated | scan same reindex equal 2-D | plain same reindex equal bounds | plain same reindex not equal | plain same reindex equal group | scan same reindex int equal | scan same reindex int equal swapped | scan same reindex int contains | scan same reindex int two | scan same reindex int two listed | scan same reindex int two dense | scan same reindex int overlap listed | scan same reindex int overlap | scan same reindex int any | scan same reindex int contained | scan same reindex int not contained | plain same reindex int null element | scan same reindex unnest group | scan same reindex unnest total | scan same reindex unnest distinct | scan same reindex unnest like | scan same reindex unnest null | scan same reindex unnest filtered | scan same reindex unnest contains | scan same reindex unnest top | scan same reindex unnest lateral | scan same reindex unnest left | plain same reindex unnest ordinality | plain same reindex unnest int | scan same reindex unnest int sum | scan same reindex unnest int dist | scan same reindex unnest two | plain same reindex keys | plain same reindex count | plain same reindex rare | scan same reindex common | scan same reindex two | scan same reindex absent | scan same reindex overlap | scan same reindex any | scan same reindex not any | scan same reindex contained | scan same reindex not two | scan same reindex not overlap | scan same reindex not contained | scan same reindex null element | scan same reindex group | scan same reindex empty | scan same reindex length | scan same reindex whole array | scan same reindex equal | scan same reindex equal two | scan same reindex equal swapped | scan same reindex equal repeated | scan same reindex equal 2-D | scan same reindex equal bounds | scan same reindex not equal | scan same reindex equal group | scan same reindex int equal | scan same reindex int equal swapped | scan same reindex int contains | scan same reindex int two | scan same reindex int two listed | scan same reindex int two dense | scan same reindex int overlap listed | scan same reindex int overlap | scan same reindex int any | scan same reindex int contained | scan same reindex int not contained | plain same reindex int null element | scan same reindex unnest group | scan same reindex unnest total | scan same reindex unnest distinct | scan same reindex unnest like | scan same reindex unnest null | scan same reindex unnest filtered | scan same reindex unnest contains | scan same reindex unnest top | scan same reindex unnest lateral | scan same reindex unnest left | scan same reindex unnest ordinality | plain same reindex unnest int | scan same reindex unnest int sum | scan same reindex unnest int dist | scan same reindex unnest two | plain same reindex keys | scan same reindex count | scan same (106 rows) -- a value no row owns is answered by the directory: no unit is read and no heap row vetted CREATE FUNCTION reads(q text) RETURNS SETOF text LANGUAGE sql AS $$ SELECT * FROM sift_helpers.plan_units_and_rows_lines(q) $$; SELECT * FROM reads($q$SELECT count(*) FROM ae WHERE tags @> ARRAY['zz']$q$); reads ----------------------------------------- Units: 0 read, 2 pruned Rows: 0 scanned, 0 aggregated, 1 groups (2 rows) -- a value in a few thousand rows is listed as well: of a pair, the list of the one that is listed gives the rows to test (1001 is in too many rows to be listed) CREATE TABLE ar (id int, ids int[]) WITH (autovacuum_enabled = false); INSERT INTO ar SELECT id, ids FROM gen(1, 30000); CREATE INDEX ar_col ON ar USING sift_col (id, ids) WITH (paths = 'ids[] int4'); VACUUM (FREEZE, ANALYZE) ar; SELECT * FROM reads($q$SELECT count(*) FROM ar WHERE ids @> ARRAY[1001, 2003]$q$); reads --------------------------------------------------------------- Units: 1 read, 1 from point directory (3598 rows) Rows: 30000 scanned, 718 aggregated, 8 dense chunks, 1 groups (2 rows) SELECT * FROM reads($q$SELECT count(*) FROM ar WHERE ids @> ARRAY[1001]$q$); reads ---------------------------------------------------------------- Units: 1 read Rows: 30000 scanned, 5037 aggregated, 8 dense chunks, 1 groups (2 rows) DROP TABLE ae, ad, ar; DROP FUNCTION gen(int, int), chk(text), shapes(text, text), reads(text); DROP EXTENSION pg_sift_helpers; DROP EXTENSION pg_sift;