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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
