Can't make it tonight

<FONT face="Default Sans Serif, Verdana, Arial, Helvetica, sans-serif" size=2><DIV><DIV><FONT face=Arial size=2><DIV>Hey folks.&nbsp; I found out my wife had a meeting this evening and so I'll have to stay home with our daughter.&nbsp; Sorry about the late notice.</DIV><DIV>&nbsp;</DIV><DIV>But here is something to chew on:</DIV><DIV>&nbsp;</DIV><DIV>Just upgraded from 7.3.6 to 7.4.2 this past weekend.&nbsp; We had a query that used to take 20 mins to run......It now takes.....45 secs!&nbsp; Of course we re-wrote what we had originally done because of the way 7.3.6 handled indexes.&nbsp; We used the simple query below to spot the problem:</DIV><DIV>&nbsp;</DIV><DIV>7.3.6 queries:</DIV><DIV>this query worked great.&nbsp; It used the index on machine_id and date_est:</DIV><DIV>select *<BR>from cdm.cdm_partnerlog<BR>where <BR>machine_id=100838873<BR>and date_est='2004-04-05'</DIV><DIV>&nbsp;</DIV><DIV>--explain</DIV><DIV>&nbsp;Index Scan using cdm_partnerlog_machine_id on cdm_partnerlog&nbsp; (cost=0.00..1106.38 rows=3 width=76) (actual time=0.39..0.39 rows=0 loops=1)<BR>&nbsp;&nbsp; Index Cond: (machine_id = 100838873)<BR>&nbsp;&nbsp; Filter: (date_est = '2004-04-05'::date)<BR>&nbsp;Total runtime: 0.44 msec<BR></DIV><DIV>---</DIV><DIV>This&nbsp;query first did a scan of the entire table using the date_est field and then after it pull the result set then filtered on the machine_id&nbsp;field. This would take a stink load of time due to the number of records:</DIV><DIV>&nbsp;</DIV><DIV>select *<BR>from cdm.cdm_partnerlog<BR>where <BR>machine_id=100838873<BR>and date_est&gt;='2004-04-05'</DIV><DIV>&nbsp;</DIV><DIV>--explain</DIV><DIV><DIV>Index Scan using cdm_partnerlog_date_est on cdm_partnerlog&nbsp; (cost=0.00..955.45 rows=1 width=76) (actual time=3274.86..3274.86 rows=0 loops=1)<BR>&nbsp;&nbsp; Index Cond: (date_est &gt;= '2004-04-05'::date)<BR>&nbsp;&nbsp; Filter: (machine_id = 100838873)<BR>&nbsp;Total runtime: 3274.93 msec<BR>---<BR></DIV><DIV>Once we updated to 7.4.2, the query below:</DIV><DIV>&nbsp;</DIV><DIV>select *<BR>from cdm.cdm_partnerlog<BR>where <BR>machine_id=100838873<BR>and date_est&gt;='2004-04-05'</DIV><DIV>&nbsp;</DIV><DIV>&nbsp;Index Scan using cdm_partnerlog_machine_id on cdm_partnerlog&nbsp; (cost=0.00..315615.91 rows=62983 width=656) (actual time=0.149..0.149 rows=0 loops=1)<BR>&nbsp;&nbsp; Index Cond: (machine_id = 100838873)<BR>&nbsp;&nbsp; Filter: (date_est &gt;= '2004-04-05'::date)<BR>&nbsp;Total runtime: 0.243 ms<BR></DIV><DIV>&nbsp;</DIV><DIV>SWEEET!</DIV><DIV><BR>Patrick&nbsp;Hatcher<BR></DIV></DIV></FONT></DIV><BR><DIV><br><br>Patrick&nbsp;Hatcher<br>Macys.Com<br>Legacy&nbsp;Integration&nbsp;Developer<br>415-422-1610&nbsp;office<br>HatcherPT&nbsp;-&nbsp;AIM<BR></DIV></DIV></FONT>
>From sfpug-owner(at)postgresql(dot)org Thu Apr 15 18:49:22 2004
X-Original-To: sfpug-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (unknown [200.46.204.2])
by svr1.postgresql.org (Postfix) with ESMTP id 4A7FBD1C9DA
for <sfpug-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Thu, 15 Apr 2004 18:49:17 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
with ESMTP id 21306-08 for <sfpug-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Thu, 15 Apr 2004 18:49:18 -0300 (ADT)
Received: from redhotpenguin.com (unknown [64.127.99.50])
by svr1.postgresql.org (Postfix) with ESMTP id 3DE0BD1C9C8
for <sfpug(at)postgresql(dot)org>; Thu, 15 Apr 2004 18:49:11 -0300 (ADT)
Received: (qmail 3507 invoked by uid 509); 15 Apr 2004 21:49:18 -0000
Received: from fred(at)redhotpenguin(dot)com by www.redhotpenguin.com by uid 505 with qmail-scanner-1.16
(clamscan: 0.65. spamassassin: 2.44. Clear:SA:0(-2.3/5.0):.
Processed in 1.273768 secs); 15 Apr 2004 21:49:18 -0000
Received: from c-24-7-88-254.client.comcast.net (HELO ?192.168.0.8?) (fred(at)redhotpenguin(dot)com@24.7.88.254)
by 64.127.99.50 with RC4-SHA encrypted SMTP; 15 Apr 2004 21:49:16 -0000
Mime-Version: 1.0 (Apple Message framework v613)
In-Reply-To: <200404121716(dot)00967(dot)josh(at)agliodbs(dot)com>
References: <200404121655(dot)53811(dot)josh(at)agliodbs(dot)com> <20040413001306(dot)GC29071(at)fetter(dot)org> <200404121716(dot)00967(dot)josh(at)agliodbs(dot)com>
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <B92E816C-8F26-11D8-8A81-000A9598018A(at)redhotpenguin(dot)com>
Content-Transfer-Encoding: 7bit
From: Fred Moyer <fred(at)redhotpenguin(dot)com>
Subject: Elephant costume
Date: Thu, 15 Apr 2004 14:49:08 -0700
To: SF Postgres <sfpug(at)postgresql(dot)org>
X-Mailer: Apple Mail (2.613)
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200404/11
X-Sequence-Number: 808

So talk of the elephant costume at last night's meeting led me to this
page:

http://www.funfolly.com/h/mascots/c1744.htm

All it needs is a blue cape and PostgreSQL logo embroidered on the
front.