-- mode(), percentile_cont and percentile_disc of Custom Scan (SiftAgg) keep the values of a group in a histogram and pick the ranks the final functions of PostgreSQL pick, without -- sorting the histogram (a quickselect of the positions of all the aggregates that share the argument). Every statement is run pushed down and on the normal plan: the rows must be the same, -- for ties, all-distinct values, NaN, infinities, -0, NULLs, DESC, fractions at the ends, groups of one and two values, aggregates that share an argument and those that do not, -- a FILTER, a histogram that spills to a tuplesort (work_mem), and parallel workers (their histograms are merged). CREATE EXTENSION pg_sift; CREATE EXTENSION pg_sift_helpers; SET max_parallel_workers_per_gather = 0; SET sift.agg_force = on; -- 12 digits: a PostgreSQL built with fused multiply-add interpolates in the last bit of a float8 differently SET extra_float_digits = -3; CREATE TABLE os (id int, g int, small int, f float8, f4 float4, d date, n int2, i8 bigint, ts timestamp, tz timestamptz, nm numeric(12,3), pad text DEFAULT repeat('x', 50)); INSERT INTO os (id, g, small, f, f4, d, n, i8, ts, tz, nm) SELECT i, CASE WHEN i % 11 = 0 THEN 0 WHEN i < 50 THEN i ELSE 1 + i % 37 END, i % 7, CASE WHEN i % 997 = 0 THEN NULL WHEN i % 313 = 0 THEN 'NaN' WHEN i % 331 = 0 THEN 'Infinity' WHEN i % 337 = 0 THEN '-Infinity' WHEN i % 311 = 0 THEN '-0' WHEN i % 5 = 0 THEN (i % 13)::float8 ELSE ((i * 7919) % 100003) / 7.0 END, ((i * 31) % 5000) / 3.0, date '2020-01-01' + (i * 7) % 900, (i % 300 - 150)::int2, CASE WHEN i % 991 = 0 THEN NULL WHEN i % 3 = 0 THEN (i % 17) - 8 WHEN i % 7 = 0 THEN 4611686018427387904 + i ELSE (i * 104729)::bigint * (CASE WHEN i % 2 = 0 THEN 1 ELSE -1 END) * 1000003 END, CASE WHEN i % 983 = 0 THEN NULL WHEN i % 941 = 0 THEN 'infinity' WHEN i % 947 = 0 THEN '-infinity' ELSE timestamp '2001-02-03 04:05:06.789012' + (i * 7919 % 1000003) * interval '1 minute' - (i % 5) * interval '1 us' END, CASE WHEN i % 977 = 0 THEN NULL ELSE timestamptz '2020-06-01 00:00:00+00' + (i * 31 % 5003) * interval '1 hour' END, CASE WHEN i % 971 = 0 THEN NULL WHEN i % 967 = 0 THEN 'NaN' WHEN i % 5 = 0 THEN (i % 13) ELSE ((i * 7919) % 1000003 - 500000) / 7.0 END FROM generate_series(1, 20000) i; -- groups of one and of two values, and one of -0 only INSERT INTO os (id, g, small, f, f4, d, n, i8, ts, tz, nm) VALUES (100001, 9001, 1, 5.5, 1.5, '2024-01-01', 3, 7, '2024-01-01', '2024-01-01+00', 1.5), (100002, 9002, 1, 1.5, 2.5, '2024-01-02', 1, -9223372036854775807 - 1, '2024-01-02', '2024-01-02+00', -2.25), (100003, 9002, 2, 2.5, 3.5, '2024-01-03', 2, 9223372036854775807, '2024-01-03', '2024-01-03+00', 2.5), (100004, 9003, 3, '-0', 0.5, '2024-01-04', 0, 0, '2024-01-04', '2024-01-04+00', 0), (100005, 9003, 3, '-0', 0.5, '2024-01-04', 0, 0, '2024-01-04', '2024-01-04+00', 0); CREATE INDEX os_col ON os USING sift_col (g, small, f, f4, d, n, i8, ts, tz, nm); NOTICE: sift_col index "os_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, small HINT: Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('os_col'); VACUUM (FREEZE, DISABLE_PAGE_SKIPPING, ANALYZE) os; CREATE FUNCTION plan_of(q text) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.plan_siftagg_or_normal(q) $$; CREATE FUNCTION same(q text) RETURNS TABLE (plan text, result_rows bigint, vs_normal_plan bigint) LANGUAGE plpgsql AS $$ BEGIN plan := plan_of(q); -- -0 and 0 are one value to float8: which of them a percentile returns among rows that tie is the sort's pick (the normal plan gives another with the order of the rows, -- a parallel scan changes it from run to run), and the scan keeps one zero: the sign of a zero is not compared EXECUTE format('CREATE TEMP TABLE r_on ON COMMIT DROP AS SELECT regexp_replace(t::text, ''(?<=[(,])-0(?=[,)])'', ''0'', ''g'') AS j FROM (%s) t', q); SET LOCAL sift.enable_agg = off; EXECUTE format('CREATE TEMP TABLE r_off ON COMMIT DROP AS SELECT regexp_replace(t::text, ''(?<=[(,])-0(?=[,)])'', ''0'', ''g'') AS j FROM (%s) t', q); RESET sift.enable_agg; result_rows := (SELECT count(*) FROM r_on); vs_normal_plan := (SELECT count(*) FROM ((TABLE r_on EXCEPT TABLE r_off) UNION ALL (TABLE r_off EXCEPT TABLE r_on)) d); DROP TABLE r_on, r_off; RETURN NEXT; END $$; CREATE FUNCTION all_same() RETURNS TABLE (n int, plan text, result_rows bigint, vs_normal_plan bigint) LANGUAGE plpgsql AS $$ DECLARE q text[] := ARRAY[ 'SELECT g, percentile_cont(0.5) WITHIN GROUP (ORDER BY f) FROM os GROUP BY g', 'SELECT g, percentile_cont(0) WITHIN GROUP (ORDER BY f), percentile_cont(1) WITHIN GROUP (ORDER BY f), percentile_cont(0.25) WITHIN GROUP (ORDER BY f), percentile_cont(0.999) WITHIN GROUP (ORDER BY f) FROM os GROUP BY g', 'SELECT small, percentile_cont(0.5) WITHIN GROUP (ORDER BY f DESC), percentile_cont(0.1) WITHIN GROUP (ORDER BY f DESC) FROM os GROUP BY small', 'SELECT g, percentile_disc(0.5) WITHIN GROUP (ORDER BY f), percentile_disc(0.9) WITHIN GROUP (ORDER BY f DESC) FROM os GROUP BY g', 'SELECT percentile_cont(0.37) WITHIN GROUP (ORDER BY f), percentile_disc(0.37) WITHIN GROUP (ORDER BY f) FROM os', 'SELECT g, percentile_disc(0.5) WITHIN GROUP (ORDER BY d), percentile_disc(0.25) WITHIN GROUP (ORDER BY n), percentile_disc(0.75) WITHIN GROUP (ORDER BY f4) FROM os GROUP BY g', 'SELECT small, percentile_cont(0.5) WITHIN GROUP (ORDER BY f) FILTER (WHERE g < 20), percentile_cont(0.5) WITHIN GROUP (ORDER BY f) FROM os GROUP BY small', 'SELECT g, percentile_cont(0.5) WITHIN GROUP (ORDER BY f * 2.0 - 1.0), count(*), sum(f4) FROM os WHERE small <> 3 GROUP BY g', 'SELECT g % 3, percentile_cont(0.5) WITHIN GROUP (ORDER BY f), percentile_cont(0.5) WITHIN GROUP (ORDER BY f4::float8) FROM os WHERE g IN (1, 2, 3, 9001, 9002, 40) GROUP BY 1', 'SELECT g, percentile_disc(0.5) WITHIN GROUP (ORDER BY small) FROM os GROUP BY g', 'SELECT g, percentile_disc(0.5) WITHIN GROUP (ORDER BY i8), percentile_disc(0.9) WITHIN GROUP (ORDER BY i8 DESC), percentile_disc(0) WITHIN GROUP (ORDER BY i8), percentile_disc(1) WITHIN GROUP (ORDER BY i8) FROM os GROUP BY g', 'SELECT small, percentile_disc(0.5) WITHIN GROUP (ORDER BY ts), percentile_disc(0.25) WITHIN GROUP (ORDER BY ts DESC), percentile_disc(0.5) WITHIN GROUP (ORDER BY tz) FROM os GROUP BY small', 'SELECT g, percentile_disc(0.5) WITHIN GROUP (ORDER BY nm), percentile_disc(0.95) WITHIN GROUP (ORDER BY nm), percentile_disc(0.1) WITHIN GROUP (ORDER BY nm DESC) FROM os GROUP BY g', 'SELECT small, percentile_disc(0.5) WITHIN GROUP (ORDER BY nm * 2 - 1), percentile_disc(0.5) WITHIN GROUP (ORDER BY nm * f4::numeric) FROM os WHERE f4 < 1000 GROUP BY small', 'SELECT percentile_disc(0.37) WITHIN GROUP (ORDER BY i8), percentile_disc(0.37) WITHIN GROUP (ORDER BY ts), percentile_disc(0.37) WITHIN GROUP (ORDER BY nm) FROM os', 'SELECT g % 3, percentile_disc(0.5) WITHIN GROUP (ORDER BY nm) FILTER (WHERE g < 20), percentile_disc(0.5) WITHIN GROUP (ORDER BY i8), percentile_cont(0.5) WITHIN GROUP (ORDER BY f) FROM os GROUP BY 1', 'SELECT g, mode() WITHIN GROUP (ORDER BY f), mode() WITHIN GROUP (ORDER BY f DESC), mode() WITHIN GROUP (ORDER BY f4), mode() WITHIN GROUP (ORDER BY d) FROM os GROUP BY g', 'SELECT small, mode() WITHIN GROUP (ORDER BY n), mode() WITHIN GROUP (ORDER BY n DESC), mode() WITHIN GROUP (ORDER BY i8), mode() WITHIN GROUP (ORDER BY ts DESC), mode() WITHIN GROUP (ORDER BY tz), mode() WITHIN GROUP (ORDER BY nm) FROM os GROUP BY small', 'SELECT g, mode() WITHIN GROUP (ORDER BY small), percentile_disc(0.5) WITHIN GROUP (ORDER BY small), mode() WITHIN GROUP (ORDER BY small DESC) FROM os GROUP BY g', 'SELECT mode() WITHIN GROUP (ORDER BY f), mode() WITHIN GROUP (ORDER BY nm * 2), mode() WITHIN GROUP (ORDER BY n) FROM os']; BEGIN FOR n IN 1..array_length(q, 1) LOOP RETURN QUERY SELECT n, s.plan, s.result_rows, s.vs_normal_plan FROM same(q[n]) s; END LOOP; END $$; SELECT * FROM all_same(); n | plan | result_rows | vs_normal_plan ----+---------+-------------+---------------- 1 | SiftAgg | 52 | 0 2 | SiftAgg | 52 | 0 3 | SiftAgg | 7 | 0 4 | SiftAgg | 52 | 0 5 | SiftAgg | 1 | 0 6 | SiftAgg | 52 | 0 7 | SiftAgg | 7 | 0 8 | SiftAgg | 49 | 0 9 | SiftAgg | 3 | 0 10 | SiftAgg | 52 | 0 11 | SiftAgg | 52 | 0 12 | SiftAgg | 7 | 0 13 | SiftAgg | 52 | 0 14 | SiftAgg | 7 | 0 15 | SiftAgg | 1 | 0 16 | SiftAgg | 3 | 0 17 | SiftAgg | 52 | 0 18 | SiftAgg | 7 | 0 19 | SiftAgg | 52 | 0 20 | SiftAgg | 1 | 0 (20 rows) -- the histogram spills to a tuplesort: the sorted walk, not the quickselect SET work_mem = '64kB'; SELECT * FROM all_same(); n | plan | result_rows | vs_normal_plan ----+---------+-------------+---------------- 1 | SiftAgg | 52 | 0 2 | SiftAgg | 52 | 0 3 | SiftAgg | 7 | 0 4 | SiftAgg | 52 | 0 5 | SiftAgg | 1 | 0 6 | SiftAgg | 52 | 0 7 | SiftAgg | 7 | 0 8 | SiftAgg | 49 | 0 9 | SiftAgg | 3 | 0 10 | SiftAgg | 52 | 0 11 | SiftAgg | 52 | 0 12 | SiftAgg | 7 | 0 13 | SiftAgg | 52 | 0 14 | SiftAgg | 7 | 0 15 | SiftAgg | 1 | 0 16 | SiftAgg | 3 | 0 17 | SiftAgg | 52 | 0 18 | SiftAgg | 7 | 0 19 | SiftAgg | 52 | 0 20 | SiftAgg | 1 | 0 (20 rows) RESET work_mem; -- parallel workers merge their histograms SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SET min_parallel_table_scan_size = 0; SET max_parallel_workers_per_gather = 2; SELECT * FROM all_same(); n | plan | result_rows | vs_normal_plan ----+---------+-------------+---------------- 1 | SiftAgg | 52 | 0 2 | SiftAgg | 52 | 0 3 | SiftAgg | 7 | 0 4 | SiftAgg | 52 | 0 5 | SiftAgg | 1 | 0 6 | SiftAgg | 52 | 0 7 | SiftAgg | 7 | 0 8 | SiftAgg | 49 | 0 9 | SiftAgg | 3 | 0 10 | SiftAgg | 52 | 0 11 | SiftAgg | 52 | 0 12 | SiftAgg | 7 | 0 13 | SiftAgg | 52 | 0 14 | SiftAgg | 7 | 0 15 | SiftAgg | 1 | 0 16 | SiftAgg | 3 | 0 17 | SiftAgg | 52 | 0 18 | SiftAgg | 7 | 0 19 | SiftAgg | 52 | 0 20 | SiftAgg | 1 | 0 (20 rows) RESET ALL; DROP TABLE os; DROP FUNCTION all_same(), same(text), plan_of(text); DROP EXTENSION pg_sift_helpers; DROP EXTENSION pg_sift;