From: | Pantelis Theodosiou <ypercube(at)gmail(dot)com> |
---|---|
To: | Shay Rojansky <roji(at)roji(dot)org> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-docs <pgsql-docs(at)postgresql(dot)org> |
Subject: | Re: Clarify the ordering guarantees in combining queries (or lack thereof) |
Date: | 2022-07-14 10:22:43 |
Message-ID: | CAE3TBxzS4OHsB23guzu0Xe-xvjs7tMODgJqopm6NyF0pSjcb+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
On Thu, Jul 14, 2022 at 9:16 AM Shay Rojansky <roji(at)roji(dot)org> wrote:
>
> >> I was trying to understand what - if any - are the guarantees with
> regards to ordering for combining queries (UNION/UNION ALL/...). From this
> message[1], it seems that UNION ALL does preserve the ordering of the
> operand queries, whereas UNION does not (presumably neither do INTERSECT,
> INTERSECT ALL, EXCEPT and EXCEPT ALL).
> >>
> >> The documentation[2] makes no mention of this, I'd suggest adding a
> note clarifying this.
> >
> > If you want ordered output use ORDER BY.
>
> I don't see how that could be done. Consider the following:
>
> (SELECT id FROM data ORDER BY id)
> UNION ALL
> (SELECT id FROM data ORDER BY id DESC);
>
> If there's a guarantee that UNION ALL preserves ordering - as Tom seems to
> indicate in the thread quoted above - then the above works. If there's no
> such guarantee, then AFAIK the above can't be rewritten; putting the ORDER
> BY outside - on the results of the UNION ALL - would order all results
> rather than preserving each resultset's ordering.
>
> No, there is no guarantee. It's just that UNION ALL works this way today
(preserving the order of the subselects) - and I'm not even sure about
that, it may not preserve the order in all cases, with different indexes or
partitioning or a parallel plan, etc.
In any case, there is no guarantee that the behaviour will not change in
the future due to planner improvements.
Best regards
Pantelis Theodosiou
> [1] https://www.postgresql.org/message-id/26825.1064858799@sss.pgh.pa.us
> [2] https://www.postgresql.org/docs/current/queries-union.html
>
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-07-14 12:01:48 | Re: Clarify the ordering guarantees in combining queries (or lack thereof) |
Previous Message | Shay Rojansky | 2022-07-14 08:16:10 | Re: Clarify the ordering guarantees in combining queries (or lack thereof) |