ORDER BY random()

Severity: Warning

ORDER BY random() forces PostgreSQL to perform a sequential scan of the entire table, generate a random value for every row, then sort all rows before discarding all but the requested LIMIT. Even with LIMIT 1, every row is read and sorted.

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

Example

-- Full table scan + sort (O(n log n))
SELECT * FROM posts ORDER BY random() LIMIT 1;

Why it happens

random() returns a different value for each row on every call, so PostgreSQL cannot use any index — it must evaluate every row, sort the entire result set by the random values, and then return the top rows. The cost scales linearly with table size for the scan and O(n log n) for the sort, regardless of the LIMIT.

How to fix it

Option 1: TABLESAMPLE

PostgreSQL’s TABLESAMPLE clause selects a random sample of rows at the block level without scanning the full table:

SELECT * FROM posts TABLESAMPLE BERNOULLI(1) LIMIT 1;

BERNOULLI(1) samples approximately 1% of rows. The sample is not perfectly uniform — it operates at the page level — but it is dramatically faster on large tables. Adjust the percentage based on table size.

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.

Option 3: TABLESAMPLE SYSTEM

For even faster sampling at the cost of less uniformity, SYSTEM sampling picks random pages rather than rows:

SELECT * FROM posts TABLESAMPLE SYSTEM(1) LIMIT 1;

This is the fastest option but can return clustered results since it selects whole pages.

Detection

Query Doctor flags this nudge when it finds a SortBy node in the ORDER BY clause whose expression is a function call to random(). The nudge marker points to the random() call in the formatted SQL view.