sort-vs-mergeappend.sql

\set size 1000000

DROP TABLE IF EXISTS test CASCADE;
DEALLOCATE ALL;

DO $$
DECLARE
  parts integer := 16;
  i integer;
BEGIN
  -- Remove shreds, if still exists
  FOR i IN 1..parts LOOP
    EXECUTE format('DROP TABLE IF EXISTS l%s', i);
  END LOOP;

  CREATE TEMP TABLE test (x real, y real DEFAULT random())
  PARTITION BY HASH (x);
  
  FOR i IN 1..parts LOOP
    EXECUTE format('CREATE TEMP TABLE l%s PARTITION OF test FOR VALUES WITH (modulus %s, remainder %s)', i, parts, i-1);
  END LOOP;
END$$;

INSERT INTO test (x) SELECT random() FROM generate_series(1, :size);
CREATE INDEX ON test (y);
ANALYZE;
ANALYZE test;

SET work_mem = '128MB';
SET plan_cache_mode = 'force_generic_plan';
SET max_parallel_workers_per_gather = 0;
SET enable_indexscan = 'off';
SET enable_bitmapscan = 'off';

PREPARE tst AS SELECT * FROM test ORDER BY y;
EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, BUFFERS OFF) EXECUTE tst;