How would I select 20 random rows in a SQL (PostgreSQL) query?
Add a comment
|
1 Answer
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 20
4 Comments
Johan
Be warned though this is very very slow, don't do it if you have more than 20k rows.
Chris Cirefice
@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).Johan
@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.Chris Cirefice
@Johan Good point! I created a SQLFiddle 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.