-- A wildcard path (m.*) keeps a dictionary of at most 65,536 distinct keys per index. The keys past it, and their values, are stored as overflow and
-- read from the heap; CREATE INDEX says how many units hold one. sift.test_limits lowers the dictionary so that a few thousand documents pass it.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_test;
SET sift.async_maintenance = off;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 10000; SET random_page_cost = 10000;
\ir test/agg_helpers.sql
\set ECHO none
\ir test/agg_same_helpers.sql
\set ECHO none
-- 3 units of about 1,000 documents (a document is about 1.6 kB): the first 16 keys fill the dictionary of 16, the second unit brings keys it has not
-- seen, the third only those it has
CREATE TABLE jw (id int, pad text, m jsonb) WITH (autovacuum_enabled = false);
INSERT INTO jw
SELECT i, repeat(md5(i::text), 50),
       CASE WHEN i % 100 = 0 AND i BETWEEN 1200 AND 1800 THEN jsonb_build_object('k' || (i % 16), i, 'z' || i, 1)
            ELSE jsonb_build_object('k' || (i % 16), i, 'k' || ((i + 5) % 16), 2) END
FROM generate_series(1, 3000) i;
-- the default dictionary holds them all: no NOTICE
CREATE INDEX jw_all ON jw USING sift_col (id, m) WITH (paths = 'm.* int4');
NOTICE:  sift_col index "jw_all": 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('jw_all');
DROP INDEX jw_all;
SET sift.test_limits = 'shred_keys=16';
CREATE INDEX jw_col ON jw USING sift_col (id, m) WITH (paths = 'm.* int4');
NOTICE:  sift_col index "jw_col": 1 advice (track_changes), 1 limit (paths)
DETAIL:  advice: track_changes = the table has no one-column primary key; limit: paths = m.*
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('jw_col');
VACUUM (FREEZE, ANALYZE) jw;
SELECT sift.col_verify('jw_col', true);
 col_verify 
------------
 
(1 row)

-- the query that reads the keys keeps the normal plan, with the same answer; a query that does not read them is scanned
SELECT pushed('SELECT e.key, sum(e.value::int) FROM jw, jsonb_each_text(m) e GROUP BY e.key') AS keys_scanned,
	same('SELECT e.key, sum(e.value::int) FROM jw, jsonb_each_text(m) e GROUP BY e.key');
 keys_scanned | same 
--------------+------
 f            | same
(1 row)

SELECT pushed('SELECT id % 7, count(*), sum(id) FROM jw GROUP BY 1') AS ids_scanned, same('SELECT id % 7, count(*), sum(id) FROM jw GROUP BY 1');
 ids_scanned | same 
-------------+------
 t           | same
(1 row)

RESET sift.test_limits;
DROP TABLE jw;
DROP FUNCTION runq(text, bool), same(text), pushed(text);
DROP EXTENSION pg_sift_test;
DROP EXTENSION pg_sift;
DROP FUNCTION outcome(text,boolean), runs_in_scan(text);