\set VERBOSITY terse CREATE SCHEMA pathman; CREATE EXTENSION pg_pathman SCHEMA pathman; CREATE SCHEMA test; /* * Test push down a join clause into child nodes of append */ /* create test tables */ CREATE TABLE test.fk ( id1 INT NOT NULL, id2 INT NOT NULL, start_key INT, end_key INT, PRIMARY KEY (id1, id2)); CREATE TABLE test.mytbl ( id1 INT NOT NULL, id2 INT NOT NULL, key INT NOT NULL, CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES test.fk(id1, id2), PRIMARY KEY (id1, key)); SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8); /* ...fill out with test data */ INSERT INTO test.fk VALUES (1, 1); INSERT INTO test.mytbl VALUES (1, 1, 5), (1,1,6); /* gather statistics on test tables to have deterministic plans */ ANALYZE test.fk; ANALYZE test.mytbl; /* run test queries */ EXPLAIN (COSTS OFF) /* test plan */ SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key FROM test.mytbl m JOIN test.fk USING(id1, id2) WHERE NOT key <@ int4range(6, end_key); /* test joint data */ SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key FROM test.mytbl m JOIN test.fk USING(id1, id2) WHERE NOT key <@ int4range(6, end_key); DROP SCHEMA test CASCADE; DROP EXTENSION pg_pathman CASCADE; DROP SCHEMA pathman CASCADE;