Veli Keçeci
Back to all posts
·6 min read

Reading PostgreSQL query plans: fixing slow queries with the right index

When a query gets slow, the first reflex is usually "let's add an index." Sometimes that works. Sometimes it only increases write cost and fixes nothing. The difference comes down to actually seeing how the database runs the query. PostgreSQL shows this openly through the query plan, as long as we know how to read it.

In this post I explain how to read EXPLAIN ANALYZE output, which index types make sense in which situations, and which pitfalls are common. The examples use PostgreSQL, but the habit of reading plans carries over to other engines.

Don't guess, ask for the plan

To see a plan, put EXPLAIN in front of the query. Plain EXPLAIN does not run the query; it shows the planner's estimate. EXPLAIN (ANALYZE, BUFFERS) actually runs the query and adds real timings and the number of pages read.

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

Note: because ANALYZE executes the query, when you use it with UPDATE or DELETE you should try it inside a transaction and roll it back. For read queries there is no such risk.

What to look for in a plan

A plan is a nested tree; the innermost nodes run first. Two groups of numbers matter on each node: the number of rows the planner estimated (rows) and the number of rows actually returned (actual ... rows). If the two differ a lot, the statistics may be stale. In that case, running ANALYZE table_name often changes the plan.

Node types give hints as well:

  • Seq Scan: The whole table is read. Normal for small tables, suspicious for a large table and a query that returns few rows.
  • Index Scan / Index Only Scan: An index is used. An Index Only Scan can answer without visiting the table at all, so it is usually the cheapest path.
  • Bitmap Heap Scan: For medium-sized result sets, a list of pages is built from the index and the table is read in bulk.
  • Sort and Hash: They spill to disk when memory runs out. A line such as Sort Method: external merge points to insufficient work_mem or a missing sorted index.

The Buffers lines are valuable too: a high shared read means data is being read from disk, a high shared hit means it comes from cache. When timings fluctuate, it is often because of exactly this difference.

Order matters in composite indexes

The query above filters by equality on customer_id, equality on status, and sorts by created_at. A single composite index can serve it:

CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at DESC);

The rule: columns with equality conditions come first, the sort or range column comes last. PostgreSQL then goes straight to the relevant rows and, since it finds the data already sorted, needs no separate Sort step; LIMIT 20 can stop after the first twenty entries.

Think of why order matters like this: an index on (customer_id, status, created_at) is like a phone book. You can look up by last name, but it does not help if you only know the first name. If your query searches only by created_at without status, this index will not work efficiently.

Partial and covering indexes

If you only query a portion of the data, indexing all of it is wasteful. A partial index contains only the rows matching a condition and is smaller, therefore faster:

CREATE INDEX idx_orders_open
    ON orders (customer_id, created_at DESC)
    WHERE status = 'open';

This is ideal for a small, frequently queried subset such as "open orders." A covering index, on the other hand, carries the extra columns a query needs via INCLUDE, which makes an Index Only Scan possible:

CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id, created_at DESC)
    INCLUDE (total);

Whether an Index Only Scan can avoid the table entirely depends on the table's visibility map being up to date, which regular VACUUM takes care of. On heavily updated tables you may not see the gain you expect.

Common traps that make an index unusable

If an index exists but the plan still shows a Seq Scan, the cause is usually one of these:

  1. A function on the column: WHERE lower(email) = '[email protected]' cannot use a plain index on email. The fix is either an expression index (CREATE INDEX ON users (lower(email))) or normalizing the data up front.
  2. Type mismatch: Comparing a text column with a different type can force an implicit cast and disable the index.
  3. Leading wildcard: LIKE '%abc' cannot use an ordinary B-tree index. Such searches need a different approach, such as the pg_trgm extension with a GIN index.
  4. Low selectivity: If the condition returns a large part of the table, a Seq Scan may genuinely be cheaper. The planner usually makes this call correctly.
  5. Stale statistics: After a large bulk load, the planner makes wrong estimates.

An index has a cost

Every index must be updated on every INSERT, UPDATE, and DELETE. Too many indexes slow down writes and consume disk and cache. So removing unused indexes matters as much as adding them. The pg_stat_user_indexes view shows how many times an index was scanned (idx_scan); indexes that stay at zero for a long time are candidates. Before dropping, check how long the statistics have been accumulating and whether rarely run reports depend on the index.

When creating an index on a live system, use CREATE INDEX CONCURRENTLY so rows are not locked. It takes longer and cannot run inside a transaction block, but it does not block writes.

A practical workflow

When you meet a slow query, this order saves time:

  1. Run the query with EXPLAIN (ANALYZE, BUFFERS) using real parameters and realistic data.
  2. Compare estimated and actual row counts; if they differ a lot, run ANALYZE first.
  3. Find the most expensive node: often a Seq Scan, a Sort, or an inner loop repeated many times.
  4. Design the smallest useful index for the query's conditions and ordering.
  5. Take the plan again and verify the improvement with numbers. If it did not improve, roll the index back.

Add a checkpoint on the application side too: enable the slow query log (log_min_duration_statement) or watch the most time-consuming queries with pg_stat_statements. That way optimization is directed at measured load, not perceived slowness.

Closing

The query plan is the database's answer to "how will I do this job"; the index is a shortcut you give it. Only the plan tells you whether the shortcut helps. Once reading plans becomes a habit, you add indexes with evidence instead of guesses, you notice and remove unnecessary ones, and you keep read speed up without giving up write performance. On your next slow query, I suggest running EXPLAIN (ANALYZE, BUFFERS) first.