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.