Re: [GENERAL] Ordering of window functions with no order specified? - Mailing list pgsql-general

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