test-agg-sorted.sql

-- Materialize "table" from
-- https://postgr.es/m/20200625203629.7m6yvut7eqblgmfo@alap3.anarazel.de
-- example:
CREATE TABLE agg_test AS
  SELECT a, b FROM (SELECT generate_series(1, 10000)) a(a), (SELECT generate_series(1, 10000)) b(b);
-- Duplicate table, but random order:
CREATE TABLE random_agg_test(LIKE agg_test);
INSERT INTO random_agg_test SELECT * FROM agg_test ORDER BY random();

-- Query showing how sort order alone can significantly alter performance
-- characteristics:
ANALYZE agg_test;
ANALYZE random_agg_test;
SET work_mem='200MB';
-- Sorted original spills and has lots of batches:
EXPLAIN ANALYZE
SELECT a, array_agg(b) FROM agg_test
  GROUP BY a HAVING array_length(array_agg(b), 1) = 0;
-- Random variant does not spill at all:
EXPLAIN ANALYZE
SELECT a, array_agg(b) FROM random_agg_test
  GROUP BY a HAVING array_length(array_agg(b), 1) = 0;