Getting a single random row, or a few rows, from a table in order to get representative data for example is a frequent need. The most common way to do this in PostgreSQL is using ORDER BY random()
like:
SELECT id FROM data ORDER BY random() LIMIT 1
But when run on a large table this can be very slow because it will have to scan the entire table to find the rows. Jonathan Katz mentioned a different way to do it on Twitter, which reminded me that people keep coming up with different (and sometimes very complicated) ways of trying to solve this problem.
And while Jonathan's method (he has the super simple sample code and results up on a gist) is still about twice as fast as ORDER BY random()
on my test (with his data), it comes with some problems. For example, it requires a contiguous set of id
values, that have to be integers. And it still takes about a second to run on my machine with his sample of 5 million rows -- and will keep getting slower as the table grows.
And it turns out, if you don't need your row to be perfectly random, just mostly random, and can deal with some caveats, PostgreSQL has built-in functionality that does the job about 20,000 times faster than Jonathan's version and 40,000 times faster than ORDER BY random()
. Enter TABLESAMPLE
.