X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id CD2DB9DC805
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 23 Dec 2005 10:35:16 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 79858-04
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 23 Dec 2005 10:35:19 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from web35514.mail.mud.yahoo.com (web35514.mail.mud.yahoo.com
	[66.163.179.138])
	by postgresql.org (Postfix) with SMTP id C78A59DC827
	for <pgsql-performance@postgresql.org>;
	Fri, 23 Dec 2005 10:35:13 -0400 (AST)
Received: (qmail 85772 invoked by uid 60001); 23 Dec 2005 14:35:18 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=sQ+mR6FuAdBdKVsraBjHlKkuQEdGjSHOxjLzgAdJPQziLeDe6/SEC3qdbLRGKZWWq/xiQcddEk0GhqT6T3MdS8us08u1D7eU84+9AzMjmmCcx4wDYT/Snn1DtJBxEd4pR4GXPqEhKVvH8gjrppSAT/Gyv9cm3ykN0k/uFrRYdC0=
	;
Message-ID: <20051223143518.85770.qmail@web35514.mail.mud.yahoo.com>
Received: from [201.2.222.140] by web35514.mail.mud.yahoo.com via HTTP;
	Fri, 23 Dec 2005 14:35:18 GMT
Date: Fri, 23 Dec 2005 14:35:18 +0000 (GMT)
From: Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>
Subject: Re: Order by behaviour
To: pgsql-performance@postgresql.org
In-Reply-To: <55DD6E49-BC80-443D-9F6E-573B6CEE961D@pharmaline.de>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1919189698-1135348518=:85449"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.558 required=5 tests=[AWL=0.078,
	DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001]
X-Spam-Score: 0.558
X-Spam-Level: 
X-Archive-Number: 200512/585
X-Sequence-Number: 16406

--0-1919189698-1135348518=:85449
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

>If you can't force PostgreSQL to perform better on the ordered query, 
>what about retrieving only the primary keys for the rows you want 
>unordered in a subquery and using an "where primaryKey in (...) order 
>by ..." statement with ordering the five rows?
   
  I appreciate your suggestion but I think I´m misunderstanding something, the select statement should return at about 150.000 rows, why 5 rows?
  
Guido Neitzer <guido.neitzer@pharmaline.de> escreveu:
      On 23.12.2005, at 13:34 Uhr, Carlos Benkendorf wrote:

> For some implementation reason in 8.0.3 the query is returning the 
> rows in the correct order even without the order by but in 8.1.1 
> probably the implementation changed and the rows are not returning 
> in the correct order.

You will never be sure to get rows in a specific order without an 
"order by".

I don't know why PG is faster without ordering, perhaps others can 
help with that so you don't need a workaround like this:

If you can't force PostgreSQL to perform better on the ordered query, 
what about retrieving only the primary keys for the rows you want 
unordered in a subquery and using an "where primaryKey in (...) order 
by ..." statement with ordering the five rows?
   
  

Like this:

select * from mytable where pk in (select pk from mytable where ...) 
order by ...;

I don't know whether the query optimizer will flatten this query, but 
you can try it.

cug


-- 
PharmaLine Essen, GERMANY and
Big Nerd Ranch Europe - PostgreSQL Training, Feb. 2006, Rome, Italy
http://www.bignerdranch.com/classes/postgresql.shtml






		
---------------------------------
 Yahoo! doce lar. Faça do Yahoo! sua homepage.
--0-1919189698-1135348518=:85449
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<DIV id=RTEContent>&gt;If you can't force PostgreSQL to perform better on the ordered query, <BR>&gt;what about retrieving only the primary keys for the rows you want <BR>&gt;unordered in a subquery and using an "where primaryKey in (...) order <BR>&gt;by ..." statement with ordering the five rows?</DIV>  <div>&nbsp;</div>  <DIV>I appreciate your suggestion but I think I´m misunderstanding something, the select statement should return at&nbsp;about 150.000 rows, why 5 rows?</DIV>  <DIV><BR><B><I>Guido Neitzer &lt;guido.neitzer@pharmaline.de&gt;</I></B> escreveu:</DIV>  <DIV>  <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">  <div>On 23.12.2005, at 13:34 Uhr, Carlos Benkendorf wrote:<BR><BR>&gt; For some implementation reason in 8.0.3 the query is returning the <BR>&gt; rows in the correct order even without the order by but in 8.1.1 <BR>&gt; probably the implementation changed and the rows are not returning <BR>&gt; in the 
 correct
 order.<BR><BR>You will never be sure to get rows in a specific order without an <BR>"order by".<BR><BR>I don't know why PG is faster without ordering, perhaps others can <BR>help with that so you don't need a workaround like this:<BR><BR>If you can't force PostgreSQL to perform better on the ordered query, <BR>what about retrieving only the primary keys for the rows you want <BR>unordered in a subquery and using an "where primaryKey in (...) order <BR>by ..." statement with ordering the five rows?</div>  <div>&nbsp;</div>  <div><BR><BR>Like this:<BR><BR>select * from mytable where pk in (select pk from mytable where ...) <BR>order by ...;<BR><BR>I don't know whether the query optimizer will flatten this query, but <BR>you can try it.<BR><BR>cug<BR><BR><BR>-- <BR>PharmaLine Essen, GERMANY and<BR>Big Nerd Ranch Europe - PostgreSQL Training, Feb. 2006, Rome, Italy<BR>http://www.bignerdranch.com/classes/postgresql.shtml<BR><BR><BR></div></BLOCKQUOTE><BR></DIV><p>
		<hr size=1> 
Yahoo! doce lar. <a href="http://us.rd.yahoo.com/mail/br/tagline/homepage_set/*http://br.yahoo.com/homepageset.html">Faça do Yahoo! sua homepage.</a>
--0-1919189698-1135348518=:85449--