Turning off enable_partition_pruning doesn't

Started by Tom Lanealmost 7 years ago3 messageshackers
Jump to latest
#1Tom Lane
tgl@sss.pgh.pa.us

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
/messages/by-id/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

#2Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Tom Lane (#1)
Re: Turning off enable_partition_pruning doesn't

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alvaro Herrera (#2)
Re: Turning off enable_partition_pruning doesn't

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