Re: Constraint exclusion for partitioned tables

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.

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