Just as an example, here's the query plan of the *SAME* query before and after a
VACUUM ANALYZE
Notice the way the two plans are *COMPLETELY* different. l_portal_statuses and
b_portal_statuses only have *3 rows* right now, but there was no way for the
planner to know that. Anyway, this should be evidence that a good VACUUM ANALYZE
periodically is a Good Thing(tm).
(BTW, the site that this database drives is now significantly more responsive)
Before:
----------------------------------------
Merge Join (cost=97.62..170.37 rows=1000 width=110)
-> Index Scan using l_portal_statuses_pkey on l_portal_statuses lps (cost=0.00..59.00 rows=1000 width=16)
-> Sort (cost=97.62..97.62 rows=100 width=94)
-> Merge Join (cost=22.67..94.30 rows=100 width=94)
-> Index Scan using b_portal_statuses_pkey on b_portal_statuses bps (cost=0.00..59.00 rows=1000 width=16)
-> Sort (cost=22.67..22.67 rows=10 width=78)
-> Seq Scan on contracts c (cost=0.00..22.50 rows=10 width=78)
After:
----------------------------------------
Nested Loop (cost=0.00..3.47 rows=1 width=110)
-> Nested Loop (cost=0.00..2.40 rows=1 width=94)
-> Seq Scan on contracts c (cost=0.00..1.34 rows=1 width=78)
-> Seq Scan on b_portal_statuses bps (cost=0.00..1.03 rows=3 width=16)
-> Seq Scan on l_portal_statuses lps (cost=0.00..1.03 rows=3 width=16)
-- Dave