Re: [HACKERS] Constraint exclusion for partitioned tables

From: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Constraint exclusion for partitioned tables
Date: 2017-12-03 23:15:15
Message-ID: CAFjFpRffbPMRAVi96nMjVRvwabmvrC1_XFrhT-y9nMWErMGdoQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Sat, Dec 2, 2017 at 1:11 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Dec 1, 2017 at 12:21 AM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
>> On Wed, Sep 13, 2017 at 4:07 PM, Ashutosh Bapat
>> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>>> For a partitioned table, this patch saves the time to run constraint
>>> exclusion on all the partitions if constraint exclusion succeeds on
>>> the partitioned table. If constraint exclusion fails, we have wasted
>>> CPU cycles on one run of constraint exclusion. The difference between
>>> the time spent in the two scenarios increases with the number of
>>> partitions. Practically, users will have a handful partitions rather
>>> than a couple and thus running overhead of running constraint
>>> exclusion on partitioned table would be justified given the time it
>>> will save when CE succeeds.
>>
>> Moved patch to next CF.
>
> Committed after adding a comment. Generally, code changes should be
> accompanied by comment updates.

Thanks for committing the patch. Sorry for not including the comments.
Your comment looks good.

>
> I tested this and found out that this is quite useful for cases where
> multiple levels of partitioning are in use. Consider creating 100
> partitions like this:
>
> #!/usr/bin/perl
>
> use strict;
> use warnings;
>
> print "create table foo (a int, b int, c text) partition by list (a);\n";
> for $a (1..10)
> {
> print "create table foo$a partition of foo for values in ($a)
> partition by list (b);\n";
> for $b (1..10)
> {
> print "create table foo${a}_$b partition of foo$a for values
> in ($b);\n";
> }
> }
>
> Then consider this query: select * from foo where a = 5;
>
> Without this patch, we have to reject 90 leaf partitions individually,
> but with the patch, we can reject the intermediate partitioned tables;
> each time we do, it substitutes for rejecting 10 children
> individually. This seems to me to be a case that is quite likely to
> come up in the real world.
>

Right. Thanks for the testing.

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Janes 2017-12-03 23:21:56 Re: Bitmap scan is undercosted? - boolean correlation
Previous Message Tom Lane 2017-12-03 23:08:46 Re: Bitmap scan is undercosted?