-- The dictionary of a string leaf stored to be read in place: the strings in blocks, and with the option -- dict_lookup the codes in byte order too, so that an equality, an IN list and a prefix or range test of a stored segment read it from the stream and -- the backend parses none. The answers are those of the compact form (sift.col_format_minor 13), of the blocks without the order (dict_lookup off) and of the heap, over NULLs, empty, long (inline), -- non-ASCII and tied strings, uuids, DML, the write buffer, promotion and a merge of segments of both forms. CREATE EXTENSION pg_sift_test CASCADE; NOTICE: installing required extension "pg_sift" SET sift.test_col_legacy_options = on; SET sift.async_maintenance = off; CREATE TABLE ds (id int, s text, u uuid, v varchar(40), pad text DEFAULT repeat('x', 400)); ALTER TABLE ds ALTER COLUMN pad SET STORAGE PLAIN; INSERT INTO ds (id, s, u, v) SELECT i, CASE WHEN i % 53 = 0 THEN NULL WHEN i % 47 = 0 THEN '' WHEN i BETWEEN 9000 AND 9050 THEN repeat('long', 100) || i % 9 WHEN i % 41 = 0 THEN (ARRAY['é', 'éa', '日本', '日本語', 'ab', 'abc', 'abd', 'a', 'b', 'B', 'Ab', 'zz'])[1 + i % 12] WHEN i % 7 = 0 THEN 'commonprefix_' || (i * 7919) % 3000 ELSE 'k' || md5((i % 2500)::text) END, CASE WHEN i % 59 = 0 THEN NULL ELSE md5((i % 4000)::text)::uuid END, CASE WHEN i % 61 = 0 THEN NULL ELSE 'v' || (i * 31) % 1700 END FROM generate_series(1, 12000) i; CREATE FUNCTION ds_cmp(idx regclass, preds text, heapwhere text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE a bigint; b bigint; ex bigint; mi bigint; BEGIN SELECT count(*) INTO a FROM sift._test_col_tids(idx, preds); EXECUTE format('SELECT count(*) FROM ds WHERE %s', heapwhere) INTO b; EXECUTE format('SELECT count(*) FROM (SELECT tid FROM sift._test_col_tids(%L, %L) EXCEPT SELECT ctid FROM ds WHERE %s) z', idx, preds, heapwhere) INTO ex; EXECUTE format('SELECT count(*) FROM (SELECT ctid FROM ds WHERE %s EXCEPT SELECT tid FROM sift._test_col_tids(%L, %L)) z', heapwhere, idx, preds) INTO mi; RETURN CASE WHEN a = b AND ex = 0 AND mi = 0 THEN 'ok' ELSE format('MISMATCH index=%s heap=%s extra=%s missing=%s', a, b, ex, mi) END; END $$; -- leaf 0 is s, 1 is u, 2 is v CREATE TABLE ds_q (n serial, preds text, heapwhere text); INSERT INTO ds_q (preds, heapwhere) VALUES ('0:eq:commonprefix_17', 's = ''commonprefix_17'''), ('0:eq:kc4ca4238a0b923820dcc509a6f75849b', 's = ''kc4ca4238a0b923820dcc509a6f75849b'''), ('0:eq:nothing_here', 's = ''nothing_here'''), ('0:eq:', 's = '''''), ('0:eq:日本', 's = ''日本'''), ('0:eq:ab', 's = ''ab'''), ('0:ne:ab', 's <> ''ab'''), ('0:in:ab|abc|zz|none', 's IN (''ab'', ''abc'', ''zz'', ''none'')'), ('0:in:ab|ab|ab', 's IN (''ab'', ''ab'', ''ab'')'), ('0:notin:ab|abc|zz|none', 's NOT IN (''ab'', ''abc'', ''zz'', ''none'')'), ('0:prefix:commonprefix_1', 'starts_with(s, ''commonprefix_1'')'), ('0:prefix:', 'starts_with(s, '''')'), ('0:prefix:a', 'starts_with(s, ''a'')'), ('0:prefix:é', 'starts_with(s, ''é'')'), ('0:prefix:long', 'starts_with(s, ''long'')'), ('0:prefix:zzzz', 'starts_with(s, ''zzzz'')'), ('0:lt:ab', 's < ''ab'' COLLATE "C"'), ('0:ge:abc;0:lt:abd', 's >= ''abc'' COLLATE "C" AND s < ''abd'' COLLATE "C"'), ('0:gt:b', 's > ''b'' COLLATE "C"'), ('2:eq:v77', 'v = ''v77'''), ('2:prefix:v1', 'starts_with(v, ''v1'')'), ('2:in:v1|v2|v3|nope', 'v IN (''v1'', ''v2'', ''v3'', ''nope'')'), ('1:eq:c4ca4238-a0b9-2382-0dcc-509a6f75849b', 'u = ''c4ca4238-a0b9-2382-0dcc-509a6f75849b'''), ('1:eq:00000000-0000-0000-0000-000000000001', 'u = ''00000000-0000-0000-0000-000000000001'''), ('1:in:c4ca4238-a0b9-2382-0dcc-509a6f75849b|c81e728d-9d4c-2f63-6f06-7f89cc14862c', 'u IN (''c4ca4238-a0b9-2382-0dcc-509a6f75849b'', ''c81e728d-9d4c-2f63-6f06-7f89cc14862c'')'), ('0:eq:ab;1:eq:c4ca4238-a0b9-2382-0dcc-509a6f75849b', 's = ''ab'' AND u = ''c4ca4238-a0b9-2382-0dcc-509a6f75849b'''); -- the stored form: the segment is minor 14, the dictionaries are not parsed by an equality, an IN list or a prefix, and every answer is the heap's CREATE INDEX ds_new ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', write_buffer_limit = 256, dict_lookup = on); NOTICE: sift_col index "ds_new": 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('ds_new'); VACUUM (ANALYZE) ds; SELECT sift.col_verify('ds_new', true); col_verify ------------ (1 row) SELECT count(*) AS segments, min(format) AS format, sum(units) > 2 AS units FROM sift.col_segments('ds_new') WHERE kind = 'immutable'; segments | format | units ----------+--------+------- 1 | 39 | t (1 row) SELECT count(*) > 0 AS reset FROM sift._test_col_dict_cache(true); reset ------- t (1 row) SELECT n, ds_cmp('ds_new', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp ----+-------- 1 | ok 2 | ok 3 | ok 4 | ok 5 | ok 6 | ok 7 | ok 8 | ok 9 | ok 10 | ok 11 | ok 12 | ok 13 | ok 14 | ok 15 | ok 16 | ok 17 | ok 18 | ok 19 | ok 20 | ok 21 | ok 22 | ok 23 | ok 24 | ok 25 | ok 26 | ok (26 rows) SELECT loads = 0 AS no_dictionary_parsed FROM sift._test_col_dict_cache(false); no_dictionary_parsed ---------------------- t (1 row) -- a dictionary that the backend tests again and again is parsed and cached (about 32 tests), and then answers from the cache SELECT sum(rows) AS rows FROM generate_series(1, 40) g, LATERAL sift._test_col_counters('ds_new', '1:eq:c4ca4238-a0b9-2382-0dcc-509a6f75849b'); rows ------ 120 (1 row) SELECT loads > 0 AS parsed, hits > 0 AS then_cached FROM sift._test_col_dict_cache(false); parsed | then_cached --------+------------- f | t (1 row) SELECT n, ds_cmp('ds_new', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp ----+-------- 1 | ok 2 | ok 3 | ok 4 | ok 5 | ok 6 | ok 7 | ok 8 | ok 9 | ok 10 | ok 11 | ok 12 | ok 13 | ok 14 | ok 15 | ok 16 | ok 17 | ok 18 | ok 19 | ok 20 | ok 21 | ok 22 | ok 23 | ok 24 | ok 25 | ok 26 | ok (26 rows) -- the option off (blocks for the dictionaries that pay, without the order) and the compact form (the setting 13): the same answers, the dictionaries are parsed CREATE INDEX ds_off ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', write_buffer_limit = 256, dict_lookup = off); NOTICE: sift_col index "ds_off": 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('ds_off'); SET sift.col_format_minor = 13; CREATE INDEX ds_13 ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', write_buffer_limit = 256, dict_lookup = on); NOTICE: sift_col index "ds_13": 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('ds_13'); RESET sift.col_format_minor; SELECT index, min(format) AS format FROM (SELECT 'off' AS index, format FROM sift.col_segments('ds_off') WHERE kind = 'immutable' UNION ALL SELECT '13', format FROM sift.col_segments('ds_13') WHERE kind = 'immutable') x GROUP BY index ORDER BY index; index | format -------+-------- 13 | 12 off | 14 (2 rows) SELECT count(*) > 0 AS reset FROM sift._test_col_dict_cache(true); reset ------- t (1 row) SELECT n, ds_cmp('ds_off', preds, heapwhere), ds_cmp('ds_13', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp | ds_cmp ----+--------+-------- 1 | ok | ok 2 | ok | ok 3 | ok | ok 4 | ok | ok 5 | ok | ok 6 | ok | ok 7 | ok | ok 8 | ok | ok 9 | ok | ok 10 | ok | ok 11 | ok | ok 12 | ok | ok 13 | ok | ok 14 | ok | ok 15 | ok | ok 16 | ok | ok 17 | ok | ok 18 | ok | ok 19 | ok | ok 20 | ok | ok 21 | ok | ok 22 | ok | ok 23 | ok | ok 24 | ok | ok 25 | ok | ok 26 | ok | ok (26 rows) SELECT loads > 0 AS dictionaries_parsed FROM sift._test_col_dict_cache(false); dictionaries_parsed --------------------- t (1 row) SELECT sift.col_verify('ds_off', true), sift.col_verify('ds_13', true); col_verify | col_verify ------------+------------ | (1 row) DROP INDEX ds_off, ds_13; -- a dictionary under the minimum is kept compact (the minimum is a limit) CREATE TABLE dfew AS SELECT i AS id, 'few' || i % 300 AS s FROM generate_series(1, 6000) i; CREATE INDEX dfew_i ON dfew USING sift_col (s sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', dict_lookup = on); NOTICE: sift_col index "dfew_i": 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('dfew_i'); SELECT min(format) AS format FROM sift.col_segments('dfew_i') WHERE kind = 'immutable'; format -------- 12 (1 row) DROP TABLE dfew; SELECT set_config('sift.test_limits', 'dict_lookup_min=1', false) IS NOT NULL AS lowered; lowered --------- t (1 row) CREATE TABLE dt (id int, s text); INSERT INTO dt SELECT i, CASE i % 4 WHEN 0 THEN NULL WHEN 1 THEN 'one' WHEN 2 THEN '' ELSE repeat('x', 255) END FROM generate_series(1, 400) i; CREATE TABLE dt1 (id int, s text); INSERT INTO dt1 SELECT i, 'same' FROM generate_series(1, 100) i; CREATE INDEX dt_i ON dt USING sift_col (s sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', dict_lookup = on); NOTICE: sift_col index "dt_i": 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('dt_i'); CREATE INDEX dt1_i ON dt1 USING sift_col (s sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', dict_lookup = on); NOTICE: sift_col index "dt1_i": 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('dt1_i'); SELECT (SELECT min(format) FROM sift.col_segments('dt_i') WHERE kind = 'immutable') AS format, (SELECT min(format) FROM sift.col_segments('dt1_i') WHERE kind = 'immutable') AS format_one_string; format | format_one_string --------+------------------- 39 | 39 (1 row) SELECT (SELECT count(*) FROM sift._test_col_tids('dt_i', '0:eq:one')) AS one, (SELECT count(*) FROM sift._test_col_tids('dt_i', '0:eq:')) AS empty, (SELECT count(*) FROM sift._test_col_tids('dt_i', '0:prefix:x')) AS long, (SELECT count(*) FROM sift._test_col_tids('dt_i', '0:gt:one')) AS above_one, (SELECT count(*) FROM sift._test_col_tids('dt1_i', '0:eq:same')) AS same, (SELECT count(*) FROM sift._test_col_tids('dt1_i', '0:eq:sam')) AS shorter, (SELECT count(*) FROM sift._test_col_tids('dt1_i', '0:prefix:sa')) AS prefix; one | empty | long | above_one | same | shorter | prefix -----+-------+------+-----------+------+---------+-------- 100 | 100 | 100 | 100 | 100 | 0 | 100 (1 row) SELECT sift.col_verify('dt_i', true), sift.col_verify('dt1_i', true); col_verify | col_verify ------------+------------ | (1 row) RESET sift.test_limits; DROP TABLE dt, dt1; -- DML, the write buffer, promotion and a merge of segments of the compact and the stored form UPDATE ds SET s = 'commonprefix_' || id, u = md5('new' || id)::uuid, v = 'v' || id WHERE id % 97 = 0; DELETE FROM ds WHERE id % 89 = 0; INSERT INTO ds (id, s, u, v) SELECT i, 'late' || i % 700, md5('late' || i)::uuid, 'w' || i % 300 FROM generate_series(12001, 13000) i; SELECT n, ds_cmp('ds_new', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp ----+-------- 1 | ok 2 | ok 3 | ok 4 | ok 5 | ok 6 | ok 7 | ok 8 | ok 9 | ok 10 | ok 11 | ok 12 | ok 13 | ok 14 | ok 15 | ok 16 | ok 17 | ok 18 | ok 19 | ok 20 | ok 21 | ok 22 | ok 23 | ok 24 | ok 25 | ok 26 | ok (26 rows) VACUUM ds; SELECT n, ds_cmp('ds_new', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp ----+-------- 1 | ok 2 | ok 3 | ok 4 | ok 5 | ok 6 | ok 7 | ok 8 | ok 9 | ok 10 | ok 11 | ok 12 | ok 13 | ok 14 | ok 15 | ok 16 | ok 17 | ok 18 | ok 19 | ok 20 | ok 21 | ok 22 | ok 23 | ok 24 | ok 25 | ok 26 | ok (26 rows) SET sift.col_format_minor = 13; INSERT INTO ds (id, s, u, v) SELECT i, 'older' || i % 900, md5('older' || i)::uuid, 'x' || i % 400 FROM generate_series(13001, 14000) i; VACUUM ds; RESET sift.col_format_minor; SELECT format, count(*) AS segments FROM sift.col_segments('ds_new') WHERE kind = 'immutable' GROUP BY format ORDER BY format; format | segments --------+---------- 12 | 1 39 | 1 (2 rows) SELECT n, ds_cmp('ds_new', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp ----+-------- 1 | ok 2 | ok 3 | ok 4 | ok 5 | ok 6 | ok 7 | ok 8 | ok 9 | ok 10 | ok 11 | ok 12 | ok 13 | ok 14 | ok 15 | ok 16 | ok 17 | ok 18 | ok 19 | ok 20 | ok 21 | ok 22 | ok 23 | ok 24 | ok 25 | ok 26 | ok (26 rows) SELECT sift.col_merge('ds_new'::regclass, true) >= 0 AS merged; merged -------- t (1 row) SELECT format, count(*) AS segments FROM sift.col_segments('ds_new') WHERE kind = 'immutable' GROUP BY format ORDER BY format; format | segments --------+---------- 39 | 1 (1 row) SELECT sift.col_verify('ds_new', true); col_verify ------------ (1 row) SELECT n, ds_cmp('ds_new', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp ----+-------- 1 | ok 2 | ok 3 | ok 4 | ok 5 | ok 6 | ok 7 | ok 8 | ok 9 | ok 10 | ok 11 | ok 12 | ok 13 | ok 14 | ok 15 | ok 16 | ok 17 | ok 18 | ok 19 | ok 20 | ok 21 | ok 22 | ok 23 | ok 24 | ok 25 | ok 26 | ok (26 rows) -- a cache that holds nothing: the same answers SELECT set_config('sift.test_limits', 'dict_cache_kb=1', false) IS NOT NULL AS sized; sized ------- t (1 row) SELECT n, ds_cmp('ds_new', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp ----+-------- 1 | ok 2 | ok 3 | ok 4 | ok 5 | ok 6 | ok 7 | ok 8 | ok 9 | ok 10 | ok 11 | ok 12 | ok 13 | ok 14 | ok 15 | ok 16 | ok 17 | ok 18 | ok 19 | ok 20 | ok 21 | ok 22 | ok 23 | ok 24 | ok 25 | ok 26 | ok (26 rows) RESET sift.test_limits; -- ALTER INDEX: the option applies to the segments written afterwards SELECT sum(bytes) AS with_order FROM sift.col_sections('ds_new') WHERE section = 'dict' \gset ALTER INDEX ds_new SET (dict_lookup = off); REINDEX INDEX ds_new; NOTICE: sift_col index "ds_new": 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('ds_new'); SELECT sum(bytes) < :with_order AS without_order FROM sift.col_sections('ds_new') WHERE section = 'dict'; without_order --------------- t (1 row) ALTER INDEX ds_new SET (dict_lookup = on); REINDEX INDEX ds_new; NOTICE: sift_col index "ds_new": 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('ds_new'); SELECT min(format) AS format FROM sift.col_segments('ds_new') WHERE kind = 'immutable'; format -------- 39 (1 row) SELECT n, ds_cmp('ds_new', preds, heapwhere) FROM ds_q ORDER BY n; n | ds_cmp ----+-------- 1 | ok 2 | ok 3 | ok 4 | ok 5 | ok 6 | ok 7 | ok 8 | ok 9 | ok 10 | ok 11 | ok 12 | ok 13 | ok 14 | ok 15 | ok 16 | ok 17 | ok 18 | ok 19 | ok 20 | ok 21 | ok 22 | ok 23 | ok 24 | ok 25 | ok 26 | ok (26 rows) -- the default (auto) stores the order of the strings of a leaf that a lookup structure reads (set_columns, point_columns, return_columns) and of no other; on stores every one, off none SET max_parallel_maintenance_workers = 0; CREATE INDEX ds_on ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', dict_lookup = on); NOTICE: sift_col index "ds_on": 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('ds_on'); CREATE INDEX ds_off2 ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', dict_lookup = off); NOTICE: sift_col index "ds_off2": 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('ds_off2'); CREATE INDEX ds_auto ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off'); NOTICE: sift_col index "ds_auto": 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('ds_auto'); CREATE INDEX ds_auto_set ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', key_columns = 's'); NOTICE: sift_col index "ds_auto_set": 1 advice (track_changes), 2 limits (key_columns) DETAIL: advice: track_changes = the table has no one-column primary key; limit: key_columns = s; limit: key_columns = s HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ds_auto_set'); CREATE INDEX ds_auto_point ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', point_columns = 'u'); NOTICE: sift_col index "ds_auto_point": 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('ds_auto_point'); CREATE INDEX ds_auto_ret ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', return_columns = 's, v'); NOTICE: sift_col index "ds_auto_ret": 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('ds_auto_ret'); CREATE INDEX ds_auto_off ON ds USING sift_col (s sift.col_text_ops, u sift.col_uuid_ops, v sift.col_text_ops, id sift.col_int4_ops) WITH (count_columns = 'off', point_columns = 's, u', dict_lookup = off); NOTICE: sift_col index "ds_auto_off": 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('ds_auto_off'); RESET max_parallel_maintenance_workers; SELECT i.idx, d.leaf, d.bytes = (SELECT x.bytes FROM sift.col_sections('ds_on') x WHERE x.section = 'dict' AND x.leaf = d.leaf) AS with_order FROM (VALUES ('ds_on'), ('ds_off2'), ('ds_auto'), ('ds_auto_set'), ('ds_auto_point'), ('ds_auto_ret'), ('ds_auto_off')) i(idx), LATERAL sift.col_sections(i.idx::regclass) d WHERE d.section = 'dict' ORDER BY i.idx, d.leaf; idx | leaf | with_order ---------------+------+------------ ds_auto | s | f ds_auto | u | f ds_auto | v | f ds_auto_off | s | f ds_auto_off | u | f ds_auto_off | v | f ds_auto_point | s | f ds_auto_point | u | t ds_auto_point | v | f ds_auto_ret | s | t ds_auto_ret | u | f ds_auto_ret | v | t ds_auto_set | s | t ds_auto_set | u | f ds_auto_set | v | f ds_off2 | s | f ds_off2 | u | f ds_off2 | v | f ds_on | s | t ds_on | u | t ds_on | v | t (21 rows) SELECT sift.col_verify('ds_auto_set', true), sift.col_verify('ds_auto_point', true), sift.col_verify('ds_auto_ret', true); col_verify | col_verify | col_verify ------------+------------+------------ | | (1 row) CREATE INDEX ds_bad ON ds USING sift_col (s sift.col_text_ops, id sift.col_int4_ops) WITH (dict_lookup = sometimes); ERROR: invalid value for enum option "dict_lookup": sometimes DETAIL: Valid values are "auto", "on" and "off". CREATE INDEX ds_bool ON ds USING sift_col (s sift.col_text_ops, id sift.col_int4_ops) WITH (dict_lookup = true); NOTICE: sift_col index "ds_bool": 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('ds_bool'); DROP INDEX ds_bool; -- the default stores a dictionary in blocks (read in place) only where that adds little to the segment: uuids and hashes compress as well in blocks as whole, -- short strings that repeat their words (titles) do not and keep the compact form; the answers are the same CREATE TABLE dw AS SELECT i AS id, md5((i % 5000)::text)::uuid AS u, (SELECT string_agg(substr(md5((1 + (hashint4((i % 5000) * 31 + j) & 2147483647) % 400)::text), 1, 3 + j % 5), ' ') FROM generate_series(1, 5) j) AS w FROM generate_series(1, 20000) i; -- (dict_lookup = 'off': the blocks alone, the hash they carry by default is 5 bytes a string more: col_dict_blocks_hash) CREATE INDEX dw_i ON dw USING sift_col (id sift.col_int4_ops, u sift.col_uuid_ops, w sift.col_text_ops) WITH (count_columns = 'off', dict_lookup = 'off'); NOTICE: sift_col index "dw_i": 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('dw_i'); SET sift.col_format_minor = 13; CREATE INDEX dw_13 ON dw USING sift_col (id sift.col_int4_ops, u sift.col_uuid_ops, w sift.col_text_ops) WITH (count_columns = 'off'); NOTICE: sift_col index "dw_13": 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('dw_13'); RESET sift.col_format_minor; SELECT d.leaf, d.bytes <= x.bytes * 1.03 AS within_3_percent, d.bytes > x.bytes AS blocks FROM sift.col_sections('dw_i') d JOIN sift.col_sections('dw_13') x ON x.section = d.section AND x.leaf = d.leaf WHERE d.section = 'dict' ORDER BY d.leaf; leaf | within_3_percent | blocks ------+------------------+-------- u | t | t w | t | f (2 rows) SELECT sift.col_verify('dw_i', true), sift.col_verify('dw_13', true); col_verify | col_verify ------------+------------ | (1 row) SELECT (SELECT count(*) FROM sift._test_col_tids('dw_i', '2:eq:' || (SELECT w FROM dw WHERE id = 77))) = (SELECT count(*) FROM dw WHERE w = (SELECT w FROM dw WHERE id = 77)) AS same_answer; same_answer ------------- t (1 row) DROP TABLE dw; DROP TABLE ds CASCADE; DROP FUNCTION ds_cmp(regclass, text, text); DROP EXTENSION pg_sift_test, pg_sift CASCADE; DROP TABLE ds_q;