\set VERBOSITY terse SET search_path = 'public'; 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); create_hash_partitions ------------------------ 8 (1 row) /* ...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; /* 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); QUERY PLAN ------------------------------------------------------------------------------------------------------- Nested Loop -> Seq Scan on fk -> Custom Scan (RuntimeAppend) Prune by: (fk.id1 = m.id1) -> Bitmap Heap Scan on mytbl_0 m Recheck Cond: (id1 = fk.id1) Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) -> Bitmap Index Scan on mytbl_0_pkey Index Cond: (id1 = fk.id1) -> Bitmap Heap Scan on mytbl_1 m Recheck Cond: (id1 = fk.id1) Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) -> Bitmap Index Scan on mytbl_1_pkey Index Cond: (id1 = fk.id1) -> Bitmap Heap Scan on mytbl_2 m Recheck Cond: (id1 = fk.id1) Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) -> Bitmap Index Scan on mytbl_2_pkey Index Cond: (id1 = fk.id1) -> Bitmap Heap Scan on mytbl_3 m Recheck Cond: (id1 = fk.id1) Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) -> Bitmap Index Scan on mytbl_3_pkey Index Cond: (id1 = fk.id1) -> Bitmap Heap Scan on mytbl_4 m Recheck Cond: (id1 = fk.id1) Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) -> Bitmap Index Scan on mytbl_4_pkey Index Cond: (id1 = fk.id1) -> Bitmap Heap Scan on mytbl_5 m Recheck Cond: (id1 = fk.id1) Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) -> Bitmap Index Scan on mytbl_5_pkey Index Cond: (id1 = fk.id1) -> Seq Scan on mytbl_6 m Filter: ((fk.id1 = id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) -> Bitmap Heap Scan on mytbl_7 m Recheck Cond: (id1 = fk.id1) Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) -> Bitmap Index Scan on mytbl_7_pkey Index Cond: (id1 = fk.id1) (41 rows) /* 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); tableoid | id1 | id2 | key | start_key | end_key --------------+-----+-----+-----+-----------+--------- test.mytbl_6 | 1 | 1 | 5 | | (1 row) /* * Test case by @dimarick */ CREATE TABLE test.parent ( id SERIAL NOT NULL, owner_id INTEGER NOT NULL ); CREATE TABLE test.child ( parent_id INTEGER NOT NULL, owner_id INTEGER NOT NULL ); CREATE TABLE test.child_nopart ( parent_id INTEGER NOT NULL, owner_id INTEGER NOT NULL ); INSERT INTO test.parent (owner_id) VALUES (1), (2), (3), (3); INSERT INTO test.child (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3); INSERT INTO test.child_nopart (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3); SELECT pathman.create_hash_partitions('test.child', 'owner_id', 2); create_hash_partitions ------------------------ 2 (1 row) /* gather statistics on test tables to have deterministic plans */ ANALYZE; /* Query #1 */ EXPLAIN (COSTS OFF) SELECT * FROM test.parent LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND test.child.owner_id = test.parent.owner_id WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4); QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop Left Join -> Seq Scan on parent Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3)) -> Custom Scan (RuntimeAppend) Prune by: ((child.owner_id = 3) AND (child.owner_id = parent.owner_id)) -> Seq Scan on child_1 child Filter: ((owner_id = 3) AND (owner_id = parent.owner_id) AND (parent_id = parent.id)) (7 rows) SELECT * FROM test.parent LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND test.child.owner_id = test.parent.owner_id WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4); id | owner_id | parent_id | owner_id ----+----------+-----------+---------- 3 | 3 | 3 | 3 4 | 3 | | (2 rows) /* Query #2 */ EXPLAIN (COSTS OFF) SELECT * FROM test.parent LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND test.child.owner_id = 3 WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4); QUERY PLAN ---------------------------------------------------------------------- Nested Loop Left Join Join Filter: (child_1.parent_id = parent.id) -> Seq Scan on parent Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3)) -> Append -> Seq Scan on child_1 Filter: (owner_id = 3) (7 rows) SELECT * FROM test.parent LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND test.child.owner_id = 3 WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4); id | owner_id | parent_id | owner_id ----+----------+-----------+---------- 3 | 3 | 3 | 3 4 | 3 | | (2 rows) DROP SCHEMA test CASCADE; NOTICE: drop cascades to 15 other objects DROP EXTENSION pg_pathman CASCADE; DROP SCHEMA pathman CASCADE;