Evan Jones <e...@evanjones.ca> wrote: > I *know* that without an ORDER BY clause, the database is free to reorder > results in any way it likes. However, I recently ran into a case where the > *SAME* query was returning results in a different order *within* a single > transaction, which surprised me (we fixed it by adding the missing ORDER BY). > I > would assume that once a transaction obtains a snapshot, all its read > operations > would return the same results.
That is not a valid assumption. For one thing, the default transaction isolation level is read committed, and at that isolation level you are not guaranteed to even get the same *rows* running the same query twice within the same transaction, much less in the same order. At any isolation level statistics could change, resulting in a different plan on two successive executions. Even running the same plan using the same snapshot you could get a different order if you have not specified one with ORDER BY. As one example, a sequential scan of a table won't necessarily start at the beginning of the heap -- if there is already a sequential scan in progress for another process, the new one will start at the point the other one is at, and "wrap around". This can save a lot of physical disk access, resulting in better performance. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
