Missing LIMIT clause

Claim: Performance

A top-level SELECT that reads from a table without a LIMIT clause returns every matching row. If the caller only needs a page of results or the first few matches, the rest is wasted work — extra I/O, sorting, and network transfer for rows that are immediately discarded.

Example

-- Returns all matching orders, even if the UI shows 25 at a time
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

Why it matters

Wasted work

Without LIMIT, PostgreSQL returns all matching rows. If there are 10,000 matches but the application only displays 25, the query sends 400 times the rows the page shows.

Interaction with ORDER BY

When LIMIT is present, PostgreSQL can use a top-N heapsort — a much cheaper algorithm that only tracks the top N rows instead of sorting the entire result set. Without LIMIT, a full sort is required.

Memory pressure

Large unbounded result sets consume memory both on the database server (for sorting and buffering) and on the application side. This can cause spills to disk on the server and out-of-memory errors in the application.

When it’s acceptable

LIMIT is not needed when:

• You’re aggregating (SELECT count(*), SELECT sum(total))
• The query feeds an INSERT INTO ... SELECT or similar bulk operation
• You genuinely need all matching rows (exports, reports, ETL)

How to fix it

Add a LIMIT to match what the caller actually consumes:

SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 25;

For paginated APIs, use LIMIT with OFFSET or keyset pagination:

-- Keyset pagination (more efficient than OFFSET for deep pages)
SELECT * FROM orders
WHERE customer_id = 42 AND created_at < $1
ORDER BY created_at DESC
LIMIT 25;

Detection

Query Doctor reports this Finding on a top-level SELECT statement with no LIMIT clause whose FROM names a table or a CTE. One whose FROM holds only subqueries is not reported, and neither is one that returns a single row from a built-in aggregate such as count(*).

When Query Doctor has the schema, a SELECT whose WHERE pins a unique key of every table it reads is not reported. A key is pinned by = to a value, by IN with a list of values, or by = ANY with an array parameter or an ARRAY[…] of values. WHERE id IN ($1, $2) on a primary key returns at most two rows, so a LIMIT adds nothing. A partial unique index does not count, and neither does a unique index on an expression. The SELECT of an INSERT … SELECT and a SELECT … INTO are not reported, because both write their rows into a table rather than return them to the caller. The Finding’s marker points to the first table listed directly in FROM. When FROM starts with a join, the Finding has no marker.