Re: path toward faster partition pruning

On Mon, Nov 6, 2017 at 3:31 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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