Read a query plan and find the slow part

A slow query rarely needs cleverness. It needs a look at what the database is actually doing. The query plan is the database's own description of how it will fetch the rows, and reading one is a skill worth an afternoon.

1. Get the plan

Put the keyword in front of the query:

Be careful with EXPLAIN ANALYZE on UPDATE, DELETE or INSERT: it performs them. Use BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;.

2. A worked example

Two tables, 5,000 customers and 100,000 orders, and a query for the top spenders in Germany among large orders.

import random
import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript(
    """
    CREATE TABLE customers (id INTEGER PRIMARY KEY, country TEXT, name TEXT);
    CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, created_at TEXT);
    """
)
random.seed(2)
conn.executemany("INSERT INTO customers VALUES (?, ?, ?)",
                 [(i, random.choice(["DE", "FR", "ES", "IT", "NL"]), f"customer {i}") for i in range(1, 5001)])
conn.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)",
                 [(i, random.randint(1, 5000), round(random.uniform(5, 500), 2), "2025-03-01") for i in range(1, 100001)])
conn.commit()

sql = """
SELECT c.name, SUM(o.total) AS spent
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.country = 'DE' AND o.total > 400
GROUP BY c.id
ORDER BY spent DESC
LIMIT 5
"""


def show(label):
    print(label)
    for _id, parent, _notused, detail in conn.execute("EXPLAIN QUERY PLAN " + sql):
        print(f"  {_id:>2} (parent {parent:>2})  {detail}")


show("before")
conn.execute("CREATE INDEX idx_orders_customer_total ON orders (customer_id, total)")
conn.execute("ANALYZE")
show("after the index")

This prints:

before
   9 (parent  0)  SCAN o
  14 (parent  0)  SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
  19 (parent  0)  USE TEMP B-TREE FOR GROUP BY
  64 (parent  0)  USE TEMP B-TREE FOR ORDER BY
after the index
   9 (parent  0)  SCAN c
  13 (parent  0)  SEARCH o USING COVERING INDEX idx_orders_customer_total (customer_id=? AND total>?)
  53 (parent  0)  USE TEMP B-TREE FOR ORDER BY

3. Read it line by line

Plans are read as steps, and the shape matters more than the numbers.

Before. SCAN o means every one of the 100,000 orders is read. For each order that passes the total > 400 test, SEARCH c USING INTEGER PRIMARY KEY is a cheap lookup of its customer. Then two temporary B-trees are built, one to group and one to sort the result. The expensive line is the first.

After. The index on (customer_id, total) changes the strategy. The database now scans the small customers table (5,000 rows), keeps the German ones, and for each customer searches the index for orders with total > ?. The sort for the grouping has gone, because customers are now visited one at a time and each customer's orders arrive together. Only the final sort remains, over one row per customer.

Notice that the planner reversed the join order. You wrote FROM orders JOIN customers, but the plan began with customers. The order you write a join in is not the order it runs.

4. A short vocabulary

5. In PostgreSQL, compare estimates with reality

EXPLAIN (ANALYZE, BUFFERS) adds what actually happened to what the planner expected. For each node look at estimated rows against actual rows. A big gap (the planner expected 10 and got 100,000) is the classic cause of a bad plan: its statistics are stale or it cannot estimate the filter. Running ANALYZE on the table refreshes the statistics, and is the first thing to try.

6. Decide what to do

1. A scan on a large table with a selective filter: consider an index on the filtered column, then confirm in the plan that it is used.
2. An index exists but is not used: the usual causes are a function around the column, a leading wildcard or a mismatched type. The indexes guide lists them.
3. A sort on a big result: an index that matches the ORDER BY can remove it.
4. Plan is fine but the query is still slow: the work is in returning too many rows, or the machine is short of memory. Fetch fewer columns and rows.
5. Nothing looks wrong: stop. Not every slow-feeling query has a fix, and an unnecessary index only slows writes.

Change one thing at a time, read the plan again, and keep the version that is simpler.