Skip site navigation (1) Skip section navigation (2)

Can't make it tonight

From: "Patrick Hatcher" <PHatcher(at)macys(dot)com>
To: sfpug(at)postgresql(dot)org <sfpug(at)postgresql(dot)org>
Subject: Can't make it tonight
Date: 2004-04-14 20:05:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: sfpug
<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 [])
	by (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 ([])
 by localhost ( []) (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 (unknown [])
	by (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 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 (HELO ? (fred(at)redhotpenguin(dot)com@
  by 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
X-Archive-Number: 200404/11
X-Sequence-Number: 808

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

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

Attachment: unknown_filename
Description: text/html (4.7 KB)

sfpug by date

Next:From: David FetterDate: 2004-04-15 23:41:57
Subject: Re: Elephant costume
Previous:From: David AlbanDate: 2004-04-14 14:30:10
Subject: SIG-BEER-WEST this Saturday 4/17 in San Francisco

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group