* Test push down a join clause into child nodes of append

*/

/* create test tables */

CREATE TABLE fk (

CREATE TABLEtest.fk (

id1 INT NOT NULL,

id2 INT NOT NULL,

start_key INT,

end_key INT,

PRIMARY KEY (id1, id2));

CREATE TABLE mytbl (

CREATE TABLEtest.mytbl (

id1 INT NOT NULL,

id2 INT NOT NULL,

key INT NOT NULL,

CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES fk(id1, id2),

CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCEStest.fk(id1, id2),

PRIMARY KEY (id1, key));

SELECT pathman.create_hash_partitions('mytbl', 'id1', 8);

SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8);

create_hash_partitions

------------------------

8

(1 row)

/* ...fill out with test data */

INSERT INTO fk VALUES (1, 1);

INSERT INTO mytbl VALUES (1, 1, 5), (1,1,6);

INSERT INTOtest.fk VALUES (1, 1);

INSERT INTOtest.mytbl VALUES (1, 1, 5), (1,1,6);

/* gather statistics on test tables to have deterministic plans */

ANALYZE fk;

ANALYZE mytbl;

ANALYZEtest.fk;

ANALYZEtest.mytbl;

/* run test queries */

EXPLAIN (COSTS OFF) /* test plan */

SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key

FROM mytbl m JOIN fk USING(id1, id2)

FROMtest.mytbl m JOINtest.fk USING(id1, id2)

WHERE NOT key <@ int4range(6, end_key);

QUERY PLAN

------------------------------------------------------------------------------------

/* test joint data */

SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key

FROM mytbl m JOIN fk USING(id1, id2)

FROMtest.mytbl m JOINtest.fk USING(id1, id2)

WHERE NOT key <@ int4range(6, end_key);

tableoid | id1 | id2 | key | start_key | end_key

----------+-----+-----+-----+-----------+---------

mytbl_6 | 1 | 1 | 5 | |

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;

NOTICE: drop cascades to 8 other objects

DROP SCHEMA pathman CASCADE;

CREATETABLEfk (

CREATETABLEtest.fk (

id1INTNOT NULL,

id2INTNOT NULL,

start_keyINT,

end_keyINT,

PRIMARY KEY (id1, id2));

CREATETABLEmytbl (

CREATETABLEtest.mytbl (

id1INTNOT NULL,

id2INTNOT NULL,

keyINTNOT NULL,

CONSTRAINT fk_fkFOREIGN KEY (id1, id2)REFERENCES fk(id1, id2),

CONSTRAINT fk_fkFOREIGN KEY (id1, id2)REFERENCEStest.fk(id1, id2),

PRIMARY KEY (id1, key));

SELECTpathman.create_hash_partitions('mytbl','id1',8);

SELECTpathman.create_hash_partitions('test.mytbl','id1',8);

INSERT INTO fkVALUES (1,1);

INSERT INTO mytblVALUES (1,1,5), (1,1,6);

INSERT INTOtest.fkVALUES (1,1);

INSERT INTOtest.mytblVALUES (1,1,5), (1,1,6);

ANALYZE fk;

ANALYZE mytbl;

ANALYZEtest.fk;

ANALYZEtest.mytbl;

EXPLAIN (COSTS OFF)/* test plan*/

SELECTm.tableoid::regclass, id1, id2, key, start_key, end_key

FROM mytbl mJOIN fk USING(id1, id2)

FROMtest.mytbl mJOINtest.fk USING(id1, id2)

WHERE NOT key<@ int4range(6, end_key);

SELECTm.tableoid::regclass, id1, id2, key, start_key, end_key

FROM mytbl mJOIN fk USING(id1, id2)

FROMtest.mytbl mJOINtest.fk USING(id1, id2)

WHERE NOT key<@ int4range(6, end_key);