Re: union vs. sort

Karel Zak <zakkr(at)zf(dot)jcu(dot)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