Thread: 7.3.4 and 7.4 ORDER in queries
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 ...............
javier garcia - CEBAS <rn001@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
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 peter_e@gmx.net
rn001@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 (416) 646 3304 x124 (land)
peter_e@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 (416) 646 3304 x124 (land)
Christopher Browne <cbbrowne@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