On Mon, Nov 6, 2017 at 3:31 PM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote:
> Attached updated set of patches, including the fix to make the new pruning > code handle Boolean partitioning. > Hi Amit, I have tried pruning for different values of constraint exclusion GUC change, not sure exactly how it should behave, but I can see with the delete statement pruning is not happening when constraint_exclusion is off, but select is working as expected. Is this expected behaviour? create table lp (c1 int, c2 text) partition by list(c1); create table lp1 partition of lp for values in (1,2); create table lp2 partition of lp for values in (3,4); create table lp3 partition of lp for values in (5,6); insert into lp values (1,'p1'),(2,'p1'),(3,'p2'),(4,'p2'),(5,'p3'); show constraint_exclusion ; constraint_exclusion ---------------------- partition (1 row) explain select c1 from lp where c1 >= 1 and c1 < 2; QUERY PLAN ---------------------------------------------------------- Append (cost=0.00..29.05 rows=6 width=4) -> Seq Scan on lp1 (cost=0.00..29.05 rows=6 width=4) Filter: ((c1 >= 1) AND (c1 < 2)) (3 rows) explain delete from lp where c1 >= 1 and c1 < 2; QUERY PLAN ---------------------------------------------------------- Delete on lp (cost=0.00..29.05 rows=6 width=6) Delete on lp1 -> Seq Scan on lp1 (cost=0.00..29.05 rows=6 width=6) Filter: ((c1 >= 1) AND (c1 < 2)) (4 rows) set constraint_exclusion = off; explain select c1 from lp where c1 >= 1 and c1 < 2; QUERY PLAN ---------------------------------------------------------- Append (cost=0.00..29.05 rows=6 width=4) -> Seq Scan on lp1 (cost=0.00..29.05 rows=6 width=4) Filter: ((c1 >= 1) AND (c1 < 2)) (3 rows) *explain delete from lp where c1 >= 1 and c1 < 2;* QUERY PLAN ---------------------------------------------------------- Delete on lp (cost=0.00..87.15 rows=18 width=6) Delete on lp1 Delete on lp2 Delete on lp3 -> Seq Scan on lp1 (cost=0.00..29.05 rows=6 width=6) Filter: ((c1 >= 1) AND (c1 < 2)) -> Seq Scan on lp2 (cost=0.00..29.05 rows=6 width=6) Filter: ((c1 >= 1) AND (c1 < 2)) -> Seq Scan on lp3 (cost=0.00..29.05 rows=6 width=6) Filter: ((c1 >= 1) AND (c1 < 2)) (10 rows) Thanks & Regards, Rajkumar Raghuwanshi QMG, EnterpriseDB Corporation