CODEWITHSUNDEEP
postgresql
Martin Sellergren
Martin Sellergren

Reputation: 95

Order preserved in window function without ORDER BY

Is unordered_window guaranteed to provide elements in same order when used repeatedly in select? I was hoping to avoid the cost of ordering since the order is irrelevant as long as it's the same.

I.e, will xs[i] and ys[i] always be elements from same row in xyz?

select
   array_agg(x) over unordered_window as xs,
   array_agg(y) over unordered_window as ys
from
   xyz
window
   unordered_window as (partition by z);

Upvotes: 1

Views: 525

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247370

Use EXPLAIN (VERBOSE, COSTS OFF) to see what happens:

                        QUERY PLAN                         
═══════════════════════════════════════════════════════════
 WindowAgg
   Output: array_agg(x) OVER (?), array_agg(y) OVER (?), z
   ->  Sort
         Output: z, x, y
         Sort Key: xyz.z
         ->  Seq Scan on laurenz.xyz
               Output: z, x, y

There is only a single sort, so we can deduce that the order will be the same.

But that is not guaranteed, to it is possible (albeit unlikely) that the implementation may change.

But you see that a sort is performed anyway. You may as well add the ORDER BY; all that will do is another sort key, which won't slow down the execution much. So you might just as well add the ORDER BY and be safe.

Upvotes: 1

Related Questions