ORDER BY random()

Claim: Performance

ORDER BY random() forces PostgreSQL to read every row of the table and compute random() for each one before it can return any. Even with LIMIT 1, every row is read.

In depesz’s benchmarks on a 10,000-row table, this approach was roughly 230x slower than an index-based method.

Example

-- Reads and ranks every row to return one
SELECT * FROM posts ORDER BY random() LIMIT 1;

Why it happens

random() returns a different value for each row on every call, so no index can return rows in that order. PostgreSQL must read every row and compute random() for it. Without a LIMIT, it then sorts every row. With a LIMIT, it keeps only the top rows in a heap, which needs less memory but reads the same number of rows. The cost grows linearly with the size of the table either way.

How to fix it

Option 1: TABLESAMPLE

TABLESAMPLE returns a random sample of the table, and sorting a small sample is cheap. It has two methods, and only SYSTEM avoids reading the whole table:

• SYSTEM picks pages at random and reads only those. It returns every row on a chosen page, so the sample is clustered by page.
• BERNOULLI reads every page and keeps each row with the given probability. Its sample is not clustered, but the whole table is still scanned.

-- Reads about 1% of the pages, then sorts the rows on them
SELECT * FROM posts TABLESAMPLE SYSTEM (1) ORDER BY random() LIMIT 1;

-- Reads every page, keeps about 1% of the rows, then sorts those
SELECT * FROM posts TABLESAMPLE BERNOULLI (1) ORDER BY random() LIMIT 1;

Keep the ORDER BY random() on the sample. Both methods return rows in the order they are stored, so TABLESAMPLE ... LIMIT 1 on its own returns a row biased toward the start of the table. On a small table, SYSTEM (1) can choose no page at all, and the query then returns no row. Adjust the percentage based on table size.

Query Doctor still reports this Finding on these queries, because it reads only the SQL text and cannot tell that the sort covers a sample.

Option 2: Index-based random lookup

If the primary key is sequential (or near-sequential), you can pick a random ID within the key range:

SELECT * FROM posts
WHERE id >= (SELECT floor(random() * (SELECT max(id) FROM posts))::int)
ORDER BY id
LIMIT 1;

This uses the primary key index to seek directly to a random position. It only works well when there are few gaps in the ID sequence.

Detection

Query Doctor reports this Finding when it finds an ORDER BY expression that is a call to random(). The Finding’s marker points to the random() call in the formatted SQL view.