Posted inDatabase

Optimizing PostgreSQL Queries with EXPLAIN ANALYZE: Analyzing and Improving Query Performance

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Do It in 5 Minutes: Run EXPLAIN ANALYZE for the First Time

I remember my first week at the company when a senior tossed me a ticket: “This query takes 8 seconds, fix it.” I stared at the SQL with no idea where to start. That’s when I discovered EXPLAIN ANALYZE — the simplest yet most powerful tool for debugging PostgreSQL queries.

Try it right now by running this on any query you have:

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

PostgreSQL will return a wall of text that looks like this:

Seq Scan on orders  (cost=0.00..1845.00 rows=12 width=156) (actual time=0.043..18.234 rows=12 loops=1)
  Filter: ((user_id = 42) AND ((status)::text = 'pending'::text))
  Rows Removed by Filter: 89988
Planning Time: 0.215 ms
Execution Time: 18.251 ms

Doesn’t make sense yet? That’s fine. Read on — I’ll break down each part.

Reading the Execution Plan: Understanding What PostgreSQL Is Doing

Structure of an Execution Plan

Execution plans are read from the inside out, bottom to top. The deepest node runs first. Each line follows this format:

[Scan type]  (cost=X..Y rows=N width=W) (actual time=A..B rows=R loops=L)
  • cost=X..Y: Estimated cost — X is the startup cost, Y is the total cost (relative units, not milliseconds)
  • rows=N: The number of rows PostgreSQL predicts will be returned
  • actual time=A..B: Actual time in milliseconds — A is the time to the first row, B is the total
  • rows=R: The actual number of rows returned
  • loops=L: How many times this node executed — multiply by actual time for the true total

Scan Types — Which Are Fast, Which Are Slow

The first thing I check when opening a plan is the scan type. Four types to know:

  • Seq Scan: Scans the entire table from start to finish. Seeing this on a million-row table should raise a red flag immediately.
  • Index Scan: Uses an index to find rows. Much faster on large tables.
  • Index Only Scan: The best — retrieves data directly from the index without reading the heap.
  • Bitmap Heap Scan: Combines multiple indexes, effective when WHERE has multiple conditions.

Signs Your Query Has a Problem

Look at these two numbers and compare them:

-- Estimated: rows=1, Actual: rows=50000
-- → Stale statistics, run ANALYZE

-- Estimated: rows=50000, Actual: rows=1
-- → Planner chose the wrong plan, query is scanning unnecessarily

When these two numbers differ by more than 10x, the planner is choosing the wrong execution plan — usually due to stale statistics. Running ANALYZE orders; is the first step to fix it.

Practice: Debugging a Slow Query End-to-End

Setting Up a Test Environment

-- Create a test table with 1 million rows
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,
    product_id INT,
    status VARCHAR(20),
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders (user_id, product_id, status, total)
SELECT
    (random() * 10000)::INT,
    (random() * 1000)::INT,
    CASE (random() * 3)::INT
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'completed'
        ELSE 'cancelled'
    END,
    (random() * 1000)::DECIMAL(10,2)
FROM generate_series(1, 1000000);

Slow Query — No Index

EXPLAIN ANALYZE
SELECT id, total, status
FROM orders
WHERE user_id = 5000
AND created_at > '2024-01-01';

Result:

Seq Scan on orders  (cost=0.00..23334.00 rows=5 width=28) (actual time=2.341..145.672 rows=48 loops=1)
  Filter: ((user_id = 5000) AND (created_at > '2024-01-01'::timestamp))
  Rows Removed by Filter: 999952
Execution Time: 145.891 ms

Scanning 1 million rows to get 48 — completely wasteful. Add an index:

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- Run again
EXPLAIN ANALYZE
SELECT id, total, status
FROM orders
WHERE user_id = 5000
AND created_at > '2024-01-01';

Result after adding the index:

Index Scan using idx_orders_user_created on orders
  (cost=0.42..18.63 rows=48 width=28) (actual time=0.052..0.312 rows=48 loops=1)
  Index Cond: ((user_id = 5000) AND (created_at > '2024-01-01'::timestamp))
Execution Time: 0.387 ms

From 145ms down to 0.4ms. That’s the power of the right index.

Advanced: EXPLAIN with Useful Options

EXPLAIN (ANALYZE, BUFFERS) — Viewing Real I/O

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10;

The BUFFERS option shows how many blocks were read from disk (read) vs from cache (hit). A high read count means your data isn’t cached — this is an I/O bottleneck, not a CPU issue.

Analyzing JOINs: Hash Join vs Nested Loop vs Merge Join

PostgreSQL has three JOIN strategies. The planner chooses automatically based on table size, available indexes, and statistics:

  • Hash Join: Builds a hash table from the smaller table, then probes it with the larger table. Effective when there’s no index on the join column.
  • Nested Loop: For each row in the outer table, it searches the inner table. Fast when the inner table is small or has a good index.
  • Merge Join: Requires both tables to be sorted by the join key. Most efficient when both already have sorted indexes.
-- Disable a strategy to compare performance
SET enable_hashjoin = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_hashjoin = on;

pgBadger and auto_explain: Finding Slow Queries Automatically

Manually running EXPLAIN on every query is time-consuming. Enable auto_explain to have PostgreSQL automatically log slow queries:

# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000  # Log queries running > 1 second
auto_explain.log_analyze = true
auto_explain.log_buffers = true

Then use pgBadger to parse the log and generate an HTML report:

pgbadger /var/log/postgresql/postgresql-14-main.log -o report.html

Practical Tips from Real-World Experience

1. Always Check Estimated Rows vs Actual Rows

If the planner’s estimates are way off, run VACUUM ANALYZE to refresh statistics. Table with constant INSERT/DELETE/UPDATE activity? Increase the autovacuum frequency:

ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.01,  -- ANALYZE when 1% of data changes
    autovacuum_vacuum_scale_factor = 0.05
);

2. Indexes Don’t Always Help

PostgreSQL automatically chooses a Seq Scan when it estimates the query will return more than ~10-15% of all rows. In that case, a full table scan is genuinely faster than jumping through an index. Don’t try to force it — the planner is almost always right in this scenario.

3. Composite Indexes — Column Order Matters

-- This index is effective for: WHERE user_id = ? AND status = ?
-- Or just: WHERE user_id = ?
CREATE INDEX idx_user_status ON orders(user_id, status);

-- But NOT effective for: WHERE status = ? (no user_id prefix)
-- Need a separate index: CREATE INDEX idx_status ON orders(status);

4. Covering Indexes to Achieve Index Only Scans

Query only SELECTs specific columns? Create a covering index so PostgreSQL doesn’t need to read the heap:

-- Frequently running query:
SELECT id, total FROM orders WHERE user_id = 42 AND status = 'pending';

-- Covering index (INCLUDE the SELECTed columns):
CREATE INDEX idx_orders_covering
ON orders(user_id, status)
INCLUDE (id, total);

5. Use explain.depesz.com to Visualize

Copy the output of EXPLAIN ANALYZE and paste it into explain.depesz.com or explain.dalibo.com to see a visualization — especially helpful for complex plans with many nodes.

6. Don’t Optimize Prematurely — Profile First

The most common mistake I see: adding indexes randomly without measuring first. Every index costs storage and slows down INSERT/UPDATE/DELETE. Only add one when EXPLAIN ANALYZE confirms it’s the real bottleneck — and measure again afterward to confirm the improvement.

Share:
Tags: