From: Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>
To: pgsql-performance@postgresql.org
Subject: Re: Order by behaviour
Date: Fri, 23 Dec 2005 14:35:18 +0000 (GMT)
Message-ID: <20051223143518.85770.qmail@web35514.mail.mud.yahoo.com> (raw)
In-Reply-To: <55DD6E49-BC80-443D-9F6E-573B6CEE961D@pharmaline.de>
>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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: carlosbenkendorf@yahoo.com.br
Subject: Re: Order by behaviour
In-Reply-To: <20051223143518.85770.qmail@web35514.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox