Tom Lane wrote:
Jeff Frost <jeff@frostconsultingllc.com> writes:
I have two postgresql servers. One runs 8.3.1, the other 8.3.3. On the 8.3.1
machine, the index scans are being planned extremely low cost:
Index Scan using ix_email_entity_thread on email_entity (cost=0.00..4.59
rows=1 width=1031) (actual time=0.095..0.120 rows=4 loops=1) Index Cond: (email_thread = 375629157)
Index Scan using ix_email_entity_thread on email_entity (cost=0.00..2218.61
rows=1151 width=931) (actual time=0.094..0.111 rows=4 loops=1) Index Cond: (email_thread = 375629157)
This isn't a "cost" problem, this is a "stats" problem. Why does the
second server think 1151 rows will be returned? Try comparing the
pg_stats entries for the email_thread column on both servers ... seems
like they must be significantly different.
Sorry it took me a while to close the loop on this. So, the server that had the less desirable plan had actually been analyzed more recently by autovacuum. When I went back to compare the stats on the faster server, autovacuum had analyzed it and the plan was now more similar. Adjusting the stats target up for that column helped on both servers though it never did get back as close as before.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032