On Sat, Apr 18, 2009 at 11:19 PM, Grzegorz Jaskiewicz <g...@pointblue.com.pl> wrote: > This is a really funny one, because people naturally expect UNION [ALL] to > stay in the same order. Unlike the table, order here cannot change by > inserts/updates, etc. > I am sure many, even well experienced will stumble upon that one.
There is a misunderstanding here. UNION has *never* preserved the order of the subqueries before. In the OP's query it was *not* preserving the order. It was a coincidence that the order the subquery was in was sorted on the first field and since UNION resorted the whole result set by all the fields in order that meant it was in order by the first field. > Me is guessing, that UNION [ALL] performance just had to be improved for > CTEs ? Or was it something completely separate. Hash aggregates were new relative to set operations which have been around a very long time. They didn't take advantage of the new code but it was always fairly obvious that they should eventually have been changed to. I think it came along with fixing DISTINCT to use hash aggregates which was a similar situation. UNION ALL should still preserve the order of the subqueries. It just returns all the rows of each subquery one after the other with no other work. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
