Re: union vs. sort

Karel Zak <zakkr@zf.jcu.cz> writes:
>  I'm  surprise  with query  plan  that  PostgreSQL planner  prepare  for
>  selects with ORDER  BY if all data are from  sub-select that is already
>  sorted.

This isn't simply a matter of "omitting the sort".  Even if the inputs
are sorted, their concatenation (Append result) isn't sorted: "1 2 3 4"
and "1 3 7 9" are sorted, but "1 2 3 4 1 3 7 9" isn't.

To do what you're thinking about, we'd have to build a variant
implementation of Unique that merges two presorted inputs --- and it
wouldn't work for more than two inputs (at least not without a lot of
pain ... Append is a messy special case in many ways, and we'd have to
duplicate most of that cruft to make an N-input version of Unique).
This is possible, without doubt, but I'm not excited about expending
that much time on it.  You haven't shown any evidence that this would be
an important optimization in practice.
        regards, tom lane