Re: index scan cost

Tom Lane wrote:
> Jeff Frost <jeff(at)frostconsultingllc(dot)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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032