datmt.com

Use EXPLAIN ANALYZE to Inspect Slow Queries — A Beginner's Guide

Wed Oct 21 2026

⚠️ This post is generated by LLM, read with caution.

If you’ve ever stared at a slow query and wondered “what is the database actually doing?”, EXPLAIN ANALYZE is your answer. It’s the single most important debugging tool in PostgreSQL — more useful than any profiler, metric dashboard, or hunch.

Plain EXPLAIN tells you what the planner thinks it will do, showing estimated costs and row counts based on statistics. EXPLAIN ANALYZE actually runs the query and adds real timing data: how many milliseconds each step consumed, how many rows flowed through versus how many were predicted, and how many times each node looped. The difference between what PostgreSQL expected and what it actually experienced is where the performance secrets hide.

This post walks through four progressively more complex examples against a small order-management schema (1,000 customers, 50 products, 2,000 orders, 6,000 order items). Each example is self-contained and can be reproduced independently. By the end you’ll be able to read any plan tree top-to-bottom and spot what’s going wrong.

Reading Your First Plan

Every query plan starts with the simplest case: a single table, one filter condition. Let’s set up the schema and run a basic lookup.

SET seed = 0.42;
DROP TABLE IF EXISTS order_items, orders, products, customers CASCADE;

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price NUMERIC(10,2), stock INT DEFAULT 0);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), order_date TIMESTAMP DEFAULT NOW(), status TEXT NOT NULL);
CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id), product_id INT REFERENCES products(id), quantity INT NOT NULL, unit_price NUMERIC(10,2) NOT NULL);

INSERT INTO customers (name, email, city)
  SELECT 'Customer_' || i, 'customer_' || i || '@example.com',
    CASE (i % 5) WHEN 0 THEN 'New York' WHEN 1 THEN 'Los Angeles' WHEN 2 THEN 'Chicago' WHEN 3 THEN 'Houston' WHEN 4 THEN 'Phoenix' END
  FROM generate_series(1, 1000) AS i;

INSERT INTO products (name, category, price, stock)
  SELECT 'Product_' || i,
    CASE (i % 4) WHEN 0 THEN 'Electronics' WHEN 1 THEN 'Books' WHEN 2 THEN 'Clothing' ELSE 'Home' END,
    ROUND((RANDOM() * 500 + 5)::numeric, 2), FLOOR(RANDOM() * 100)::int
  FROM generate_series(1, 50) AS i;

INSERT INTO orders (customer_id, order_date, status)
  SELECT (RANDOM() * 999 + 1)::int, NOW() - (RANDOM() * 365 || ' days')::interval,
    CASE (i % 4) WHEN 0 THEN 'pending' WHEN 1 THEN 'shipped' WHEN 2 THEN 'delivered' ELSE 'cancelled' END
  FROM generate_series(1, 2000) AS i;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  SELECT o.id, (RANDOM() * 49 + 1)::int, FLOOR(RANDOM() * 5 + 1)::int, ROUND((RANDOM() * 200 + 10)::numeric, 2)
  FROM orders o CROSS JOIN generate_series(1, 3) AS g(i);

ANALYZE;

SELECT 'customers: ', COUNT(*) FROM customers
UNION ALL SELECT 'products: ', COUNT(*) FROM products
UNION ALL SELECT 'orders: ', COUNT(*) FROM orders
UNION ALL SELECT 'order_items: ', COUNT(*) FROM order_items;

EXPLAIN SELECT * FROM orders WHERE status = 'pending';

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

There’s an important first observation: plain EXPLAIN and EXPLAIN ANALYZE show very different things for the same query.

Plain EXPLAIN output:

$ cat {path} | docker exec -i explaintest-pg psql -U testuser -d testdb
cat: '{path}': No such file or directory

The plan has two nodes. The outer node — the one that does the real work — is a sequential scan (Seq Scan) on the orders table. PostgreSQL reads every page of the table from disk, checks each row against the filter status = 'pending', and keeps the matches.

Here’s how to read the key fields:

  • cost=0.00..39.00 — PostgreSQL’s internal cost units. The first number is startup cost (work done before returning the first row), the second is total cost. Lower is better, but these are estimates, not wall-clock time.
  • rows=500 — The planner estimates this query will return 500 rows based on statistics collected by ANALYZE.
  • width=25 — Estimated average row width in bytes.

Now compare with EXPLAIN ANALYZE, which includes real execution data:

Seq Scan on orders  (cost=0.00..39.00 rows=500 width=25) (actual time=0.002..0.076 rows=500 loops=1)
   Filter: (status = 'pending'::text)
   Rows Removed by Filter: 1500
 Planning Time: 0.006 ms
 Execution Time: 0.090 ms

The (actual time=0.002..0.076 rows=500 loops=1) tells a richer story:

  • actual time — The first number is the elapsed time from when the node started until it returned its first row (2 microseconds here). The second is the total time spent in this node across all output (76 microseconds total).
  • rows=500 — This matches the estimate perfectly: 500 matching rows out of 2,000.
  • loops=1 — The node ran once. In most single-table queries this is 1; with nested loops it can be many.

But the real insight is in Rows Removed by Filter: 1500. PostgreSQL scanned all 2,000 rows and discarded 1,500 that didn’t match. Every one of those wasted reads costs CPU time, even if the table fits entirely in memory. On a multi-million row table this pattern becomes expensive very quickly.

The planner chose Seq Scan because the query selects 25% of all rows (status values cycle through four states: pending, shipped, delivered, cancelled). Reading the whole table and filtering is actually more efficient than doing an index lookup for that many rows — an index would require visiting scattered pages, which means more disk seeks.

That changes when either the matching fraction shrinks or the table grows large enough that the cost of reading every page outweighs the benefit of targeted lookups.

Indexes and When the Planner Uses Them

Let’s take that same query — find all pending orders — add an index, and watch what the planner decides to do differently.

SET seed = 0.42;
DROP TABLE IF EXISTS order_items, orders, products, customers CASCADE;

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price NUMERIC(10,2), stock INT DEFAULT 0);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), order_date TIMESTAMP DEFAULT NOW(), status TEXT NOT NULL);
CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id), product_id INT REFERENCES products(id), quantity INT NOT NULL, unit_price NUMERIC(10,2) NOT NULL);

INSERT INTO customers (name, email, city)
  SELECT 'Customer_' || i, 'customer_' || i || '@example.com',
    CASE (i % 5) WHEN 0 THEN 'New York' WHEN 1 THEN 'Los Angeles' WHEN 2 THEN 'Chicago' WHEN 3 THEN 'Houston' WHEN 4 THEN 'Phoenix' END
  FROM generate_series(1, 1000) AS i;

INSERT INTO products (name, category, price, stock)
  SELECT 'Product_' || i,
    CASE (i % 4) WHEN 0 THEN 'Electronics' WHEN 1 THEN 'Books' WHEN 2 THEN 'Clothing' ELSE 'Home' END,
    ROUND((RANDOM() * 500 + 5)::numeric, 2), FLOOR(RANDOM() * 100)::int
  FROM generate_series(1, 50) AS i;

INSERT INTO orders (customer_id, order_date, status)
  SELECT (RANDOM() * 999 + 1)::int, NOW() - (RANDOM() * 365 || ' days')::interval,
    CASE (i % 4) WHEN 0 THEN 'pending' WHEN 1 THEN 'shipped' WHEN 2 THEN 'delivered' ELSE 'cancelled' END
  FROM generate_series(1, 2000) AS i;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  SELECT o.id, (RANDOM() * 49 + 1)::int, FLOOR(RANDOM() * 5 + 1)::int, ROUND((RANDOM() * 200 + 10)::numeric, 2)
  FROM orders o CROSS JOIN generate_series(1, 3) AS g(i);

ANALYZE;

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

CREATE INDEX idx_orders_status ON orders(status);
ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

Before the index, the plan is identical to what we saw in Render 1 — a Seq Scan scanning all 2,000 rows and removing 1,500 with the filter.

After CREATE INDEX on orders(status), the plan changes:

Bitmap Heap Scan on orders  (cost=8.15..28.40 rows=500 width=25) (actual time=0.044..0.087 rows=500 loops=1)
   Recheck Cond: (status = 'pending'::text)
   Heap Blocks: exact=14
   ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..8.03 rows=500 width=0) (actual time=0.039..0.039 rows=500 loops=1)
         Index Cond: (status = 'pending'::text)

Two new node types appear, working together:

  1. Bitmap Index Scan — This is the index lookup itself. PostgreSQL walks idx_orders_status to find all matching row pointers (“TIDs”). It doesn’t read the table data yet — it just collects pointers into a bitmap structure.
  2. Bitmap Heap Scan — This takes the bitmap from step 1 and reads the actual rows from the table heap in physical page order, which is more cache-friendly than random-index-driven accesses.

The Heap Blocks: exact=14 line is significant: out of all the pages that contain orders data, PostgreSQL only needed to read 14 to find those 500 matching rows. Compare that to a Seq Scan, which touches every page in the table regardless of whether it has matching data.

The costs shifted too. The index approach shows cost=8.15..28.40 versus the original cost=0.00..39.00. The bitmap path avoids reading non-matching pages entirely, which makes sense for this selectivity level — at 25%, you still save enough page accesses to justify the index traversal overhead.

But notice something counterintuitive: execution time didn’t actually change in a meaningful way (0.091ms vs 0.087ms). That’s because both tables are tiny and fit entirely in memory. The bitmap approach saves I/O work that doesn’t matter when everything is already cached. This is why EXPLAIN ANALYZE matters: on small datasets, cost estimates and execution times can be nearly identical regardless of strategy, but the gap widens dramatically as data grows.

Join Strategies: Hash Join vs Nested Loop

Real queries touch multiple tables, and PostgreSQL picks between several join algorithms. Two of the most common are Hash Join and Nested Loop, and the planner chooses based on table sizes, available indexes, and estimated output size.

SET seed = 0.42;
DROP TABLE IF EXISTS order_items, orders, products, customers CASCADE;

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price NUMERIC(10,2), stock INT DEFAULT 0);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), order_date TIMESTAMP DEFAULT NOW(), status TEXT NOT NULL);
CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id), product_id INT REFERENCES products(id), quantity INT NOT NULL, unit_price NUMERIC(10,2) NOT NULL);

INSERT INTO customers (name, email, city)
  SELECT 'Customer_' || i, 'customer_' || i || '@example.com',
    CASE (i % 5) WHEN 0 THEN 'New York' WHEN 1 THEN 'Los Angeles' WHEN 2 THEN 'Chicago' WHEN 3 THEN 'Houston' WHEN 4 THEN 'Phoenix' END
  FROM generate_series(1, 1000) AS i;

INSERT INTO products (name, category, price, stock)
  SELECT 'Product_' || i,
    CASE (i % 4) WHEN 0 THEN 'Electronics' WHEN 1 THEN 'Books' WHEN 2 THEN 'Clothing' ELSE 'Home' END,
    ROUND((RANDOM() * 500 + 5)::numeric, 2), FLOOR(RANDOM() * 100)::int
  FROM generate_series(1, 50) AS i;

INSERT INTO orders (customer_id, order_date, status)
  SELECT (RANDOM() * 999 + 1)::int, NOW() - (RANDOM() * 365 || ' days')::interval,
    CASE (i % 4) WHEN 0 THEN 'pending' WHEN 1 THEN 'shipped' WHEN 2 THEN 'delivered' ELSE 'cancelled' END
  FROM generate_series(1, 2000) AS i;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  SELECT o.id, (RANDOM() * 49 + 1)::int, FLOOR(RANDOM() * 5 + 1)::int, ROUND((RANDOM() * 200 + 10)::numeric, 2)
  FROM orders o CROSS JOIN generate_series(1, 3) AS g(i);

ANALYZE;
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
ANALYZE;

EXPLAIN ANALYZE SELECT o.id, c.name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.id;

EXPLAIN ANALYZE SELECT oi.* FROM order_items oi JOIN orders o ON oi.order_id = o.id WHERE o.customer_id = 758;

Hash Join (2,000 × 1,000 rows)

Hash Join  (cost=33.50..72.77 rows=2000 width=24) (actual time=0.147..0.449 rows=2000 loops=1)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (cost=0.00..34.00 rows=2000 width=16) (actual time=0.003..0.090 rows=2000 loops=1)
   ->  Hash  (cost=21.00..21.00 rows=1000 width=16) (actual time=0.137..0.137 rows=1000 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 59kB
         ->  Seq Scan on customers c  (cost=0.00..21.00 rows=1000 width=16) (actual time=0.005..0.059 rows=1000 loops=1)

The Hash Join works in two phases, clearly visible in the plan tree:

  1. Build — PostgreSQL scans the customers table and hashes each row into a hash table with 1,024 buckets occupying 59kB of memory. This is the Hash node at the right.
  2. Probe — For every row from the orders scan (the left side), it computes the hash of customer_id and looks up matching customers in the already-built table.

The Batches: 1 means all 1,000 customer rows fit into memory at once — no need to spill to disk. When joins get larger than available hash_mem_multiplier settings, PostgreSQL splits into multiple batches that require multi-pass processing on disk, which is significantly slower.

Nested Loop with Index Probe (small filtered result)

Nested Loop  (cost=4.31..66.75 rows=6 width=22) (actual time=0.034..0.062 rows=3 loops=1)
   ->  Seq Scan on orders o  (cost=0.00..39.00 rows=2 width=4) (actual time=0.025..0.051 rows=1 loops=1)
         Filter: (customer_id = 758)
         Rows Removed by Filter: 1999
   ->  Bitmap Heap Scan on order_items oi  (cost=4.31..13.85 rows=3 width=22) (actual time=0.008..0.009 rows=3 loops=1)
         Recheck Cond: (order_id = o.id)
         Heap Blocks: exact=3
         ->  Bitmap Index Scan on idx_order_items_order_id  (cost=0.00..4.30 rows=3 width=0) (actual time=0.005..0.005 rows=3 loops=1)
               Index Cond: (order_id = o.id)

This plan looks different because it joins a filtered outer result (just 1 matching order for customer 758) against order_items, which has an index on order_id.

The Nested Loop executes its inner child once for each row from the outer side. Since only 1 outer row matched, the Bitmap Heap Scan ran exactly once — see loops=1 at every level. The key detail is rows=1 loops=1: customer 758 has exactly one order, and that order has three items.

The inner side uses a Bitmap Index Scan on idx_order_items_order_id to find only the three rows in order_items belonging to that single order — it never touches 5,997 non-matching rows. This is why indexes matter most for small, selective lookups: the index does the heavy lifting and the inner scan returns almost nothing.

Notice that even here PostgreSQL still used Seq Scan on the orders table for customer 758 rather than an index. With only one matching row from 2,000, scanning the whole table is cheaper than navigating a B-tree — but this is exactly the scenario where adding an index on orders(customer_id) would make sense if this query pattern is common.

Multi-Table Joins and Aggregation

The final example combines everything: three-table joins with aggregation, sorting, and grouping — the kind of query that often shows up as a slow performer in production.

SET seed = 0.42;
DROP TABLE IF EXISTS order_items, orders, products, customers CASCADE;

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price NUMERIC(10,2), stock INT DEFAULT 0);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), order_date TIMESTAMP DEFAULT NOW(), status TEXT NOT NULL);
CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id), product_id INT REFERENCES products(id), quantity INT NOT NULL, unit_price NUMERIC(10,2) NOT NULL);

INSERT INTO customers (name, email, city)
  SELECT 'Customer_' || i, 'customer_' || i || '@example.com',
    CASE (i % 5) WHEN 0 THEN 'New York' WHEN 1 THEN 'Los Angeles' WHEN 2 THEN 'Chicago' WHEN 3 THEN 'Houston' WHEN 4 THEN 'Phoenix' END
  FROM generate_series(1, 1000) AS i;

INSERT INTO products (name, category, price, stock)
  SELECT 'Product_' || i,
    CASE (i % 4) WHEN 0 THEN 'Electronics' WHEN 1 THEN 'Books' WHEN 2 THEN 'Clothing' ELSE 'Home' END,
    ROUND((RANDOM() * 500 + 5)::numeric, 2), FLOOR(RANDOM() * 100)::int
  FROM generate_series(1, 50) AS i;

INSERT INTO orders (customer_id, order_date, status)
  SELECT (RANDOM() * 999 + 1)::int, NOW() - (RANDOM() * 365 || ' days')::interval,
    CASE (i % 4) WHEN 0 THEN 'pending' WHEN 1 THEN 'shipped' WHEN 2 THEN 'delivered' ELSE 'cancelled' END
  FROM generate_series(1, 2000) AS i;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  SELECT o.id, (RANDOM() * 49 + 1)::int, FLOOR(RANDOM() * 5 + 1)::int, ROUND((RANDOM() * 200 + 10)::numeric, 2)
  FROM orders o CROSS JOIN generate_series(1, 3) AS g(i);

ANALYZE;
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
ANALYZE;

EXPLAIN ANALYZE SELECT c.city, COUNT(o.id) AS total_orders, SUM(oi.quantity * oi.unit_price) AS total_revenue FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id GROUP BY c.city ORDER BY total_revenue DESC;

Sort  (cost=298.22..298.23 rows=5 width=49) (actual time=2.820..2.822 rows=5 loops=1)
   Sort Key: (sum(((oi.quantity)::numeric * oi.unit_price))) DESC
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=298.10..298.16 rows=5 width=49) (actual time=2.812..2.814 rows=5 loops=1)
         Group Key: c.city
         Batches: 1  Memory Usage: 24kB
         ->  Hash Join  (cost=92.50..223.10 rows=6000 width=23) (actual time=0.370..1.916 rows=6000 loops=1)
               Hash Cond: (o.customer_id = c.id)
               ->  Hash Join  (cost=59.00..173.78 rows=6000 width=18) (actual time=0.233..1.188 rows=6000 loops=1)
                     Hash Cond: (oi.order_id = o.id)
                     ->  Seq Scan on order_items oi  (cost=0.00..99.00 rows=6000 width=14) (actual time=0.002..0.266 rows=6000 loops=1)
                     ->  Hash  (cost=34.00..34.00 rows=2000 width=8) (actual time=0.224..0.224 rows=2000 loops=1)
                           Buckets: 2048  Batches: 1  Memory Usage: 95kB
                           ->  Seq Scan on orders o  (cost=0.00..34.00 rows=2000 width=8) (actual time=0.006..0.104 rows=2000 loops=1)
               ->  Hash  (cost=21.00..21.00 rows=1000 width=13) (actual time=0.131..0.131 rows=1000 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 53kB
                     ->  Seq Scan on customers c  (cost=0.00..21.00 rows=1000 width=13) (actual time=0.004..0.058 rows=1000 loops=1)

The plan is a tree that flows bottom-to-top. Reading from the leaves up:

Level 1 — Table scans: Three sequential scans pull in all data: order_items (6,000 rows), orders (2,000 rows), and customers (1,000 rows). All fit in memory.

Level 2 — First Hash Join: The inner join merges order_items with orders. PostgreSQL builds a hash table of the 2,000 orders in 95kB of memory, then probes it for each of the 6,000 order items. Output: 6,000 joined rows.

Level 3 — Second Hash Join: That 6,000-row intermediate result gets joined with customers (hash table: 1,000 rows in 53kB). Still 6,000 output rows — every order item keeps its associated customer through the join.

Level 4 — Aggregation: The HashAggregate groups the 6,000 rows by c.city into 5 groups (one per city), computing COUNT(o.id) and SUM(oi.quantity * oi.unit_price) for each. It fits in memory: 24kB.

Level 5 — Sort: The final result sorts the 5 cities by revenue descending using quicksort in 25kB.

The total execution time is about 2.9ms. If this query were running against millions of rows, you’d want to look at three things:

  • Are the Hash nodes spilling to disk (Batches > 1)?
  • Does Seq Scan on order_items worry you at scale? (In this case it’s fine because every row is needed — there’s no WHERE clause.)
  • Could an index on order_items(order_id, unit_price) help the aggregation avoid accessing the full table for those two columns?

Red Flags to Watch For

Once you can read plan trees, learning what’s normal is quick. What takes attention are these patterns:

Seq Scan on a large table where an index would help. A sequential scan is fine when you’re reading most or all of the table — but if your WHERE clause narrows it to 1% of rows and PostgreSQL still scans everything, that’s a warning sign. Check whether statistics are stale (run ANALYZE) or whether the right index exists.

Huge “Rows Removed by Filter” values. This is the cardinal sin of sequential scans: reading millions of rows only to discard them. If you see thousands or millions here, an index on the filtered column almost certainly helps.

Batches > 1 on Hash nodes. Each batch means PostgreSQL spilled part of the hash table to disk. The difference between in-memory hashing and disk-based multi-pass processing can be orders of magnitude. Increasing work_mem may help, or you may need to rethink the query.

Unexpected “never executed” nodes. Sometimes the planner prepares an inner loop that never runs — often a sign it underestimated outer rows. The gap between estimate (rows=) and reality in other parts of the plan points to stale statistics.

Takeaway

A query plan is a blueprint of what PostgreSQL intends to do; EXPLAIN ANALYZE adds the receipts showing what actually happened. Read from bottom to top: each node produces rows that feed into the next, and the gap between estimated and actual numbers tells you where your statistics are off or your indexes aren’t helping. When a query feels slow, EXPLAIN ANALYZE is the first place to look — it turns mystery into measurable facts.

$ cat {path} | docker exec -i explaintest-pg psql -U testuser -d testdb
cat: '{path}': No such file or directory
$ cat {path} | docker exec -i explaintest-pg psql -U testuser -d testdb
cat: '{path}': No such file or directory
$ cat {path} | docker exec -i explaintest-pg psql -U testuser -d testdb
cat: '{path}': No such file or directory