From: Robins Tharakan <robins.tharakan@comodo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query running a lot faster with enable_nestloop=false
Date: Tue, 25 Oct 2011 15:11:27 +0530
Message-ID: <4EA68447.7090606@comodo.com> (raw)
In-Reply-To: <CAK6uQa9wNKmsukj6-StadxJ1HN8sFV6OOZ-q8R5fnmtBDjVhPA@mail.gmail.com>
References: <CAK6uQa9wNKmsukj6-StadxJ1HN8sFV6OOZ-q8R5fnmtBDjVhPA@mail.gmail.com>
Hi Mohanaraj,
One thing you should certainly try is to increase the
default_statistics_target value from 50 up to say about 1000 for the
larger tables. Large tables tend to go off on estimates with smaller
values here.
I guess I am not helping here, but apart from your query, those
estimates on Machine B seem odd, coz they shoot up from 10k to the order
of billions without any big change in row-count. Beats me.
--
Robins Tharakan
> 1. For Machine A, what can I do to make the planner choose the faster
> plan without setting enable_nestloop=false ?
>
> 2. From the research I have done it seems to be that the reason the
> planner is choosing the unoptimal query is because of the huge
> difference between the estimated and actual rows. How can I get this
> figure closer ?
>
> 3. If I should rewrite the query, what should I change ?
>
> 4. Why is it that the planner seems to be doing the right thing for
> Machine B without setting enable_nestloop=false. What should I be
> comparing in both the machines to understand the difference in choice
> that the planner made ?
>
> I have tried reading through the manual section "55.1. Row Estimation
> Examples", "14.2. Statistics Used by the Planner". I am still trying
> to fully apply the information to my specific case above and hence any
> help or pointers would be greatly appreciated.
>
> In a last ditch effort we also tried upgrading Machine A to
> PostgresSQL 9.1 and that did not rectify the issue. We have reverted
> the upgrade for now.
>
> Thank you for your time.
Attachments:
smime.p7s (application/pkcs7-signature, 6.4K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robins.tharakan@comodo.com
Subject: Re: Query running a lot faster with enable_nestloop=false
In-Reply-To: <4EA68447.7090606@comodo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox