From: | Carlos Benkendorf <carlosbenkendorf(at)yahoo(dot)com(dot)br> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Order by behaviour |
Date: | 2005-12-23 14:35:18 |
Message-ID: | 20051223143518.85770.qmail@web35514.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
>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(dot)neitzer(at)pharmaline(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Guido Neitzer | 2005-12-23 15:03:36 | Re: Order by behaviour |
Previous Message | Kresimir Tonkovic | 2005-12-23 14:31:15 | Re: Order by behaviour |