-- The leader merges the values an ordered-set aggregate keeps (percentile, mode) one by one, as costly as the worker was to insert them: 40 ns a
-- distinct (group, value) pair, 25 ms of the leader for the 600,000 pairs of two percentiles over 300,000 rows, which the scan alone does in 70 ms.
-- Priced at the pairs, a scan with workers costs more than the scan alone when the values are all different, and stays cheap when a column has few.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
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 max_parallel_workers_per_gather = 2;
CREATE TABLE ow (id int, g int, amount float8, grade int);
INSERT INTO ow SELECT i, i % 40, i * 0.37, i % 5 FROM generate_series(1, 30000) i;
CREATE INDEX ow_col ON ow USING sift_col (id, g, amount, grade);
NOTICE:  sift_col index "ow_col": 1 automatic choice (rollup_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; rollup_columns = g, grade
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ow_col');
VACUUM ANALYZE ow;
CREATE FUNCTION ow_plan(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text; scan bool := false; workers bool := false;
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		scan := scan OR l LIKE '%SiftAgg%';
		workers := workers OR l LIKE '%Workers Planned%';
	END LOOP;
	RETURN CASE WHEN NOT scan THEN 'normal' WHEN workers THEN 'scan, workers' ELSE 'scan' END;
END $$;
CREATE TABLE ow_q (tag text, q text);
INSERT INTO ow_q VALUES
	('all_distinct', 'SELECT g, percentile_disc(0.5) WITHIN GROUP (ORDER BY amount), mode() WITHIN GROUP (ORDER BY amount) FROM ow GROUP BY g'),
	('few_distinct', 'SELECT g, mode() WITHIN GROUP (ORDER BY grade) FROM ow GROUP BY g');
SELECT tag, ow_plan(q) FROM ow_q ORDER BY tag;
     tag      |    ow_plan    
--------------+---------------
 all_distinct | scan
 few_distinct | scan, workers
(2 rows)

-- the answer is the same with and without the scan
CREATE FUNCTION ow_rows(q text, push bool) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.rows_agg(q, push) $$;
SELECT tag, md5(ow_rows(q, true)) = md5(ow_rows(q, false)) AS same FROM ow_q ORDER BY tag;
     tag      | same 
--------------+------
 all_distinct | t
 few_distinct | t
(2 rows)

DROP FUNCTION ow_rows(text, bool);
DROP FUNCTION ow_plan(text);
DROP TABLE ow_q, ow;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;