Thread: disagreeing query planners

disagreeing query planners

From
"lcham02"
Date:
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