[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Query running a lot faster with enable_nestloop=false
From:       Robins Tharakan <robins.tharakan () comodo ! com>
Date:       2011-10-25 9:53:27
Message-ID: 4EA68447.7090606 () comodo ! com
[Download RAW message or body]


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.


["smime.p7s" (application/pkcs7-signature)]

[prev in list] [next in list] [prev in thread] [next in thread]