repro.sql

text/plain

Download

DROP TABLE IF EXISTS pr CASCADE;
DROP TABLE IF EXISTS ps CASCADE;
CREATE TABLE ps (c1 INT PRIMARY KEY) PARTITION BY RANGE(c1);
CREATE TABLE pr (c1 INT, c2 INT REFERENCES ps(c1)) PARTITION BY RANGE(c1);

-- Show memory usage of 'Cached%' 
SELECT name, sum(used_bytes) as bytes, pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts
WHERE name LIKE 'Cached%' GROUP BY name;

-- Procedure for creating partitioned table
CREATE OR REPLACE PROCEDURE part_make(tbl text, num int) AS $$
 DECLARE width int := 10; next int :=1;
 BEGIN
   FOR i in 1..num LOOP
       EXECUTE 'CREATE TABLE ' || tbl || '_' || i || ' partition of ' || tbl || ' FOR VALUES FROM (' || next || ') TO (' || i * width
	   || ');';
	       next := i * width;
   END LOOP;
 END;
$$ LANGUAGE plpgsql;

-- Create partitioned tables named ps and pr. The each table has 500 partitioning tables. 
CALL part_make('ps', 500);
CALL part_make('pr', 500);

-- Insert data
INSERT INTO ps SELECT generate_series(1,4999);
INSERT INTO pr SELECT i, i from generate_series(1,4999)i;

-- Show memory usages of 'Cached%' again
-- You can see 'CachedPlan 710MB'
SELECT name, sum(used_bytes) as bytes, pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts
WHERE name LIKE 'Cached%' GROUP BY name;