Re: Constraint exclusion for partitioned tables

On Tue, Sep 12, 2017 at 8:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Sep 12, 2017 at 7:08 AM, Jeevan Chalke
> <jeevan(dot)chalke(at)enterprisedb(dot)com> wrote:
> > 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.
>
> What about the extra cost of checking the parent when it doesn't help?
> In that case we will have some loss.
>
> I'm inclined to think that's OK, but it's something to think about.
>

I have updated query like:
select * from foo where b > 100;
Which matches with the CHECK constraint, and here are the result on my
local setup:

Time in milliseconds
Partitions | without patch | with patch
-----------|---------------|------------
2 | 0.072551 | 0.074154
4 | 0.102537 | 0.108024
8 | 0.162703 | 0.175017
16 | 0.288589 | 0.305285
128 | 2.7119 | 2.636247
1024 | 29.101347 | 29.48275

So yes, as you said, it will have slight (may be negligible) overhead.

This observation are from local setup and I have also seen a large standard
deviation in the runs.

Thanks

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

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