Read a PostgreSQL EXPLAIN plan and find the slow step

You need a slow SELECT and a PostgreSQL session where you can run it. By the end you will know which node of the plan costs the most, and you can paste the plan into dbtempo for an index or rewrite proposal.

EXPLAIN
SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

The sample numbers below are made up for this guide. Yours will differ.

Limit  (cost=18250.31..18250.36 rows=20 width=16)
  ->  Sort  (cost=18250.31..18433.40 rows=73234 width=16)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..16112.00 rows=73234 width=16)
              Filter: (customer_id = 42)

A plan is a tree. The most indented node runs first and hands its rows to the node above it. Read from the bottom up:

• Seq Scan on orders reads the whole table and keeps rows where customer_id = 42. A Seq Scan with a Filter that keeps few rows is the usual sign that an index is missing.
• Sort orders those rows by created_at. With an index on (customer_id, created_at) the rows could come out already in order and this step would disappear.
• Limit keeps the first 20.

Each node shows cost=start..total, the planner's guess in its own arbitrary units, and rows, its guess at how many rows the node returns. Compare costs between nodes in the same plan; do not read them as milliseconds. Here the Seq Scan (16112) is most of the total (18433), so that is the node to fix first.