Missing WHERE clause

Claim: Performance

A top-level SELECT that reads from a table without a WHERE clause returns every row. For small lookup tables this is fine, but for large tables it means a full sequential scan — reading the entire table regardless of how many rows the caller actually needs.

Example

-- Reads every row from a potentially large table
SELECT id, name FROM customers;

Why it matters

Without a WHERE clause, PostgreSQL has no choice but to scan the whole table. This means:

• I/O proportional to table size — every page in the table is read from disk or shared buffers
• No index usage — indexes only help when there’s a predicate to filter on
• Network transfer — all rows are sent to the client, which can be slow for large tables

For tables with millions of rows, an unfiltered SELECT can lock up a connection for seconds or longer and put heavy load on shared buffers.

When it’s acceptable

Unfiltered selects are reasonable for:

• Small reference or configuration tables (countries, status codes, feature flags)
• Aggregations that genuinely need all rows (SELECT count(*) FROM orders)
• Data exports or ETL pipelines designed to process full tables

How to fix it

Add a WHERE clause to filter to the rows you need:

SELECT id, name FROM customers WHERE active = true;

If you need to browse large result sets, combine with pagination:

SELECT id, name FROM customers WHERE active = true ORDER BY name LIMIT 50 OFFSET 0;

Detection

Query Doctor reports this Finding on a top-level SELECT with no WHERE clause when its FROM reads a table’s rows unfiltered. A table listed directly in FROM is read unfiltered. A CTE counts only when its own query reads a table unfiltered. A CTE with a WHERE, a LIMIT or a single-row aggregate does not, and neither does a recursive CTE whose every arm is filtered. A FROM that holds only subqueries is not reported.

A whole-table aggregate such as SELECT count(*) FROM orders is not reported, because it needs every row by design. An aggregate with GROUP BY is.

The Finding’s marker points to the first table in the FROM clause.