2017-02-11 19:51 GMT+01:00 Alexander Farber <alexander.farber@xxxxxxxxx>:
At the same time this advice fromworks, don't know why though:words=> select array_agg(u order by random())words-> from unnest(array['a','b','c','d','e','f']) u; array_agg---------------{d,a,f,c,b,e}
There is a change in plan
postgres=# explain analyze verbose select * from unnest(ARRAY['a','b','c','d','e','f']) order by random();
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Sort (cost=4.57..4.82 rows=100 width=40) (actual time=0.054..0.056 rows=6 loops=1)
Output: unnest, (random())
Sort Key: (random())
Sort Method: quicksort Memory: 25kB
-> Function Scan on pg_catalog.unnest (cost=0.00..1.25 rows=100 width=40) (actual time=0.029..0.033 rows=6 loops=1)
Output: unnest, random()
Function Call: unnest('{a,b,c,d,e,f}'::text[])
Planning time: 0.125 ms
Execution time: 0.119 ms
postgres=# explain analyze verbose select unnest(ARRAY['a','b','c','d','e','f']) order by random();
QUERY PLAN
------------------------------------------------------------------------------------------------
ProjectSet (cost=0.02..0.54 rows=100 width=40) (actual time=0.032..0.037 rows=6 loops=1)
Output: unnest('{a,b,c,d,e,f}'::text[]), (random())
-> Sort (cost=0.02..0.03 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=1)
Output: (random())
Sort Key: (random())
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)
Output: random()
Planning time: 0.100 ms
Execution time: 0.072 ms
In second case, the random function is called only once, and result is multiplied.
Maybe it is bug, because volatile functions should be evaluated every time
Regards
Pavel
