Learn SQL with PostgreSQL - Query Optimization & EXPLAIN ANALYZE

#Introduction

Welcome to episode 15 of the Learn SQL with PostgreSQL series! In episode 14 we learned how to create indexes — but how do we know an index is actually being used? How do we find out why a query is slow? The answer isn't guessing, it's reading the execution plan — the blueprint of how PostgreSQL runs your queries. This skill is a database developer's "superpower".

When there's a slow query, don't jump straight to guessing solutions. Run EXPLAIN ANALYZE, read it carefully, and let the data speak. Is the query doing a full table scan when it should use an index? Did the join pick the wrong algorithm? All the answers are in the execution plan.

In this episode, we'll cover the EXPLAIN and EXPLAIN ANALYZE commands, understand execution plan components like Sequential Scan, Index Scan, and Bitmap Index Scan, the three join algorithms (Nested Loop, Hash Join, Merge Join), how to read the cost, rows, width, actual time, and loops metrics, and how to identify slow queries in production with the pg_stat_statements extension.

#Analyzing Query Execution Plans

#EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN shows the execution plan estimated by the planner — without actually running the query. EXPLAIN ANALYZE runs the real query and shows actual statistics. For real performance analysis, always use EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'budi@example.com';

Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=44)
  Index Cond: (email = 'budi@example.com'::text)
  Actual Time: 0.032..0.034 rows=1 loops=1
Planning Time: 0.143 ms
Execution Time: 0.045 ms

Read it inside-out: PostgreSQL uses Index Scan with the Index Cond condition. Execution time is 0.045 ms — very fast. If the output shows Seq Scan on a big table with Actual Time in the hundreds of milliseconds, that's a signal a missing index.

#Understanding Execution Plan Components

#Sequential Scan vs Index Scan vs Bitmap Index Scan

Three ways PostgreSQL accesses a table:

• Seq Scan (Sequential Scan): reads every row of the table from start to finish. Efficient for small tables or queries fetching a large proportion of data (e.g. 30%+ of rows).
• Index Scan: reads the index, then fetches the matching rows. Fast for selective filters, but one row = one page access (row-by-row).
• Bitmap Index Scan: reads the index, builds a bitmap of matching rows, then fetches all pages containing those rows at once. Efficient for filters matching many scattered rows.

EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 500000;

If the matching rows are very numerous, the planner may choose Seq Scan because it's cheaper than using the index one-by-one. That doesn't mean the index is bad — the planner knows when an index isn't worth it.

#Join Algorithms: Nested Loop, Hash Join, Merge Join

The three join algorithms the planner chooses from:

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

The output will show Hash Join or Nested Loop — and from there you can judge whether an orders(customer_id) index needs to be created (a Nested Loop benefits greatly from an index on the inner side).

#Reading Cost, Rows, Width, Actual Time, and Loops

Each node shows these metrics:

• Cost: an estimate of relative cost (start..total). An abstract unit — compare between plans, not as an absolute value.
• Rows: the estimated number of rows the node returns.
• Width: the estimated bytes per row.
• Actual Time: the actual start..end time per loop, in milliseconds.
• Loops: how many times the node executed. High loops on an inner scan marks an expensive Nested Loop.

#Identifying Slow Queries in Production

Not every slow query can be reproduced locally. In production with real load, we need a tool to find which queries consume the most time. The pg_stat_statements extension is the answer.

#Enabling pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

For the extension to work fully, the shared_preload_libraries = 'pg_stat_statements' parameter must be set in postgresql.conf and the server restarted. After that, the extension starts recording all executed queries.

#Finding the Slowest Queries

SELECT
    query,
    calls,
    total_exec_time / 1000 AS total_ms,
    mean_exec_time / 1000 AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The results show the queries with the largest total execution time — the top optimization candidates. Pay attention to the calls column (frequency) and mean_exec_time (average): a query called thousands of times with a high average is the biggest problem.

#A Systematic Optimization Workflow

1. Find slow queries via pg_stat_statements or the slow query log (log_min_duration_statement in postgresql.conf).
2. Run EXPLAIN (ANALYZE, BUFFERS) on that query.
3. Read the most expensive node: is there a Seq Scan on a big table? Do the rows estimates miss the actual?
4. Fix: add the right index, rewrite the query, or refresh the statistics.
5. Re-measure: compare the Execution Time before and after.

#Common Mistakes

#Closing

In this episode 15, we've mastered evidence-based query optimization: reading the execution plan with EXPLAIN and EXPLAIN ANALYZE, understanding the differences between Sequential Scan, Index Scan, and Bitmap Index Scan, the three join algorithms (Nested Loop, Hash Join, Merge Join), the cost, rows, width, actual time, and loops metrics, and finding slow queries in production with pg_stat_statements.

Key takeaways:

• EXPLAIN ANALYZE runs the real query — always use it for actual analysis.
• Seq Scan on a big table = a missing index signal; Bitmap Scan = many scattered matching rows.
• Nested Loop, Hash Join, Merge Join — know when the planner picks each.
• A rows estimate vs actual mismatch indicates stale statistics → ANALYZE.
• pg_stat_statements is the surveillance camera for queries in production.

In the next episode, episode 16, we get into security: Roles, Security Management & Row Level Security (RLS) — from the role concept that combines users and groups, the CREATE ROLE, GRANT, and REVOKE commands, to RLS for multi-tenant security at the row level, and securing pg_hba.conf.