Finding Slow Queries in Postgres

When a backend request is slow, the database is the first place to look, not the last. Postgres is our transactional store (through Drizzle and postgres.js), and the large majority of “why is this endpoint slow?” ends at a query doing far more work than it should. This is the concrete companion to How to Debug Performance: the general loop is measure → locate → ask why → change one thing; this is that loop pointed at the database.

The two failures that cause almost all of it: one query that scans the whole table because it’s missing an index, and the N+1, one query silently becoming hundreds because you looped. Both are invisible until you look, and obvious once you do.

1. Find the slow query (don’t guess)#

You already have the data. The OpenTelemetry auto-instrumentation makes every Postgres query a span on the request’s trace, with its timing. So the first move isn’t to read code, it’s to open the slow request’s trace and look at its queries:

• One query is slow → it’s probably scanning too much. Go to step 2, EXPLAIN it.
• Many near-identical queries on one request → that’s an N+1. Go to step 3.

This is the measure-first rule: the trace tells you which query to look at, so you’re not optimizing the one you assumed was slow.

2. EXPLAIN ANALYZE: read what Postgres actually does#

EXPLAIN shows the plan Postgres intends; EXPLAIN ANALYZE runs it and shows what actually happened, real time and real row counts. (It executes the query, so don’t ANALYZE a write you don’t want to run.)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';

The one line that matters most is the scan type at the bottom of the plan:

Seq Scan on orders  (cost=0.00..18700 rows=1 width=…)
  Filter: (customer_id = 42 AND status = 'paid')
  Rows Removed by Filter: 499999          ← read half a million rows to return one
  actual time=142.318..142.319

Seq Scan means Postgres read the entire table and threw almost all of it away, “Rows Removed by Filter: 499999” is the tell. On a big table, filtered by a column, that’s the classic missing index. What you want to see instead:

Index Scan using orders_customer_id_status_idx on orders
  Index Cond: (customer_id = 42 AND status = 'paid')
  actual time=0.021..0.023          ← ~6,000x faster

Read the plan bottom-up (the innermost node runs first). You’re hunting three things: a Seq Scan on a large table, a big Rows Removed by Filter (work done and discarded, the read-amplification idea), and any single node whose actual time dominates the total.

3. The N+1: one query that became hundreds#

The most common ORM performance bug isn’t one slow query, it’s hundreds of fast ones. You load a list, then loop and load something for each item:

const orders = await db.select()...        // 1 query
for (const o of orders)
  o.customer = await loadCustomer(o.customerId)   // + N queries

Each query is 1ms and looks innocent in isolation; the trace shows 200 of them and the endpoint takes 400ms. You spot it instantly in the trace, a wall of identical SELECT … WHERE id = ? spans. The fix is to ask for everything at once instead of one-at-a-time:

• Join the related table in the original query, or
• Batch: collect the ids and do one WHERE customer_id IN (…), then stitch in memory.

One query of 200 rows beats 200 queries of one row, every time. The rule: never run a query inside a loop over database rows.

4. Fix it: the right index, not every index#

A missing index is fixed by adding one on the column(s) you filter and join by:

CREATE INDEX orders_customer_id_status_idx ON orders (customer_id, status);

Two things to get right:

• Composite order matters. An index on (customer_id, status) serves a filter on customer_id, or on both, but not one on status alone. Put the column you always filter by first.
• Don’t index everything. Every index is paid for on every write (insert/update must update the index too) and in storage. Index the columns your real queries filter and join on, the ones the traces show, not every column just in case. (More on the trade in Indexes, Sort Keys & Read Amplification.)

After adding the index, re-run EXPLAIN ANALYZE and confirm the plan flipped from Seq Scan to Index Scan and the time dropped. Measuring that the number actually moved is the step people skip, don’t.

The Cheat Sheet#

Related: How to Debug Performance, Indexes, Sort Keys & Read Amplification, Observability, Prove It Works