-- The groups of 1 to 5 integer keys in hash mode (the probe is inlined for 1 to 4 keys, and a table of more than 131,072 slots prefetches the slot of a tuple): the rows are
-- those of the normal plan, with NULL keys, a few groups (the table is forced to the hash by a limit on the direct arrays) and about 200,000.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET max_parallel_workers_per_gather = 0;
SET max_parallel_maintenance_workers = 0;
SET work_mem = '256MB';
CREATE TABLE h (id int, k1 int, k2 int, k3 int, k4 int, k5 int, v int) WITH (fillfactor = 20, autovacuum_enabled = false);
INSERT INTO h SELECT i, i % 1000, i % 997, CASE WHEN i % 13 = 0 THEN NULL ELSE i % 991 END, i % 983, CASE WHEN i % 17 = 0 THEN NULL ELSE i % 977 END, (i * 7) % 1000
FROM generate_series(1, 200000) i;
CREATE INDEX h_col ON h USING sift_col (id, k1, k2, k3, k4, k5, v);
NOTICE:  sift_col index "h_col": 1 automatic choice (order_array_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; order_array_columns = id, k1, v
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('h_col');
NOTICE:  sift_col index "h_col" is column format minor 38, which a library before 3.7 cannot read
HINT:  SET sift.col_format_minor = 14 before the build to write a format the older library reads.
VACUUM ANALYZE h;
CREATE FUNCTION h_rows(q text, push bool) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r text;
BEGIN
	PERFORM set_config('sift.enable_agg', push::text, true);
	EXECUTE format('SELECT md5(string_agg(q::text, E''\n'' ORDER BY q::text)) || '' '' || count(*) FROM (%s) q', q) INTO r;
	RETURN r;
END $$;
CREATE FUNCTION h_scan(q text) RETURNS bool LANGUAGE sql AS $$ SELECT sift_helpers.plan_has_siftagg_pushdown_on(q) $$;
CREATE TABLE h_q (n int, q text);
INSERT INTO h_q VALUES
	(1, 'SELECT k1, count(*) AS n, sum(v) AS s FROM h GROUP BY k1'),
	(2, 'SELECT k1, k2, count(*) AS n, sum(v) AS s FROM h GROUP BY k1, k2'),
	(3, 'SELECT k1, k3, count(*) AS n, sum(v) AS s FROM h GROUP BY k1, k3'),
	(4, 'SELECT k1, k2, k3, count(*) AS n, min(v) AS lo FROM h GROUP BY k1, k2, k3'),
	(5, 'SELECT k2, k3, k4, k5, count(*) AS n, max(v) AS hi FROM h GROUP BY k2, k3, k4, k5'),
	(6, 'SELECT k1, k2, k3, k4, k5, count(*) AS n FROM h GROUP BY k1, k2, k3, k4, k5'),
	(7, 'SELECT k3, k5, count(*) AS n, sum(v) AS s FROM h WHERE id <= 5000 GROUP BY k3, k5');
-- the plan is the scan, and the rows are the normal plan's: the direct arrays when they fit, then the hash table forced for every size
SELECT n, h_scan(q) AS scan, h_rows(q, true) = h_rows(q, false) AS direct_or_default FROM h_q ORDER BY n;
 n | scan | direct_or_default 
---+------+-------------------
 1 | t    | t
 2 | t    | t
 3 | t    | t
 4 | t    | t
 5 | t    | t
 6 | t    | t
 7 | t    | t
(7 rows)

SET sift.test_direct_max = 64;
SELECT n, h_rows(q, true) = h_rows(q, false) AS hash FROM h_q ORDER BY n;
 n | hash 
---+------
 1 | t
 2 | t
 3 | t
 4 | t
 5 | t
 6 | t
 7 | t
(7 rows)

RESET sift.test_direct_max;
DROP TABLE h_q;
DROP FUNCTION h_rows(text, bool), h_scan(text);
DROP TABLE h;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;