Viewing entries tagged with tablesample. Return to full view.

Getting random rows faster. Very much faster.

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.

Continue reading

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

Past

PGConf.NYC 2021
Dec 2-3, 2021
New York City, USA
PGDay Austria 2021
Sep 17, 2021
Vienna, Austria
Postgres London 2021
May 12, 2021
Online, Online
FOSDEM 2021
Feb 6-7, 2021
Online, Online
Stockholm PUG Oct 2020
Oct 27, 2020
Stockholm/Online, Sweden
More past conferences