On Wed, Sep 11, 2013 at 4:16 AM, Mikkel Lauritsen <renard@tala.dk> wrote:
Hi all,
I have a number of Postgres 9.2.4 databases with the same schema but with slightly different contents, running on small servers that are basically alike (8-16 GB ram).
When I run the same query on these databases it results in one of two different execution plans where one is much faster (appx. 50 times) than the other. Each database always gives the same plan, and vacuuming, updating statistics and reindexing doesn't seem to make any difference.
Clearly the fast plan is preferred, but I haven't been able to identify any pattern (table sizes, tuning etc.) in why one plan is chosen over the other, so is there any way I can make Postgres tell me why it chooses to plan the way it does?
Are you sure the schemas are identical, including the existence of identical indexes?
Also, using "explain (analyze, buffers)" gives more info than just "explain analyze"
If you can get both systems to use the same plan, then you can compare the cost estimates of each directly. But that is easier said than done.
You can temporarily drop an index used in the slow query but not the fast one, to see what plan that comes up with:
begin; drop index x_a_id_idx; <run query>; rollback;
