7.3.4 and 7.4 ORDER in queries

[javier garcia - CEBAS]

Hi,
I'm not an expert, not by far.
I've just installed postgres7.4 and have realized tat the order of rows in
queries in different to that in 7.3.4. It seems that previously it was more
logic, ordering by the first column, then by the second,... by default.
At least this is so in a couple of queries I have. And now I have to
explicitly say the order I want.

Could someone just tell me why is this so?

Thanks and best wishes,

Javier
................

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Doug McNaught]

javier garcia - CEBAS <rn***@cebas.csic.es> writes:

Hi,
I'm not an expert, not by far.
I've just installed postgres7.4 and have realized tat the order of rows in
queries in different to that in 7.3.4. It seems that previously it was more
logic, ordering by the first column, then by the second,... by default.
At least this is so in a couple of queries I have. And now I have to
explicitly say the order I want.

Could someone just tell me why is this so?

Unless you specify ORDER BY, the order of matching rows returned by a
query is completely up to the server. So it's not suprising that the
ordering changed.

If you depend on order, always use ORDER BY.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Peter Eisentraut]

javier garcia - CEBAS writes:

I've just installed postgres7.4 and have realized tat the order of rows in
queries in different to that in 7.3.4. It seems that previously it was more
logic, ordering by the first column, then by the second,... by default.
At least this is so in a couple of queries I have. And now I have to
explicitly say the order I want.

Unless you specify ORDER BY explicitly, the order is completely random.
In practice, it depends on the order of the rows on disk and the query
execution plan, but those are implementation details that may change over
time. If you need a particular order, you need to say so.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Christopher Browne]

rn***@cebas.csic.es (javier garcia - CEBAS) writes:

I'm not an expert, not by far.
I've just installed postgres7.4 and have realized tat the order of rows in
queries in different to that in 7.3.4. It seems that previously it was more
logic, ordering by the first column, then by the second,... by default.
At least this is so in a couple of queries I have. And now I have to
explicitly say the order I want.

Could someone just tell me why is this so?

You always had to explicitly state ORDER BY if you wanted to get a
specific ordering; that's how SQL is designed.

There are a number of cases where earlier versions of PostgreSQL had
to perform specific sort procedures in order to perform GROUP BY
queries, whereas 7.4 is able to use unordered hash tables to collect
the data, thereby eliminating the sorts.

That is quite likely to be the sort of thing that you are observing.

If you want the data to be returned in a particular order, then you
MUST state that order, otherwise you'll get whatever the database
gives you, and yes, indeed, the ordering that is imposed implicitly
can and does change.
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
[phone] x124 (land)

[Christopher Browne]

pe*****@gmx.net (Peter Eisentraut) writes:

javier garcia - CEBAS writes:

I've just installed postgres7.4 and have realized tat the order of rows in
queries in different to that in 7.3.4. It seems that previously it was more
logic, ordering by the first column, then by the second,... by default.
At least this is so in a couple of queries I have. And now I have to
explicitly say the order I want.
Unless you specify ORDER BY explicitly, the order is completely random.

That's not _quite_ true. It is somewhat unusual for the ordering of
query results to be "random;" there is typically some underlying logic
that is NOT random behind it.

What is a bit fairer is to say that, absent an ORDER BY clause, the
order is "totally unspecified," and that any given ordering is as
legitimate as any other ordering.

"Random order" is what you'd generally observe if the data is being
accessed via hashing (which has become a lot more popular in 7.4 than
it used to be).

Other 'surprising' orders might include:

- Data might be returned in the order in which it was inserted, where
if an old record was overwritten by a new one, that might draw in new
records sooner;

- Data returns might start with the set of pages that were already in
the page cache.

[The point being that it is reasonable, absent of ORDER BY, to return
results based on the physical location of the data.]

I am quite surprised that the original poster got to expect an
implicit "ORDER BY 1, 2". That seems _quite_ surprising.
In practice, it depends on the order of the rows on disk and the query
execution plan, but those are implementation details that may change over
time. If you need a particular order, you need to say so.

Very true.
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
[phone] x124 (land)

[Tom Lane]

Christopher Browne <cb******@libertyrms.info> writes:

I am quite surprised that the original poster got to expect an
implicit "ORDER BY 1, 2". That seems _quite_ surprising.

Pre-7.4, GROUP BY queries would tend to work like that, but it was
always an implementation artifact, never a documented much less
guaranteed behavior ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)