Re: UNION messing up sorting WAS: psql performance

On Fri, Apr 15, 2005 at 12:23:14AM -0400, Joseph Shraibman wrote:
>
>
> Alvaro Herrera wrote:
>
> >>Incidentally when I did that I only got back one row. What's up with
> >>that?
> >
> >
> >Try with "union all" instead of plain union.
> >
> Talk about serendipity. The problem I've been struggling with for the
> last few hours has been why my query wasn't producing sorted output even
> though I put in an ORDER BY and the EXPLAIN shows that it is ordering.
> The DISTINCT implied by the UNION must have been messing up the sorting.
>
> The docs say
> (http://www.postgresql.org/docs/7.4/interactive/sql-select.html#SQL-UNION)
>
> select_statement is any SELECT statement without an ORDER BY, LIMIT, or
> FOR UPDATE clause. (ORDER BY and LIMIT can be attached to a
> subexpression if it is enclosed in parentheses.
>
>
>
> ... but I *did* put my SELECTs in parentheses. This is either a bug in
> pg or a serious ommision from the docs.

It's not a bug, though it could possibly made clearer in the docs. If
you want your final output in a specific order, you have to put you
ORDER BY in the very outermost level of the query. PostgreSQL is pretty
liberal about where you can put ORDER BY, but when it comes to output
ordering only the order of the final query step matters.

Why allow ordering elsewhere? Consider this more-performant replacement
for SELECT max(blah):

SELECT max
FROM (SELECT blah FROM table ORDER BY blah DESC LIMIT 1) a
;

Maybe not a great example since you'll only get one row back, but the
point is that the ORDER BY in the subquery doesn't mean a thing when it
comes to output order.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"