From: Jeff Frost <jeff@frostconsultingllc.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: index scan cost
Date: Fri, 08 Aug 2008 23:17:23 -0700
Message-ID: <489D3673.5080100@frostconsultingllc.com> (raw)
In-Reply-To: <9919.1216355851@sss.pgh.pa.us>
References: <Pine.LNX.4.64.0807171358260.4570@discord.home.frostconsultingllc.com>
<9919.1216355851@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jeff@frostconsultingllc.com, tgl@sss.pgh.pa.us
Subject: Re: index scan cost
In-Reply-To: <489D3673.5080100@frostconsultingllc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox