Browse Teach Curate

ORDER BY RANDOM() Sorts the Whole Table to Pick One Row

Sorting by a random value gives every row a fresh random number, so the database must generate one for every row and sort them all before returning the one you wanted. On a large table that is the most expensive possible way to answer a cheap question. Picking a random id in the application, or using TABLESAMPLE for a rough sample, gets the same result without the full sort.

Questions this Concept answers

Why can no index help `ORDER BY random()`?

jeremy Text

Efficient PostgreSQL Strategies for Selecting Random Rows from Large Tables

This article explains why ORDER BY random() is so slow on a big table: the database has to make a fresh random number for every single row and sort them all just to hand back a few. It then shows che…