Message-ID: <489D3673.5080100@frostconsultingllc.com>
Date: Fri, 08 Aug 2008 23:17:23 -0700
From: Jeff Frost <jeff@frostconsultingllc.com>
Organization: Frost Consulting, LLC
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: index scan cost
References: 
 <Pine.LNX.4.64.0807171358260.4570@discord.home.frostconsultingllc.com>
	<9919.1216355851@sss.pgh.pa.us>
In-Reply-To: <9919.1216355851@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------030706050202020606070502"

This is a multi-part message in MIME format.
--------------030706050202020606070502
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------030706050202020606070502
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<blockquote cite="mid:9919.1216355851@sss.pgh.pa.us" type="cite">
  <pre wrap="">Jeff Frost <a class="moz-txt-link-rfc2396E" href="mailto:jeff@frostconsultingllc.com">&lt;jeff@frostconsultingllc.com&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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:
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
  <blockquote type="cite">
    <pre wrap="">  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)
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
  <blockquote type="cite">
    <pre wrap="">  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)
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.
  </pre>
</blockquote>
Sorry it took me a while to close the loop on this.&nbsp; So, the server
that had the less desirable plan had actually been analyzed more
recently by autovacuum.&nbsp; When I went back to compare the stats on the
faster server, autovacuum had analyzed it and the plan was now more
similar.&nbsp; Adjusting the stats target up for that column helped on both
servers though it never did get back as close as before.<br>
<br>
<pre class="moz-signature" cols="72">-- 
Jeff Frost, Owner 	<a class="moz-txt-link-rfc2396E" href="mailto:jeff@frostconsultingllc.com">&lt;jeff@frostconsultingllc.com&gt;</a>
Frost Consulting, LLC 	<a class="moz-txt-link-freetext" href="http://www.frostconsultingllc.com/">http://www.frostconsultingllc.com/</a>
Phone: 916-647-6411	FAX: 916-405-4032
</pre>
</body>
</html>

--------------030706050202020606070502--