DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:Cc:MIME-Version:Content-Type:Message-ID;
	b=YFpIwITXW6JSaFC9bEPfGQ4PO10TxDYOotTgZZN7HEe3xY/dOA57eGMit5NbEtryPLPcGlj+hKD4M+Y0nhkAg21SBqebdKoibMGhTeyQonXQpdlqwGr6k/KPcw4szPkO9wqZ0TwAlk3j/82wonMfMoPiOD62OU1fYlzwFlgoiBg=;
Date: Mon, 29 Sep 2008 17:17:44 -0700 (PDT)
From: Doug Eck <deck1@yahoo.com>
Reply-To: Doug Eck <deck1@yahoo.com>
Subject: Re: Identical DB's, different execution plans
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1641135722-1222733864=:58055"
Message-ID: <650568.58055.qm@web45201.mail.sp1.yahoo.com>

--0-1641135722-1222733864=:58055
Content-Type: text/plain; charset=us-ascii

Tom,

You nailed it.  The t1 table was using 9600 relpages versus 410 after the vacuum full.  The two databases are now showing similar execution plans and times.

Thanks for your help.  It is greatly appreciated.

Doug Eck


----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Doug Eck <deck1@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Monday, September 29, 2008 6:20:20 PM
Subject: Re: [PERFORM] Identical DB's, different execution plans 

Doug Eck <deck1@yahoo.com> writes:
> The new plan from the slower db:

>    ->  Index Scan using t1_uc2 on t1  (cost=0.00..25604.74 rows=204906 width=32) (actual time=0.061..327.285 rows=8438 loops=1)
>          Filter: active

This seems a bit fishy.  In the first place, with such a simple filter
condition it shouldn't be that far off on the rowcount estimate.  In
the second place, the cost estimate is more than twice what the other
server estimates to do a seqscan and sort of the same data, and the
rowcount estimate is five times as much.  So there's something really
significantly different about the t1 tables in the two cases.

The first thing you ought to do is to look at the pg_class.relpages
and reltuples entries for t1 in both databases.  What I am suspecting is
that for some reason the "slow" db has suffered a lot of bloat in that
table, leading to a corresponding increase in the cost of a seqscan.
If so, a VACUUM FULL or CLUSTER should fix it, though you'll next need
to look into why routine vacuumings weren't happening.  (It looks like
t2 may be a bit bloated as well.)

If that's not it, we'll need to probe deeper ...

            regards, tom lane


--0-1641135722-1222733864=:58055
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div>Tom,<br><br>You nailed it.&nbsp; The t1 table was using 9600 relpages versus 410 after the vacuum full.&nbsp; The two databases are now showing similar execution plans and times.<br><br>Thanks for your help.&nbsp; It is greatly appreciated.<br><br>Doug Eck<br></div><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;"><br><div style="font-family: arial,helvetica,sans-serif; font-size: 10pt;">----- Original Message ----<br>From: Tom Lane &lt;tgl@sss.pgh.pa.us&gt;<br>To: Doug Eck &lt;deck1@yahoo.com&gt;<br>Cc: pgsql-performance@postgresql.org<br>Sent: Monday, September 29, 2008 6:20:20 PM<br>Subject: Re: [PERFORM] Identical DB's, different execution plans <br><br>
Doug Eck &lt;<a ymailto="mailto:deck1@yahoo.com" href="mailto:deck1@yahoo.com">deck1@yahoo.com</a>&gt; writes:<br>&gt; The new plan from the slower db:<br><br>&gt;&nbsp; &nbsp; -&gt;&nbsp; Index Scan using t1_uc2 on t1&nbsp; (cost=0.00..25604.74 rows=204906 width=32) (actual time=0.061..327.285 rows=8438 loops=1)<br>&gt;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Filter: active<br><br>This seems a bit fishy.&nbsp; In the first place, with such a simple filter<br>condition it shouldn't be that far off on the rowcount estimate.&nbsp; In<br>the second place, the cost estimate is more than twice what the other<br>server estimates to do a seqscan and sort of the same data, and the<br>rowcount estimate is five times as much.&nbsp; So there's something really<br>significantly different about the t1 tables in the two cases.<br><br>The first thing you ought to do is to look at the pg_class.relpages<br>and reltuples entries for t1 in both databases.&nbsp; What I am
 suspecting is<br>that for some reason the "slow" db has suffered a lot of bloat in that<br>table, leading to a corresponding increase in the cost of a seqscan.<br>If so, a VACUUM FULL or CLUSTER should fix it, though you'll next need<br>to look into why routine vacuumings weren't happening.&nbsp; (It looks like<br>t2 may be a bit bloated as well.)<br><br>If that's not it, we'll need to probe deeper ...<br><br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; regards, tom lane<br></div></div></div><br>

      </body></html>
--0-1641135722-1222733864=:58055--