\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);
 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 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);
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Nested Loop
   ->  Seq Scan on fk
   ->  Custom Scan (RuntimeAppend)
         ->  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)
         ->  Bitmap Heap Scan on mytbl_6 m
               Recheck Cond: (id1 = fk.id1)
               Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
               ->  Bitmap Index Scan on mytbl_6_pkey
                     Index Cond: (id1 = fk.id1)
         ->  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)
(43 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)

DROP SCHEMA test CASCADE;
NOTICE:  drop cascades to 10 other objects
DROP EXTENSION pg_pathman CASCADE;
DROP SCHEMA pathman CASCADE;