On Wed, Sep 11, 2013 at 4:16 AM, Mikkel Lauritsen <renard@xxxxxxx> 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;
Cheers,
Jeff
