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.
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
