Re: 7.3.4 and 7.4 ORDER in queries

peter_e(at)gmx(dot)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
(416) 646 3304 x124 (land)