Why your Postgres queries are slow: 7 patterns we see in production

Since we launched SQL Debug, users have submitted thousands of slow or failing Postgres queries to ErrorLens. The same seven patterns keep showing up. None of them are exotic — they’re all things you might catch in a code review if you happened to be looking. But they’re also things that easily slip past a busy team and ship to production.

This post walks through each pattern with a real (anonymised) example, the symptom, the fix, and the search-and-destroy query you can run against your codebase to find similar bugs.

1. Implicit type cast on an indexed column

You have an index on users(id) where id is uuid. Then somewhere in your code you do:

SELECT * FROM users WHERE id = '7b2f1e9d-...'::text;

Postgres won’t use the UUID index because the comparison forces a cast. You get a sequential scan on a 50M-row table.

The signature: an EXPLAIN that shows Seq Scan on a column you’re sure is indexed. Look for ::text casts, parameter binding mismatches between the application driver and the column type, and string literals being compared against integer columns.

2. SELECT * over a wide table when you only need three columns

This isn’t a query-planner issue, it’s a network and TOAST-decompression issue. If your row has a 20-column wide table where four of those columns are large JSONB blobs, every row you fetch through SELECT * drags the JSONB across the wire and decompresses it in-memory, even when the calling code only reads id, email, created_at.

We see this most often in code generated by ORMs that don’t make column projection ergonomic. The fix is to project the columns you actually need.

3. OFFSET-based pagination on a deep page

SELECT ... ORDER BY created_at DESC LIMIT 20 OFFSET 100000 reads 100,020 rows and throws away 100,000 of them. On the first page this is invisible; on page 5,001 it’s a one-second query.

Switch to keyset pagination: WHERE created_at < $1 ORDER BY created_at DESC LIMIT 20, where $1 is the timestamp of the last row on the previous page. The created_at index does the work; the planner reads exactly 20 rows.

4. Function calls on the indexed side of a comparison

SELECT * FROM events WHERE LOWER(email) = 'alice@example.com';

If you have an index on email, this query won’t use it because LOWER(email) isn’t the indexed expression. Two fixes:

• Add a functional index: CREATE INDEX ON events (LOWER(email)).
• Or store emails normalised at write time so you don’t need LOWER at read.

This pattern shows up everywhere with case-insensitive search, DATE(created_at), EXTRACT(...), and JSON-path operators against indexed jsonb columns.

5. NOT IN against a subquery that returns NULL

SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM banned_accounts);

If banned_accounts.user_id is nullable and contains a single NULL, this query returns zero rows. The reason is three-valued logic: id NOT IN (1, 2, NULL) evaluates to UNKNOWN, which filters out every row.

Fix with NOT EXISTS or filter NULL out of the subquery. We’ve seen this pattern silently drop production data multiple times — the query “ran successfully” but returned the wrong result.

6. Foreign key without a matching index on the referencing side

Adding a foreign key in Postgres does not automatically create an index on the referencing column. ALTER TABLE orders ADD COLUMN user_id UUID REFERENCES users(id) creates the constraint, not the index.

The consequence shows up at delete time. DELETE FROM users WHERE id = $1 with the FK in place forces a sequential scan over orders to find rows that reference the deleted user. On a large orders table that takes a long time and holds locks.

Always add the matching index: CREATE INDEX ON orders(user_id). Periodically audit by joining pg_constraint with pg_index to find FKs without indexes.

7. jsonb queries that don’t use the GIN index

You have CREATE INDEX ON analyses USING gin (result_json). Then your code queries:

SELECT * FROM analyses WHERE result_json->>'severity' = 'critical';

That index isn’t used. The default GIN index on a jsonb column supports the @> containment operator, not the ->> extraction operator.

Two options: rewrite the query as WHERE result_json @> '{"severity": "critical"}' (uses the GIN index), or create a separate functional B-tree index on (result_json ->> 'severity') if you specifically want the extraction shape. We tend to do the latter when one path is queried hot and others aren’t.

How to find these in your codebase

Two practices help:

1. Log slow queries. log_min_duration_statement = 200ms in postgresql.conf. Pipe the slow-query log into ErrorLens once a week. The AI is good at clustering similar queries, so a single misbehaving ORM call appearing 10,000 times shows up as one cluster, not ten thousand reports.
2. EXPLAIN-in-CI. For your hot-path queries, capture the EXPLAIN (ANALYZE, BUFFERS) output in CI against a representative dataset. Diff the plan across PRs. A query that quietly loses its index hit will jump out — the row count won’t change but the plan node will switch from Index Scan to Seq Scan.

None of these patterns are new. The common thread is that they’re only visible at production data sizes, so they slip past local-dev testing where every table has 100 rows and every plan is fast. The fastest way to catch them is to read your slow-query log routinely. The fastest way to read your slow-query log routinely is to ask an AI to cluster and prioritise it.