Why is my PostgreSQL query slow? Reading EXPLAIN ANALYZE and adding the right indexes

A page that loaded instantly in development now takes seconds in production. The API logs point at one query, and the usual reaction is to add an index on whatever column appears in the WHERE clause and hope. Sometimes that works. Often the index is ignored, or it helps one query while slowing every write to the table.

Postgres tells you exactly why a query is slow, if you ask it. This article covers how to find the queries worth fixing, how to read EXPLAIN ANALYZE output without getting lost, and how to pick an index that matches the access pattern rather than just the column names. The same process works for any Postgres deployment, self-hosted or managed.

Find the queries that actually matter

Start with data, not guesses. The pg_stat_statements extension records normalised queries with call counts and timings. It ships with Postgres and is available on every major managed provider, though it usually needs enabling. Sort by total time, not mean time: a 5 ms query called a million times a day matters more than a 2 second report someone runs weekly.

-- Requires shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  calls,
  round(total_exec_time::numeric, 0)  AS total_ms,
  round(mean_exec_time::numeric, 2)   AS mean_ms,
  rows,
  left(query, 120)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Reset the statistics with pg_stat_statements_reset() after a deploy or index change, then compare the same list a day later. That turns it feels faster into a before-and-after view of where database time is going.

For queries that are slow only occasionally, enable auto_explain with a log_min_duration threshold. It writes the plan of any query over the threshold to the logs, which captures the plan Postgres actually chose at that moment, with those parameters.

Reading an EXPLAIN ANALYZE plan

Run the query with EXPLAIN (ANALYZE, BUFFERS). ANALYZE executes it and reports real row counts and timings; BUFFERS shows how many pages came from cache versus disk (Postgres 18 includes buffer counts by default with ANALYZE). Because the query really runs, wrap data-modifying statements in a transaction you roll back.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

-- Illustrative, abridged output:
-- Limit  (actual rows=20 loops=1)
--   ->  Sort  (actual rows=20 loops=1)
--         Sort Key: created_at DESC
--         Sort Method: top-N heapsort
--         ->  Seq Scan on orders  (rows=180 width=24) (actual rows=211 loops=1)
--               Filter: ((customer_id = 4821) AND (status = 'paid'::text))
--               Rows Removed by Filter: 2399789
--               Buffers: shared hit=1204 read=27311

Plans are trees; read them from the innermost, most indented node outward. For each node, look at three things:

• Node type. A Seq Scan on a large table with a selective filter is the classic smell. Index Scan, Index Only Scan and Bitmap Heap Scan mean an index is in play.
• Rows Removed by Filter. If Postgres reads millions of rows to return a few hundred, the table lacks an index matching the filter. That is exactly what the example shows.
• Estimated versus actual rows. Compare rows= in the estimate with actual rows=. Differences of orders of magnitude mean the planner is working from bad statistics and may pick the wrong join strategy or scan type.

Also watch for loops. Timings and row counts on an inner node are per loop, so a nested loop that runs an inner index scan many thousands of times can be the real cost even when each iteration looks cheap. And Buffers: read versus hit tells you whether you are paying for disk I/O or the data was already in memory.

Choosing the right index

An index should mirror the query: equality filters first, then range filters or sort order. For the query above, an index on customer_id alone helps, but Postgres would still fetch every order for that customer and sort them. A composite index lets it jump straight to the matching rows already in order and stop after 20.

-- Equality columns first, then the sort column.
-- CONCURRENTLY avoids blocking writes (cannot run inside a transaction block).
CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC);

-- Partial index: only index the rows a hot query touches.
CREATE INDEX CONCURRENTLY orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

-- Covering index: INCLUDE extra columns to allow an Index Only Scan.
CREATE INDEX CONCURRENTLY orders_customer_created_cover_idx
  ON orders (customer_id, created_at DESC)
  INCLUDE (total, status);

-- Expression index: the query must use the same expression.
CREATE INDEX CONCURRENTLY users_email_lower_idx ON users (lower(email));

• Column order matters. An index on (a, b) serves filters on a, or on a and b, but generally not on b alone.
• Partial indexes are small and fast when queries always include the same predicate, such as unprocessed jobs or active subscriptions.
• Covering indexes with INCLUDE let Postgres answer from the index alone, provided the table is vacuumed often enough for the visibility map to be current.
• Functions hide columns. WHERE lower(email) = $1 cannot use a plain index on email; it needs the expression index.
• Foreign keys are not indexed automatically. Index the referencing column if you join or delete through it.

When the index exists but is not used

The planner chooses by estimated cost. If it expects a filter to match a large fraction of the table, a sequential scan genuinely is cheaper, and that is correct behaviour. When it is wrong, the cause is usually one of these:

1. Stale statistics after a bulk load or large delete. Run ANALYZE orders; and compare plans. For skewed or correlated columns, raise the statistics target or create extended statistics with CREATE STATISTICS.
2. Type mismatches, such as comparing a bigint column to a numeric parameter, or implicit casts introduced by the ORM.
3. Leading wildcards like LIKE '%term', which a B-tree cannot serve. Use a trigram (pg_trgm) GIN index or full-text search.
4. OR conditions across different columns, which may be faster rewritten as a UNION ALL of two indexed queries.
5. Very small test tables, where scanning everything really is cheapest. Always check plans against production-sized data.

Beyond indexing, check the query itself. OFFSET pagination reads and throws away every skipped row, so deep pages get slower; keyset pagination (WHERE created_at < $last ORDER BY created_at DESC LIMIT 20) uses the index directly. SELECT * on wide rows defeats index-only scans. And N+1 query patterns from an ORM will not show up as one slow query but as thousands of fast ones in pg_stat_statements.

Key takeaways

• Use pg_stat_statements and sort by total time to find queries worth fixing.
• Read plans inside out; look at node type, rows removed by filter, and estimated versus actual rows.
• Design composite indexes as equality columns first, then range or sort columns, and use partial, covering and expression indexes where they fit.
• Create indexes CONCURRENTLY in production and clean up invalid ones.
• If an index is ignored, check statistics, types and query shape before forcing anything.

If your database is becoming the bottleneck, our data engineering team can profile workloads, tune schemas and plan for growth. Get in touch.