PostgreSQL select 20 random records

Question

How would I select 20 random rows in a SQL (PostgreSQL) query?

score 28 · Accepted Answer · edited Jun 01 '17 at 12:33

28

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 20

edited Jun 01 '17 at 12:33

answered May 22 '11 at 21:42

ditkin

10

Be warned though this is very very slow, don't do it if you have more than 20k rows. – Johan May 22 '11 at 21:57
@Johan I'm not sure if this was optimized since the posting of this question, but an `EXPLAIN` on 20k + rows shows the same cost for `RANDOM()` sort key, and any other column used as the sort key (e.g., `rating`). – Chris Cirefice Jul 22 '14 at 02:11
@ChrisCirefice, if the `rating` column has poor cardinality (or is not indexed) then it will have the same costs, because indexes can only be used if the conditions are right. Without more details that's all I can say. – Johan Jul 22 '14 at 12:18
3

@Johan Good point! I created a [SQLFiddle](http://sqlfiddle.com/#!15/80065/6) to demonstrate some performance differences of the `ORDER BY` statement. The results are that ordering by the primary key is the fastest, closesly followed by ordering by an index. Then, ordering by any field that is *not indexed* significantly increases the cost. However, `ORDER BY RANDOM()` is not *significantly slower* than ordering by a non-indexed column (at least for **250k rows**). The results could be drastically different for several million rows though... I didn't test that many. – Chris Cirefice Jul 22 '14 at 18:08

1 Answers1