The 5 most common Postgres SQL mistakes, with their EXPLAIN fingerprints

Most performance problems in a Postgres application come from one of a short list of avoidable SQL patterns. The list has not changed much in a decade. What follows are the five that show up most often in real codebases, each with the specific symptom it produces in EXPLAIN ANALYZE, the fix, and the reason the fix works. All of these apply to any Postgres, managed or self-hosted.

1. Filtering on an unindexed column

The most common and the most damaging. A query like SELECT * FROM sessions WHERE user_id = $1 on a 50-million-row table runs a sequential scan if user_id is not indexed, reads every page of the table, and takes seconds per call. At low traffic the performance hit is invisible. At high traffic it saturates CPU.

The symptom in EXPLAIN is a Seq Scan node with a large Rows Removed by Filter count on a table that has more than a few thousand rows.

EXPLAIN ANALYZE SELECT * FROM sessions WHERE user_id = 12345;

 Seq Scan on sessions
   Filter: (user_id = 12345)
   Rows Removed by Filter: 49,999,988
   (actual time=2104.2..2847.1 rows=12 loops=1)

The fix is an index:

CREATE INDEX CONCURRENTLY sessions_user_id_idx ON sessions (user_id);

Always CONCURRENTLY in production. A plain CREATE INDEX holds a lock that blocks writes for the duration of the build, which on a large table is long enough to cause an outage. Verify after that the new plan is an Index Scan, not a Seq Scan.

2. SELECT * when you need three fields

SELECT * is fine during development. In production it costs real money. Every column in the table is serialized out of the heap, shipped over the network, and deserialized in the application. For a table with a TOAST'd JSONB column, a wide text column, or a row-level array, SELECT * can move a hundred kilobytes per row when the caller only needed thirty bytes.

The harder cost is that SELECT * prevents index-only scans. An index-only scan returns rows directly from the index without touching the heap, which is the fastest plan Postgres can produce for a read-heavy hot path. Index-only scans require that every column the query returns is in the index. SELECT * guarantees at least one column is not, so the planner has to fall back to a regular index scan that visits the heap for every row.

The fix is explicit. List the columns you actually need. If there are three, write three. If the query is a hot path, consider a covering index:

CREATE INDEX CONCURRENTLY sessions_user_id_covering_idx
  ON sessions (user_id) INCLUDE (created_at, expires_at);

Combined with SELECT user_id, created_at, expires_at FROM sessions WHERE user_id = $1, this can produce an Index Only Scan plan. Ten times less work than a heap lookup.

3. Deep OFFSET pagination

Pagination with LIMIT 20 OFFSET N is fine for the first few pages and catastrophic past about N=10,000. The reason: Postgres has to produce the first N rows to throw them away, and the only way to do that is to read them. OFFSET 100000 means reading a hundred thousand rows and discarding them, every page.

A cold version of OFFSET 100000 on a million-row table takes seconds. Users rarely page that far, but web crawlers and abusive scripts do, and a single bad actor can saturate the database by paginating to the end of a large table.

The fix is keyset pagination. Instead of "give me rows 100,001 through 100,020 by count," ask "give me the next 20 rows after this specific ID." The query references the primary key (or any unique index), which Postgres can seek to directly:

-- First page:
SELECT id, title, created_at FROM posts
ORDER BY id DESC LIMIT 20;

-- Next page, where $last_id is the id of the last row on the previous page:
SELECT id, title, created_at FROM posts
WHERE id < $last_id
ORDER BY id DESC LIMIT 20;

Every page takes the same constant time regardless of depth, because every page is an index seek followed by twenty sequential reads.

4. ORM-generated N+1 cascade

An ORM that helpfully auto-loads related objects is a performance footgun in disguise. A list of 20 projects with related owner and latest-task information, fetched through project.owner.name and project.latest_task.title in application code, expands into 1 parent query plus 40 child queries, one round trip each.

The symptom: no single query is slow, but the page is. The signature in pg_stat_statements is a few simple SELECTs with call counts in the hundreds of thousands and mean_exec_time under a millisecond. EXPLAIN ANALYZE cannot see this, because the planner works on one query at a time and has no visibility into the loop that issued them.

The fix is one query that asks for everything at once. JOIN handles the one-to-one relationships. LATERAL handles the "most recent task per project" pattern that JOIN alone cannot express cleanly:

SELECT p.id, p.name, o.full_name AS owner_name, latest.title AS latest_task
FROM projects p
JOIN users o ON o.id = p.owner_id
LEFT JOIN LATERAL (
  SELECT title FROM tasks t
  WHERE t.project_id = p.id
  ORDER BY t.created_at DESC LIMIT 1
) AS latest ON true
WHERE p.user_id = $1;

Most ORMs have an escape hatch for this: includes, preload, joinedload, or a raw-SQL method. Use it.

5. Casting a column inside the WHERE clause

A WHERE clause like WHERE lower(email) = $1 or WHERE created_at::date = '2025-01-15' cannot use an index on email or created_at. The planner treats the expression as a function applied to every row, which forces a sequential scan or a much less selective index choice. The query is correct, and slow.

Two fixes. If the cast is incidental, move it to the parameter side of the comparison:

-- Slow, cannot use index on created_at:
SELECT * FROM orders WHERE created_at::date = '2025-01-15';

-- Fast, can use index on created_at:
SELECT * FROM orders WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16';

If the cast is essential (case-insensitive email lookup, for example), create an expression index that matches the exact expression in the query:

CREATE INDEX CONCURRENTLY users_lower_email_idx ON users (lower(email));

-- Now this query uses the index:
SELECT * FROM users WHERE lower(email) = 'ada@example.com';

Expression indexes are real indexes, with real maintenance cost on writes. Only create one for a case-insensitive or function-applied lookup that actually runs on a hot path.

What a pre-merge check catches

Three of these five are deterministically detectable at PR time from the SQL alone. A check that runs against the repo and the live schema can flag:

• Mistake 1 by comparing filtered columns in the DDL against the existing index list on the target table.
• Mistake 2 by grepping for SELECT * in migration files and new application code.
• Mistake 5 by parsing WHERE clauses and detecting function calls or casts on columns that match an existing index's base column but not its expression.

That is what Datapace does before merge. Honest limits: the check cannot detect Mistakes 3 and 4 from SQL alone, because those live in application call patterns. pg_stat_statements call-count regression after deploy is how Datapace catches those, and the attribution to a commit is probabilistic, based on timing.

Frequently asked questions

Are these the only five that matter?

They are the five that show up most often. A longer list would add: missing composite indexes when queries filter on two columns, unused indexes that slow down writes, bloat from UPDATE-heavy tables that never VACUUM, and misuse of DISTINCT when GROUP BY was meant. The five above cover most cases in practice.

How do I find these in my own database right now?

Run EXPLAIN ANALYZE BUFFERS on the top five queries by total_exec_time in pg_stat_statements. Each of the five mistakes leaves a specific fingerprint in the output: Seq Scan with high Rows Removed by Filter (Mistake 1), Index Scan with many heap buffer reads (Mistake 2), a Limit node with a high loops count (Mistake 3), small queries with call counts that do not match any user-facing endpoint (Mistake 4), or a Seq Scan with a Filter: line that wraps a column in a function (Mistake 5).

Is SELECT * actually that bad if my rows are small?

On a narrow table with no TOAST, the wire and serialization cost is negligible. The real reason to avoid it is the index-only-scan argument: the planner cannot use index-only scans for a query that returns every column, and on hot read paths that plan is several times cheaper than the alternative. If your row is ten columns of fixed-width scalars, SELECT * costs you a factor of two. If your row has a wide JSONB or array, it costs you much more.

Is there a tool that catches all five at PR time?

Three of the five are detectable from SQL alone (Mistakes 1, 2, 5). The other two require comparing database statistics across deploy boundaries. Datapace runs both checks: static analysis of the migration for the first three, and pg_stat_statements diffs across commits for the last two. Flagging is probabilistic on the second set and deterministic on the first.

Sources

1. PostgreSQL documentation, Indexes
2. PostgreSQL documentation, Expression indexes
3. PostgreSQL documentation, Index-only scans
4. Markus Winand, Keyset pagination