With three databases running the same query, I am receiving greatly
differing results from 2 of the query planners.
-db2 and db3 are slonied copies of db1. The servers have identical
postgresql.conf files but the server hardware differs.
-all appropriate columns are indexed
-vacuum analyze is run nightly on all dbs
Here is a simplified version of the query:
------------------------------------------------------------------------
EXPLAIN ANALYZE
SELECT COUNT(DISTINCT(m_object_paper.id))
FROM m_object_paper, m_assignment, m_class,
r_comment_rubric_user_object
WHERE m_object_paper.assignment=m_assignment.id
AND m_assignment.class=m_class.id
AND m_class.account = 36123
AND m_object_paper.id = r_comment_rubric_user_object.objectid;
------------------------------------------------------------------------
db1 displays a concise query plan of nested loops and index scans
executing in 85 ms.
However, db2's query plan consists of sequential scans and takes 3500
ms to complete.
The strange part is this. Last week, db1 and db3 were in agreement and
executing the more efficient plan. Now, db3 is in agreement with db2
with the less efficient, slower plan.
Are we missing something, what could cause this disagreement?
Thanks