LIMIT without ORDER BY

Severity: Warning

Using LIMIT or FETCH FIRST N ROWS ONLY without ORDER BY returns an arbitrary subset of rows. PostgreSQL does not guarantee row order without an explicit ORDER BY — the order depends on physical storage layout, the access method chosen (Seq Scan vs Index Scan), and parallelism. The same query can return different rows between executions.

Example

-- Non-deterministic: may return different rows each time
SELECT * FROM users LIMIT 10;

-- Deterministic: always returns the same 10 rows
SELECT * FROM users ORDER BY id LIMIT 10;

Why it matters

Unpredictable results

Without ORDER BY, PostgreSQL returns rows in whatever order it finds them. This order can change after a VACUUM, an index creation, a planner cost re-estimate, or when parallel workers are involved. Two identical queries can return different rows.

Broken pagination

LIMIT/OFFSET pagination without ORDER BY is especially dangerous. Rows can be skipped or duplicated across pages because the underlying order shifts between requests:

-- Page 1 and page 2 may overlap or skip rows
SELECT * FROM orders LIMIT 10 OFFSET 0;
SELECT * FROM orders LIMIT 10 OFFSET 10;

How to fix it

Add an ORDER BY on a deterministic column — typically a primary key or unique column:

SELECT * FROM users ORDER BY id LIMIT 10;

For pagination, always order by a unique column to guarantee stable page boundaries:

SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10;

For LIMIT 1 existence checks, consider using EXISTS instead — it stops at the first matching row without needing an order:

-- Instead of: SELECT * FROM users WHERE email = '[email protected]' LIMIT 1
SELECT EXISTS (SELECT 1 FROM users WHERE email = '[email protected]');

Detection

Query Doctor flags this nudge when a SELECT statement has a LIMIT clause but no ORDER BY clause. This includes subqueries, set operations (UNION/INTERSECT/EXCEPT), and FETCH FIRST syntax.