-- A set filter bound as one array parameter (col = ANY($1)) on a generic plan (docs/SET-FILTERS.md): the length of the array is not known to the
-- planner, which assumes 10 elements and prices an index or bitmap scan for them. The scan of Custom Scan (SiftAgg) reads the whole array into a
-- table when it starts, so the generic plan takes it by default (sift.agg_param_set_size), and the answers are those of the normal plan for every array.
CREATE EXTENSION pg_sift;
SET default_statistics_target = 10000;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE ev (id int PRIMARY KEY, dim_a_id int, small_id int2, big_id int8, kind text, amount numeric(10, 2), day date, ref int) WITH (autovacuum_enabled = false);
INSERT INTO ev
SELECT i,
       CASE WHEN i <= 5 THEN -i                           -- negative ids
            WHEN i <= 10 THEN 2147483647 - i              -- far above the others: a span no byte table covers
            WHEN i % 1000 = 0 THEN NULL
            ELSE 1 + (i::bigint * 7919) % 40000 END,
       (i % 50)::int2, 5000000000 + (i::bigint * 7919) % 40000,
       (ARRAY['a', 'b', 'c', 'd'])[1 + i % 4], (i % 1000) / 10.0, date '2024-01-01' + i % 700, i
FROM generate_series(1, 80000) i;
CREATE INDEX ev_entity ON ev (dim_a_id, day);
CREATE INDEX ev_ref ON ev (ref);
CREATE INDEX ev_col ON ev USING sift_col (dim_a_id, small_id, big_id, kind, amount, day, ref);
VACUUM (FREEZE, ANALYZE) ev;
-- the plan of a statement, EXPLAIN without the numbers
CREATE FUNCTION plan_kind(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text; r text := 'normal';
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		IF l LIKE '%Custom Scan (SiftAgg)%' THEN r := 'SiftAgg'; END IF;
		IF l LIKE '%Workers Planned: 2%' THEN r := r || ' with workers'; END IF;
	END LOOP;
	RETURN r;
END $$;
SET plan_cache_mode = force_generic_plan;
-- the pushed plan, prepared with the aggregate pushdown on, and the normal plan, prepared with it off: a generic plan is made at the first EXECUTE
PREPARE on_q(int4[]) AS
SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE day >= '2024-01-01' AND dim_a_id = ANY ($1) GROUP BY kind ORDER BY kind;
SELECT plan_kind('EXECUTE on_q(''{1,2,3}'')') AS default_costs;
 default_costs 
---------------
 SiftAgg
(1 row)

SET sift.enable_agg = off;
PREPARE off_q(int4[]) AS
SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE day >= '2024-01-01' AND dim_a_id = ANY ($1) GROUP BY kind ORDER BY kind;
SELECT plan_kind('EXECUTE off_q(''{1,2,3}'')') AS enable_agg_off;
 enable_agg_off 
----------------
 normal
(1 row)

RESET sift.enable_agg;
-- on a column of one row per value the planner's own guess of 10 elements keeps the index scan (opening the scan costs more than 10 probes); the elements assumed by sift.agg_param_set_size move the choice
PREPARE guess_q(int4[]) AS
SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE ref = ANY ($1) GROUP BY kind ORDER BY kind;
SELECT plan_kind('EXECUTE guess_q(''{1,2,3}'')') AS default_assumed;
 default_assumed 
-----------------
 SiftAgg
(1 row)

SET sift.agg_param_set_size = 0;
PREPARE guess_q0(int4[]) AS
SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE ref = ANY ($1) GROUP BY kind ORDER BY kind;
SELECT plan_kind('EXECUTE guess_q0(''{1,2,3}'')') AS planner_guess;
 planner_guess 
---------------
 normal
(1 row)

RESET sift.agg_param_set_size;
-- the plan shows the parameter, and a query SiftAgg cannot run keeps its plan whatever the size of the array
EXPLAIN (COSTS OFF) EXECUTE on_q('{1,2,3}');
                       QUERY PLAN                        
---------------------------------------------------------
 Custom Scan (SiftAgg)
   Index: ev_col
   Filters: day >= '01-01-2024'::date AND dim_a_id IN $1
   Group Keys: kind
   Pushed Aggregates: count(*), sum(ev.amount)
(5 rows)

PREPARE other_q(int4[]) AS SELECT count(*) FROM ev WHERE dim_a_id = ANY ($1) AND kind ~ '^[ab]' AND (id % 7 = 0 OR amount > 99);
SELECT plan_kind('EXECUTE other_q(''{1,2,3}'')') AS unsupported_shape;
 unsupported_shape 
-------------------
 normal
(1 row)

PREPARE rows_q(int4[]) AS SELECT id FROM ev WHERE dim_a_id = ANY ($1) ORDER BY id LIMIT 5;
SELECT plan_kind('EXECUTE rows_q(''{1,2,3}'')') AS no_aggregate;
 no_aggregate 
--------------
 normal
(1 row)

-- the same rows as the normal plan, for every shape of array
CREATE FUNCTION same_rows(arr int4[]) RETURNS TABLE (arr_len int, rows_on bigint, rows_diff bigint) LANGUAGE plpgsql AS $$
BEGIN
	EXECUTE format('CREATE TEMP TABLE r_on ON COMMIT DROP AS EXECUTE on_q(%L)', arr);
	EXECUTE format('CREATE TEMP TABLE r_off ON COMMIT DROP AS EXECUTE off_q(%L)', arr);
	RETURN QUERY SELECT coalesce(cardinality(arr), -1), (SELECT count(*) FROM r_on),
		(SELECT count(*) FROM ((TABLE r_on EXCEPT TABLE r_off) UNION ALL (TABLE r_off EXCEPT TABLE r_on)) d);
END $$;
SELECT * FROM same_rows('{1,2,3}');
 arr_len | rows_on | rows_diff 
---------+---------+-----------
       3 |       2 |         0
(1 row)

SELECT * FROM same_rows('{}');
 arr_len | rows_on | rows_diff 
---------+---------+-----------
       0 |       0 |         0
(1 row)

SELECT * FROM same_rows('{NULL}');
 arr_len | rows_on | rows_diff 
---------+---------+-----------
       1 |       0 |         0
(1 row)

SELECT * FROM same_rows('{1,NULL,3}');
 arr_len | rows_on | rows_diff 
---------+---------+-----------
       3 |       1 |         0
(1 row)

SELECT * FROM same_rows('{7,7,7,8,8}');
 arr_len | rows_on | rows_diff 
---------+---------+-----------
       5 |       2 |         0
(1 row)

SELECT * FROM same_rows(NULL);
 arr_len | rows_on | rows_diff 
---------+---------+-----------
      -1 |       0 |         0
(1 row)

SELECT * FROM same_rows('{-1,-3,-100,40001,2147483640,2147483642}');
 arr_len | rows_on | rows_diff 
---------+---------+-----------
       6 |       2 |         0
(1 row)

SELECT * FROM same_rows('{2147483640,2147483641,-5}');
 arr_len | rows_on | rows_diff 
---------+---------+-----------
       3 |       3 |         0
(1 row)

SELECT * FROM same_rows(ARRAY(SELECT generate_series(1, 40000, 4)));
 arr_len | rows_on | rows_diff 
---------+---------+-----------
   10000 |       1 |         0
(1 row)

SELECT * FROM same_rows(ARRAY(SELECT generate_series(1, 70000)));
 arr_len | rows_on | rows_diff 
---------+---------+-----------
   70000 |       4 |         0
(1 row)

SELECT * FROM same_rows(ARRAY(SELECT generate_series(1, 70000) UNION ALL SELECT NULL));
 arr_len | rows_on | rows_diff 
---------+---------+-----------
   70001 |       4 |         0
(1 row)

SELECT * FROM same_rows(ARRAY(SELECT x FROM generate_series(1, 30000) x CROSS JOIN generate_series(1, 3)));
 arr_len | rows_on | rows_diff 
---------+---------+-----------
   90000 |       4 |         0
(1 row)

-- other element types, a second set, NOT IN with a NULL, and the same query as a custom plan and as a prepared statement that turns generic
CREATE FUNCTION diff_count(a text, b text) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
	EXECUTE format('SELECT count(*) FROM ((%1$s EXCEPT %2$s) UNION ALL (%2$s EXCEPT %1$s)) d', a, b) INTO n;
	RETURN n;
END $$;
CREATE FUNCTION compare_modes(q text, VARIADIC args text[]) RETURNS TABLE (pushed text, rows_on bigint, rows_diff bigint) LANGUAGE plpgsql AS $$
DECLARE a text := array_to_string(args, ', ');
BEGIN
	EXECUTE 'PREPARE cm_on AS ' || q;
	SET LOCAL sift.enable_agg = off;
	EXECUTE 'PREPARE cm_off AS ' || q;
	SET LOCAL sift.enable_agg = on;
	EXECUTE format('CREATE TEMP TABLE cm_a ON COMMIT DROP AS EXECUTE cm_on(%s)', a);
	SET LOCAL sift.enable_agg = off;
	EXECUTE format('CREATE TEMP TABLE cm_b ON COMMIT DROP AS EXECUTE cm_off(%s)', a);
	SET LOCAL sift.enable_agg = on;
	RETURN QUERY SELECT plan_kind(format('EXECUTE cm_on(%s)', a)), (SELECT count(*) FROM cm_a), diff_count('TABLE cm_a', 'TABLE cm_b');
	DEALLOCATE cm_on;
	DEALLOCATE cm_off;
END $$;
CREATE FUNCTION set_of(n int, step int) RETURNS int4[] LANGUAGE sql AS 'SELECT array_agg(x) FROM generate_series(1, n, step) x';
SELECT * FROM compare_modes('SELECT small_id, count(*) AS n, sum(amount) AS s FROM ev WHERE small_id = ANY ($1) GROUP BY small_id', '''{1,5,9,NULL}''::int2[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE big_id = ANY ($1) GROUP BY kind', '''{5000000001,5000000007,5000000011}''::int8[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       2 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n FROM ev WHERE dim_a_id = ANY ($1) AND small_id = ANY ($2) GROUP BY kind',
                            '''{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40}''::int4[]', '''{1,2,3,4,5}''::int2[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       2 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n FROM ev WHERE dim_a_id <> ALL ($1) GROUP BY kind', '''{1,2,3}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n FROM ev WHERE dim_a_id <> ALL ($1) GROUP BY kind', '''{1,2,NULL}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       0 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n FROM ev WHERE dim_a_id <> ALL ($1) GROUP BY kind', '''{}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT count(*) AS n, count(dim_a_id) AS nn, min(dim_a_id) AS lo, max(dim_a_id) AS hi FROM ev WHERE dim_a_id = ANY ($1)', '''{-3,-2,40000,2147483640}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       1 |         0
(1 row)

-- a text list is tested once for each entry of a dictionary, with the hash of its elements built at the first test: a NULL list is no list (it crashed), and a list a PL/pgSQL
-- variable holds, packed or expanded, is read again for each entry
SELECT * FROM compare_modes('SELECT count(*) AS n, sum(amount) AS s FROM ev WHERE kind = ANY ($1)', '''{a,c}''::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       1 |         0
(1 row)

SELECT * FROM compare_modes('SELECT count(*) AS n, sum(amount) AS s FROM ev WHERE kind = ANY ($1)', '''{a,NULL}''::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       1 |         0
(1 row)

SELECT * FROM compare_modes('SELECT count(*) AS n, sum(amount) AS s FROM ev WHERE kind = ANY ($1)', 'NULL::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       1 |         0
(1 row)

SELECT * FROM compare_modes('SELECT count(*) AS n, sum(amount) AS s FROM ev WHERE kind <> ALL ($1)', '''{a,c}''::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       1 |         0
(1 row)

SELECT * FROM compare_modes('SELECT count(*) AS n, sum(amount) AS s FROM ev WHERE kind <> ALL ($1)', 'NULL::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       1 |         0
(1 row)

CREATE FUNCTION kind_count(kinds text[], expand bool) RETURNS bigint LANGUAGE plpgsql SET plan_cache_mode = force_generic_plan AS $$
DECLARE n bigint;
BEGIN
	IF expand THEN kinds[cardinality(kinds) + 1] := 'd'; END IF;
	SELECT count(*) INTO n FROM ev WHERE kind = ANY (kinds);
	RETURN n;
END $$;
SELECT kind_count('{a,c}', false) = (SELECT count(*) FROM ev WHERE kind IN ('a', 'c')) AS packed,
       kind_count('{a,c}', true) = (SELECT count(*) FROM ev WHERE kind IN ('a', 'c', 'd')) AS expanded;
 packed | expanded 
--------+----------
 t      | t
(1 row)

DROP FUNCTION kind_count(text[], bool);
-- a long list over a range wider than a byte table (4 million values) is a bit table (a hash table below sift.test_limits bits_set_values, 1024 members), for int4 and int8
-- columns, IN and NOT IN, with a NULL element, an empty list and a value far beyond the others
CREATE FUNCTION wide_set(far int, step int) RETURNS int4[] LANGUAGE sql AS 'SELECT array_agg(x) FROM (SELECT generate_series(1, 40000, step) UNION ALL SELECT far) s(x)';
CREATE FUNCTION wide_set8(far int8, step int) RETURNS int8[] LANGUAGE sql AS 'SELECT array_agg(5000000000 + x) FROM (SELECT generate_series(1, 40000, step) UNION ALL SELECT far) s(x)';
SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id = ANY ($1) GROUP BY kind', 'wide_set(30000000, 13)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id <> ALL ($1) GROUP BY kind', 'wide_set(30000000, 13)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id = ANY ($1) GROUP BY kind', 'array_append(wide_set(30000000, 13), NULL)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id <> ALL ($1) GROUP BY kind', 'array_append(wide_set(30000000, 13), NULL)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       0 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id = ANY ($1) GROUP BY kind', 'wide_set(268435455, 7)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id = ANY ($1) GROUP BY kind', 'wide_set(268435457, 7)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE big_id = ANY ($1) GROUP BY kind', 'wide_set8(5030000000, 13)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE big_id <> ALL ($1) GROUP BY kind', 'wide_set8(5030000000, 13)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE big_id = ANY ($1) GROUP BY kind', 'array_append(wide_set8(5030000000, 13), NULL)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

-- the release library has no test_limits: the same lists then run against the default limit, with the same answers
SELECT current_setting('sift.test_limits', true) IS NOT NULL AS limits \gset
\if :limits
SET sift.test_limits = 'bits_set_values=4';
\endif
SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id = ANY ($1) GROUP BY kind', '''{3,17,40000,5000000}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id <> ALL ($1) GROUP BY kind', '''{3,17,40000,5000000}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id = ANY ($1) GROUP BY kind', '''{3,17,NULL,40000,5000000}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE dim_a_id = ANY ($1) GROUP BY kind', '''{-2147483648,3,2147483647,17}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       2 |         0
(1 row)

SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE big_id = ANY ($1) GROUP BY kind', '''{5000000001,5000000007,5004000000,5000000011}''::int8[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       2 |         0
(1 row)

\if :limits
RESET sift.test_limits;
\endif
DROP FUNCTION wide_set(int, int);
DROP FUNCTION wide_set8(int8, int);
-- custom plans see the array and are as before; a statement in auto mode turns generic after five executions with the same answers
SET plan_cache_mode = force_custom_plan;
PREPARE custom_q(int4[]) AS SELECT kind, count(*) AS n FROM ev WHERE dim_a_id = ANY ($1) GROUP BY kind ORDER BY kind;
SELECT * FROM same_rows('{1,2,3}');
 arr_len | rows_on | rows_diff 
---------+---------+-----------
       3 |       2 |         0
(1 row)

EXECUTE custom_q('{1,2,3}');
 kind | n 
------+---
 c    | 2
 d    | 2
(2 rows)

SET plan_cache_mode = auto;
PREPARE auto_q(int4[]) AS SELECT kind, count(*) AS n FROM ev WHERE day >= '2024-01-01' AND dim_a_id = ANY ($1) GROUP BY kind ORDER BY kind;
EXECUTE auto_q('{1,2,3}');
 kind | n 
------+---
 c    | 2
 d    | 2
(2 rows)

EXECUTE auto_q('{1,2,3}');
 kind | n 
------+---
 c    | 2
 d    | 2
(2 rows)

EXECUTE auto_q('{1,2,3}');
 kind | n 
------+---
 c    | 2
 d    | 2
(2 rows)

EXECUTE auto_q('{1,2,3}');
 kind | n 
------+---
 c    | 2
 d    | 2
(2 rows)

EXECUTE auto_q('{1,2,3}');
 kind | n 
------+---
 c    | 2
 d    | 2
(2 rows)

EXECUTE auto_q('{1,2,3}');
 kind | n 
------+---
 c    | 2
 d    | 2
(2 rows)

EXECUTE auto_q('{4,5,6}');
 kind | n 
------+---
 a    | 2
 b    | 2
 d    | 2
(3 rows)

SET plan_cache_mode = force_generic_plan;
CREATE TEMP TABLE auto_r AS EXECUTE auto_q('{4,5,6}');
SELECT diff_count('TABLE auto_r', 'SELECT kind, count(*) FROM ev WHERE day >= ''2024-01-01'' AND dim_a_id IN (4, 5, 6) GROUP BY kind') AS auto_diff;
 auto_diff 
-----------
         0
(1 row)

-- other key types: an array of text, date, timestamp, timestamptz, int2 or int8 reaches the scan too (the types without a byte table hash the array once),
-- with the answers of the normal plan for NULL elements, an empty array, NOT IN and a value the table lacks
CREATE FUNCTION key_set(a int, b int, step int) RETURNS text[] LANGUAGE sql AS $$ SELECT array_agg('key-' || x) FROM generate_series(a, b, step) x $$;
CREATE FUNCTION int8_set(a int8, b int8, step int8) RETURNS int8[] LANGUAGE sql AS $$ SELECT array_agg(x) FROM generate_series(a, b, step) x $$;
CREATE TABLE evk (id int PRIMARY KEY, k2 int2, k8 int8, kt text, kd date, kts timestamp, ktz timestamptz, g int, amount numeric(10, 2))
	WITH (autovacuum_enabled = false);
INSERT INTO evk
SELECT i, CASE WHEN i % 997 = 0 THEN NULL ELSE (i * 7)::int % 3000 - 1500 END,
       CASE WHEN i % 997 = 0 THEN NULL ELSE 5000000000 + (i::bigint * 7919) % 3000 END,
       CASE WHEN i % 997 = 0 THEN NULL ELSE 'key-' || (i::bigint * 7919) % 3000 END,
       date '2024-01-01' + (i * 7) % 3000,
       timestamp '2024-01-01 00:00:00' + ((i * 7) % 3000) * interval '1 minute',
       timestamptz '2024-01-01 00:00:00+00' + ((i * 7) % 3000) * interval '1 minute',
       i % 5, (i % 100) / 10.0
FROM generate_series(1, 12000) i;
INSERT INTO evk VALUES (12001, 1, 9223372036854775807, 'max', '2024-01-01', 'infinity', 'infinity', 1, 1.5),
                       (12002, 1, -9223372036854775808, 'min', '2024-01-01', '-infinity', '-infinity', 2, 2.5),
                       (12003, 1, 5000000000 + 90000000, 'far', '2024-01-01', '2024-01-01', '2024-01-01+00', 3, 3.5);
CREATE INDEX evk_col ON evk USING sift_col (k2, k8, kt, kd, kts, ktz, g, amount);
VACUUM (FREEZE, ANALYZE) evk;
SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k2 = ANY ($1) GROUP BY g', '''{-1500,-3,0,7,1499,NULL}''::int2[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 = ANY ($1) GROUP BY g', '''{5000000001,5000000007,5000002999,4999999999,NULL}''::int8[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

-- int8 and timestamp lists: a dense range, one too wide for a table (hashed), the extremes of the type, a NULL
SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 = ANY ($1) GROUP BY g', '''{5000000001,5000000007,5090000000,9223372036854775807,-9223372036854775808,NULL}''::int8[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 = ANY ($1) GROUP BY g', '''{5000000001,5000000002}''::int8[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       2 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 = ANY ($1) GROUP BY g', '''{}''::int8[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       0 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 <> ALL ($1) GROUP BY g', '''{5000000001,5090000000}''::int8[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       5 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 <> ALL ($1) GROUP BY g', '''{5000000001,NULL}''::int8[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       0 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 = ANY ($1) GROUP BY g', 'int8_set(5000000000, 5000003000, 7)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       5 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 = ANY ($1) GROUP BY g', 'int8_set(-4000000000000000000, 4000000000000000000, 100000000000000000)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       0 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE kts = ANY ($1) GROUP BY g', '''{infinity,-infinity,"2024-01-01 00:07:00"}''::timestamp[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       2 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE ktz = ANY ($1) GROUP BY g', '''{infinity,-infinity,"2024-01-01 00:07:00+00"}''::timestamptz[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       2 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE k8 = ANY ($1) GROUP BY g', '''{1,2,3}''::int4[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       0 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE kt = ANY ($1) GROUP BY g', '''{key-1,key-7,key-2999,key-3000,NULL}''::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE kt = ANY ($1) GROUP BY g', '''{}''::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       0 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE kt <> ALL ($1) GROUP BY g', '''{key-1,key-7,key-2999}''::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       5 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE kt <> ALL ($1) GROUP BY g', '''{key-1,NULL}''::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       0 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE kd = ANY ($1) GROUP BY g', '''{2024-01-01,2024-01-08,2032-03-20,1999-01-01,NULL}''::date[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE kd = ANY ($1) GROUP BY g', '''{2024-01-01,2032-03-20}''::date[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       4 |         0
(1 row)

SELECT * FROM compare_modes('SELECT count(*) AS n, sum(amount) AS s FROM evk WHERE kd = ANY ($1)', '''{2024-01-01,2032-03-20}''::date[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       1 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE kts = ANY ($1) GROUP BY g', '''{"2024-01-01 00:07:00","2024-01-01 00:14:00","2024-01-01 00:01:00",NULL}''::timestamp[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n, sum(amount) AS s FROM evk WHERE ktz = ANY ($1) GROUP BY g', '''{"2024-01-01 00:07:00+00","2024-01-01 00:14:00+00","2024-01-01 00:01:00+00",NULL}''::timestamptz[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n FROM evk WHERE k8 = ANY ($1) AND kt = ANY ($2) GROUP BY g',
                            '''{5000000001,5000000007,5000000011,5000000013}''::int8[]', '''{key-1,key-7,key-11,key-13}''::text[]');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       3 |         0
(1 row)

SELECT * FROM compare_modes('SELECT g, count(*) AS n FROM evk WHERE kt = ANY ($1) GROUP BY g',
                            'key_set(0, 2999, 3)');
 pushed  | rows_on | rows_diff 
---------+---------+-----------
 SiftAgg |       5 |         0
(1 row)

DROP TABLE evk;
DROP FUNCTION key_set(int, int, int);
DROP FUNCTION int8_set(int8, int8, int8);
-- parallel workers
SET plan_cache_mode = force_generic_plan;
SET max_parallel_workers_per_gather = 2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
SET seq_page_cost = 10000;
SET sift.agg_preagg = off;
SELECT * FROM compare_modes('SELECT kind, count(*) AS n, sum(amount) AS s FROM ev WHERE day >= ''2024-01-01'' AND dim_a_id = ANY ($1) GROUP BY kind',
                            'set_of(40000, 3)');
        pushed        | rows_on | rows_diff 
----------------------+---------+-----------
 SiftAgg with workers |       4 |         0
(1 row)

DEALLOCATE ALL;
DROP FUNCTION compare_modes(text, text[]);
DROP FUNCTION set_of(int, int);
DROP FUNCTION diff_count(text, text);
DROP FUNCTION same_rows(int4[]);
DROP FUNCTION plan_kind(text);
DROP TABLE ev;
DROP EXTENSION pg_sift;