Thread: Turning off enable_partition_pruning doesn't

Turning off enable_partition_pruning doesn't

From
Tom Lane
Date:
I'd have thought that disabling enable_partition_pruning would,
um, disable partition pruning.  It does not:

regression=# create table p (a int) partition by list (a);
CREATE TABLE
regression=# create table p1 partition of p for values in (1);
CREATE TABLE
regression=# create table p2 partition of p for values in (2);
CREATE TABLE
regression=# explain select * from p1 where a = 3;
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on p1  (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 3)
(2 rows)

regression=# set enable_partition_pruning TO off;
SET
regression=# explain select * from p1 where a = 3;
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on p1  (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 3)
(2 rows)


The fact that we fail to prune the first child is a separate issue
driven by some ruleutils.c limitations, cf
https://www.postgresql.org/message-id/flat/001001d4f44b$2a2cca50$7e865ef0$@lab.ntt.co.jp

My point here is that the second EXPLAIN should have shown scanning
both partitions, shouldn't it?

(v11 behaves the same as HEAD here; didn't try v10.)

            regards, tom lane



Re: Turning off enable_partition_pruning doesn't

From
Alvaro Herrera
Date:
On 2019-Apr-30, Tom Lane wrote:

> regression=# explain select * from p1 where a = 3; 

But you're reading from the partition, not from the partitioned table ...

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: Turning off enable_partition_pruning doesn't

From
Tom Lane
Date:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2019-Apr-30, Tom Lane wrote:
>> regression=# explain select * from p1 where a = 3; 

> But you're reading from the partition, not from the partitioned table ...

Argh!  Where's my brown paper bag?

            regards, tom lane