Received: from mercury.pollstar.com (dynamic19.pollstar.com [12.33.5.19])
	by postgresql.org (Postfix) with ESMTP id 25E1C47595D
	for <pgsql-sql@postgresql.org>; Thu, 25 Jul 2002 12:55:30 -0400 (EDT)
Received: from groupwise6.pollstar.com (dynamic2.pollstar.com [12.33.5.2])
	by mercury.pollstar.com (8.11.2/8.11.2) with ESMTP id g6PGtZc09421
	for <pgsql-sql@postgresql.org>; Thu, 25 Jul 2002 09:55:35 -0700
Received: from PS_GWMSG-MTA by groupwise6.pollstar.com
	with Novell_GroupWise; Thu, 25 Jul 2002 09:55:35 -0700
Message-Id: <sd3fcb17.081@groupwise6.pollstar.com>
X-Mailer: Novell GroupWise Internet Agent 6.0.1
Date: Thu, 25 Jul 2002 09:55:25 -0700
From: "Nathan Suderman" <Nathan@pollstar.com>
To: <pgsql-sql@postgresql.org>
Subject: queries got slower using 7.1.3
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=_AFF3DF97.C9A8C417"
X-Archive-Number: 200207/404
X-Sequence-Number: 8782

This is a MIME message. If you are reading this text, you may want to 
consider changing to a mail reader or gateway that understands how to 
properly handle MIME multipart messages.

--=_AFF3DF97.C9A8C417
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I have a weird problem and don't even know where to begin.  First let me
explain what is going on.
 
I am developing on a Postgres 7.1.3 box and found this unexplainable
delay on this query
 
select * from webfinal where w_artistkey = 'MICBUR' order by w_date
offset 2 limit 4;
 
I get at least a 8-9 second delay.  If I take either the 'order by' or
the 'limit' off the results are immediate.  I have several other
postgres boxes 7.1.1 and there is no delay using this query, here is
what I'm getting with explain, but I don't know why?
 
notifydev=# explain select * from webfinal where w_artistkey = 'MICBUR'
order by w_date offset 2 limit 4;
NOTICE:  QUERY PLAN:
 
Limit  (cost=39.97..119.91 rows=4 width=109)
  ->  Index Scan using webfinal_w_date on webfinal 
(cost=0.00..10563.49 rows=529 width=109)
 
EXPLAIN
notifydev=# explain select * from webfinal where w_artistkey = 'MICBUR'
order by w_date offset 2;        
NOTICE:  QUERY PLAN:
 
Limit  (cost=999.97..999.97 rows=527 width=109)
  ->  Sort  (cost=999.97..999.97 rows=529 width=109)
        ->  Index Scan using wf_ak1023165516 on webfinal 
(cost=0.00..976.06 rows=529 width=109)
 
EXPLAIN
 
Now I run explain on a 7.1.3 box
news=# explain select * from webfinal where w_artistkey = 'MICBUR'
order by w_date offset 2 limit 4;
NOTICE:  QUERY PLAN:
 
Limit  (cost=924.71..924.71 rows=4 width=109)
  ->  Sort  (cost=924.71..924.71 rows=498 width=109)
        ->  Index Scan using wf_ak1027580700 on webfinal 
(cost=0.00..902.38 rows=498 width=109)
 
EXPLAIN
 
can someone explain this to me? Please send response directly as I am
not on the list.

--=_AFF3DF97.C9A8C417
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Content-Description: HTML

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2716.2200" name=GENERATOR></HEAD>
<BODY style="MARGIN-TOP: 2px; FONT: 10pt MS Sans Serif; MARGIN-LEFT: 2px">
<DIV>I have a weird problem and don't even know where to begin.&nbsp; First let 
me explain what is going on.</DIV>
<DIV>&nbsp;</DIV>
<DIV>I am developing on a Postgres 7.1.3 box and found this unexplainable delay 
on this query</DIV>
<DIV>&nbsp;</DIV>
<DIV>select * from webfinal where w_artistkey = 'MICBUR' order by w_date offset 
2 limit 4;</DIV>
<DIV>&nbsp;</DIV>
<DIV>I get at least a 8-9 second delay.&nbsp; If I take either the 'order by' or 
the 'limit' off the results are immediate.&nbsp; I have several other postgres 
boxes 7.1.1 and there is no delay using this query, here is what I'm getting 
with explain, but I don't know why?</DIV>
<DIV>&nbsp;</DIV>
<DIV>notifydev=# explain select * from webfinal where w_artistkey = 'MICBUR' 
order by w_date offset 2 limit 4;<BR>NOTICE:&nbsp; QUERY PLAN:</DIV>
<DIV>&nbsp;</DIV>
<DIV>Limit&nbsp; (cost=39.97..119.91 rows=4 width=109)<BR>&nbsp; -&gt;&nbsp; 
Index Scan using webfinal_w_date on webfinal&nbsp; (cost=0.00..10563.49 rows=529 
width=109)</DIV>
<DIV>&nbsp;</DIV>
<DIV>EXPLAIN<BR>notifydev=# explain select * from webfinal where w_artistkey = 
'MICBUR' order by w_date offset 2;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
<BR>NOTICE:&nbsp; QUERY PLAN:</DIV>
<DIV>&nbsp;</DIV>
<DIV>Limit&nbsp; (cost=999.97..999.97 rows=527 width=109)<BR>&nbsp; -&gt;&nbsp; 
Sort&nbsp; (cost=999.97..999.97 rows=529 
width=109)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan 
using wf_ak1023165516 on webfinal&nbsp; (cost=0.00..976.06 rows=529 
width=109)</DIV>
<DIV>&nbsp;</DIV>
<DIV>EXPLAIN</DIV>
<DIV>&nbsp;</DIV>
<DIV>Now I run explain on a 7.1.3 box</DIV>
<DIV>news=# explain select * from webfinal where w_artistkey = 'MICBUR' order by 
w_date offset 2 limit 4;<BR>NOTICE:&nbsp; QUERY PLAN:</DIV>
<DIV>&nbsp;</DIV>
<DIV>Limit&nbsp; (cost=924.71..924.71 rows=4 width=109)<BR>&nbsp; -&gt;&nbsp; 
Sort&nbsp; (cost=924.71..924.71 rows=498 
width=109)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan 
using wf_ak1027580700 on webfinal&nbsp; (cost=0.00..902.38 rows=498 
width=109)</DIV>
<DIV>&nbsp;</DIV>
<DIV>EXPLAIN</DIV>
<DIV>&nbsp;</DIV>
<DIV>can someone explain this to me? Please send response directly as I am not 
on the list.</DIV></BODY></HTML>

--=_AFF3DF97.C9A8C417--