init_parts.sql

application/sql

Download

DROP FUNCTION IF EXISTS test_func;
DROP TABLE IF EXISTS parent CASCADE;

CREATE OR REPLACE FUNCTION test_func(x integer) RETURNS Integer[] AS $$
  SELECT array_agg(id) AS arr FROM generate_series(1, x, 1) AS id
$$ LANGUAGE SQL IMMUTABLE;

\set parts 100
CREATE TABLE parent (
  x integer,
  payload text
) PARTITION BY RANGE (x);

SELECT format('
			  CREATE TABLE part_%s
			  PARTITION OF parent
			  FOR VALUES FROM (%s) TO (%s);',
			  x, x, x+1)
FROM generate_series(1, :parts ,1) x
\gexec
ANALYZE parent;

\o out.txt
\echo PARTS: :parts
EXPLAIN (SUMMARY ON)
SELECT * FROM parent WHERE x = ANY (test_func(1));
EXPLAIN (SUMMARY ON)
SELECT * FROM parent WHERE x = ANY (test_func(10));
EXPLAIN (SUMMARY ON)
SELECT * FROM parent WHERE x = ANY (test_func(100));
EXPLAIN (SUMMARY ON)
SELECT * FROM parent WHERE x = ANY (test_func(1000));
EXPLAIN (SUMMARY ON)
SELECT * FROM parent WHERE x = ANY (test_func(10000));
\o

/*
SUMMARY:
========

master:
elems	1		1E1		1E2		1E3		1E4	
1		28kB	50kB	0.3MB	2.5MB	25MB
10		45kB	143kB	0.6MB	4.8MB	47MB
100		208kB	125kB	3.3MB	27MB	274MB

patched:
elems	1		1E1		1E2		1E3		1E4	
1		28kB	48kB	0.25MB	2.2MB	22.8MB
10		44kB	100kB	313kB	2.4MB	23.7MB
100		208kB	101kB	0.9MB	3.7MB	32.4MB
*/