Re: [HACKERS] Constraint exclusion for partitioned tables - Mailing list pgsql-hackers

From Jeevan Chalke
Subject Re: [HACKERS] Constraint exclusion for partitioned tables
Date
Msg-id CAM2+6=XN+YvHGPd6c0nERJqu8EDjeb0EmcnDnbFSXw9gRS4AYg@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] Constraint exclusion for partitioned tables  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: [HACKERS] Constraint exclusion for partitioned tables
List pgsql-hackers
Hi,

I had a look at these changes and here are my observations:

1. Patch applies cleanly with "git apply'.
2. make / make install / make check-world all are good.

This patch clearly improves the planning time with given conditions.

To verify that, I have created a table like:
create table foo(a int, b int check (b > 100), c text) partition by range(a);
And then used following query to get planning time:
select * from foo where b < 100;

And on my local setup, I have observed that,
For 16 partitions, planning time was 0.234692 ms, which reduced to 0.112948 ms with this patch.
For 128 partitions, planning time was 1.62305 ms, which reduced to 0.654252 ms with this patch.
For 1024 partitions, planning time was 18.720993 ms, which reduced to 9.667395 ms with this patch.

This clearly shows an improvement in planning time.

Patch looks good to me. So passing that to the committer.

Thanks


On Tue, Aug 1, 2017 at 7:17 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Apr 6, 2017 at 6:47 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> 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).

CHECK constraints that apply to the parent would apply to all
children, unless they are NO INHERIT, so even for regular inheritance,
it might still be possible to prove something by ignoring things that
won't necessarily cascade.

For partitioning, it may be that we've got enough restrictions in
place on what can happen that we can assume everything can cascade.
Actually, I hope that's true, since the partitioned table has no
storage of its own.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

 
--
Jeevan Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: Re: [HACKERS] Automatic testing of patches in commit fest
Next
From: Ashutosh Bapat
Date:
Subject: Re: [HACKERS] no test coverage for ALTER FOREIGN DATA WRAPPER nameHANDLER ...