selecting from partitions and constraint exclusion

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: selecting from partitions and constraint exclusion
Date: 2019-03-20 04:37:13
Message-ID: 9813f079-f16b-61c8-9ab7-4363cab28d80@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi,

While looking at a partition pruning bug [1], I noticed something that
started to feel like a regression:

Setup:

create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);

In PG 10:

set constraint_exclusion to on;
explain select * from p1 where a = 2;
QUERY PLAN
──────────────────────────────────────────
Result (cost=0.00..0.00 rows=0 width=4)
One-Time Filter: false
(2 rows)

In PG 11 (and HEAD):

set constraint_exclusion to on;
explain select * from p1 where a = 2;
QUERY PLAN
────────────────────────────────────────────────────
Seq Scan on p1 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 2)
(2 rows)

That's because get_relation_constraints() no longer (as of PG 11) includes
the partition constraint for SELECT queries. But that's based on an
assumption that partitions are always accessed via parent, so partition
pruning would make loading the partition constraint unnecessary. That's
not always true, as shown in the above example.

Should we fix that? I'm attaching a patch here.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/00e601d4ca86$932b8bc0$b982a340$@lab.ntt.co.jp

Attachment Content-Type Size
v1-0001-Fix-planner-to-load-partition-constraint-in-some-.patch text/plain 5.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Paul Guo 2019-03-20 04:48:52 Re: Two pg_rewind patches (auto generate recovery conf and ensure clean shutdown)
Previous Message Masahiko Sawada 2019-03-20 04:11:12 Re: [HACKERS] Block level parallel vacuum