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)
