I've attached an updated patch, now at v10. v9 was short lived due to the evolution of Amit's which which this based on.
This version is based on Amit's v27 of faster partition pruning [1] which can be applied atop of ad7dbee36.
Hi,
I have applied v10 patch on Amit's v27 over head ad7dbee36. I got "ERROR: partition missing from Append subplans" with the patch. on head and only with Amit's patches query is working fine, Please find test case below.
CREATE TABLE part ( c1 INT2, c2 DATE) PARTITION BY RANGE (c1); CREATE TABLE part_p1 PARTITION OF part FOR VALUES FROM (0) TO (141) PARTITION BY RANGE(c2); CREATE TABLE part_p11 PARTITION OF part_p1 FOR VALUES FROM ('1/1/1997') TO ('2/1/1999'); CREATE TABLE part_p12 PARTITION OF part_p1 FOR VALUES FROM ('2/1/1999') TO ('2/1/2000'); CREATE TABLE part_p2 PARTITION OF part FOR VALUES FROM (141) TO (211) PARTITION BY RANGE(c2); CREATE TABLE part_p21 PARTITION OF part_p2 FOR VALUES FROM ('1/1/2000') TO ('2/1/2001'); CREATE TABLE part_p22 PARTITION OF part_p2 FOR VALUES FROM ('2/1/2001') TO ('2/1/2006');
INSERT INTO part VALUES (100,'1/1/1999'); INSERT INTO part VALUES (110,'1/1/1998'); INSERT INTO part VALUES (130,'1/1/2000'); INSERT INTO part VALUES (170,'1/1/2000'); INSERT INTO part VALUES (180,'1/1/2001'); INSERT INTO part VALUES (190,'1/1/2006'); INSERT INTO part VALUES (200,'1/1/2000'); INSERT INTO part VALUES (210,'1/1/2002');
postgres=# PREPARE RTP AS SELECT * FROM PART WHERE c2 BETWEEN '1/1/1998' AND '1/1/1999'; PREPARE postgres=# EXPLAIN execute RTP; QUERY PLAN ----------------------------------------------------------------------------- Append (cost=0.00..46.00 rows=12 width=6) -> Seq Scan on part_p11 (cost=0.00..46.00 rows=12 width=6) Filter: ((c2 >= '1998-01-01'::date) AND (c2 <= '1999-01-01'::date)) (3 rows)
postgres=# execute RTP; ERROR: partition missing from Append subplans