2017-02-11 19:51 GMT+01:00 Alexander Farber <alexander.far...@gmail.com>:
> At the same time this advice from > http://stackoverflow.com/questions/42179012/how-to- > shuffle-array-in-postgresql-9-6-and-also-lower-versions > works, 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
