Constraint exclusion for partitioned tables

From: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Constraint exclusion for partitioned tables
Date: 2017-04-06 10:47:24
Message-ID: CAFjFpRcuRaydz88CY_aQekmuvmN2A9ax5z0k=ppT+s8KS8xMRA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi,
In relation_excluded_by_constraints(), we do not apply constraint
exclusion if rte->inh is true.

/* Only plain relations have constraints */
if (rte->rtekind != RTE_RELATION || rte->inh)
return false;

Thus every partitioned table will not benefit from the constraint
exclusion, even when constraint_exclusion = on. Hence for a
partitioned table
\d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | not null | | plain | |
b | integer | | | | plain | |
Partition key: RANGE (a)
Check constraints:
"t1_b_check" CHECK (b > 100)
Partitions: t1p1 FOR VALUES FROM (0) TO (100),
t1p2 FOR VALUES FROM (100) TO (200)

while executing a query "select * from t1 where b < 100"
set_rel_size() doesn't mark t1 as dummy. It gets marked dummy only
after all the children have been deemed dummy by constraint exclusion.
This means that we will unnecessarily examine children when the parent
itself is known dummy.

I am guessing that for normal inheritance, a constraint on parent
doesn't necessarily imply the same constraint on the child (Amit
Langote gives me an example of NOT NULL constraint). But in case of
partitioned table, every constraint on the parent is applicable to the
child as well. So, we can apply constraint exclusion on partitioned
relation. Here's patch to do that.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Attachment Content-Type Size
pg_part_ce.patch application/octet-stream 513 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Beena Emerson 2017-04-06 11:13:51 Re: increasing the default WAL segment size
Previous Message Kyotaro HORIGUCHI 2017-04-06 10:42:24 Re: [COMMITTERS] pgsql: Collect and use multi-column dependency stats