Re: Ordering of window functions with no order specified?

Am 15.06.2017 um 06:34 schrieb Ken Tanzer:
> Hi. If you use a window function and don't specify an order, will the
> rows be processed in the same order as the query results?
>
> In this particular case, I'm wondering about row_number(), and whether
> I explicitly need to repeat the ordering that's already specified in
> the query?
>
> SELECT a,b,c,row_number()
> OVER (PARTITION BY a) -- Do I need an ORDER BY c,b here?
> FROM foo
> ORDER BY c,b
>
> Also, I'm interested in both what if any behavior is guaranteed, and
> what gets done in practice. (i.e., a SELECT with no order doesn't
> have guarantees, but in practice seems to return the results in the
> order they were added to the table. Is it something similar here?)
>

That's different queries and results:

test=# create table foo as select s%5 a, random() as b, random() as c
from generate_series(1, 20) s;
SELECT 20

test=*# select a, b, c, row_number() over (partition by a) from foo
order by c,b;
a | b | c | row_number
---+--------------------+---------------------+------------
3 | 0.293813084252179 | 0.00748801836743951 | 4
2 | 0.366433540824801 | 0.0825160844251513 | 3
2 | 0.646373085677624 | 0.144253523554653 | 2
1 | 0.436142998747528 | 0.149559560697526 | 4
4 | 0.766950330231339 | 0.279563031159341 | 2
4 | 0.780563669744879 | 0.36753943329677 | 4
4 | 0.521357014775276 | 0.37830171873793 | 1
3 | 0.641054477542639 | 0.438840930350125 | 3
0 | 0.23528463486582 | 0.506252389866859 | 1
1 | 0.883372921962291 | 0.607358017936349 | 2
2 | 0.0624627070501447 | 0.610814236104488 | 4
1 | 0.203920441213995 | 0.680096843745559 | 3
0 | 0.945639119483531 | 0.686336697079241 | 3
2 | 0.360363553743809 | 0.702507333364338 | 1
0 | 0.493005351629108 | 0.739280233159661 | 4
0 | 0.844849191140383 | 0.756641649641097 | 2
1 | 0.375874035060406 | 0.771526555530727 | 1
4 | 0.0844886344857514 | 0.837361172772944 | 3
3 | 0.50597962597385 | 0.841444775927812 | 2
3 | 0.0100470245815814 | 0.899044481106102 | 1
(20 Zeilen)

test=*# select a, b, c, row_number() over (partition by a order by c,b)
from foo order by c,b;
a | b | c | row_number
---+--------------------+---------------------+------------
3 | 0.293813084252179 | 0.00748801836743951 | 1
2 | 0.366433540824801 | 0.0825160844251513 | 1
2 | 0.646373085677624 | 0.144253523554653 | 2
1 | 0.436142998747528 | 0.149559560697526 | 1
4 | 0.766950330231339 | 0.279563031159341 | 1
4 | 0.780563669744879 | 0.36753943329677 | 2
4 | 0.521357014775276 | 0.37830171873793 | 3
3 | 0.641054477542639 | 0.438840930350125 | 2
0 | 0.23528463486582 | 0.506252389866859 | 1
1 | 0.883372921962291 | 0.607358017936349 | 2
2 | 0.0624627070501447 | 0.610814236104488 | 3
1 | 0.203920441213995 | 0.680096843745559 | 3
0 | 0.945639119483531 | 0.686336697079241 | 2
2 | 0.360363553743809 | 0.702507333364338 | 4
0 | 0.493005351629108 | 0.739280233159661 | 3
0 | 0.844849191140383 | 0.756641649641097 | 4
1 | 0.375874035060406 | 0.771526555530727 | 4
4 | 0.0844886344857514 | 0.837361172772944 | 4
3 | 0.50597962597385 | 0.841444775927812 | 3
3 | 0.0100470245815814 | 0.899044481106102 | 4
(20 Zeilen)

And also different execution plans:

test=*# explain analyse select a, b, c, row_number() over (partition by
a) from foo order by c,b;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Sort (cost=239.18..243.43 rows=1700 width=20) (actual
time=0.134..0.138 rows=20 loops=1)
Sort Key: c, b
Sort Method: quicksort Memory: 26kB
-> WindowAgg (cost=118.22..147.97 rows=1700 width=20) (actual
time=0.056..0.101 rows=20 loops=1)
-> Sort (cost=118.22..122.47 rows=1700 width=20) (actual
time=0.048..0.054 rows=20 loops=1)
Sort Key: a
Sort Method: quicksort Memory: 26kB
-> Seq Scan on foo (cost=0.00..27.00 rows=1700
width=20) (actual time=0.021..0.028 rows=20 loops=1)
Planning time: 0.104 ms
Execution time: 0.200 ms
(10 Zeilen)

test=*# explain analyse select a, b, c, row_number() over (partition by
a order by c,b) from foo order by c,b;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Sort (cost=247.68..251.93 rows=1700 width=20) (actual
time=0.115..0.119 rows=20 loops=1)
Sort Key: c, b
Sort Method: quicksort Memory: 26kB
-> WindowAgg (cost=118.22..156.47 rows=1700 width=20) (actual
time=0.056..0.090 rows=20 loops=1)
-> Sort (cost=118.22..122.47 rows=1700 width=20) (actual
time=0.048..0.054 rows=20 loops=1)
Sort Key: a, c, b
Sort Method: quicksort Memory: 26kB
-> Seq Scan on foo (cost=0.00..27.00 rows=1700
width=20) (actual time=0.019..0.025 rows=20 loops=1)
Planning time: 0.100 ms
Execution time: 0.173 ms
(10 Zeilen)

As you can see, different sort keys for the WindowAgg-Sort.

Please don't mix the ORDER BY for the window-function and for the
result-set. Use alwyas an explicit ORDER BY if you expect an ordered result.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com