On Tue, Sep 12, 2017 at 8:12 PM, Robert Haas <robertmh...@gmail.com> wrote:
> On Tue, Sep 12, 2017 at 7:08 AM, Jeevan Chalke > <jeevan.cha...@enterprisedb.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
