EXPLAIN output reading in PostgreSQL

Introduction

EXPLAIN helps you see how PostgreSQL plans to run your query. It shows the steps and costs so you can understand and improve query speed.

You want to check why a query is slow.

You want to learn how PostgreSQL searches or joins tables.

You want to find if indexes are used in your query.

You want to compare different query versions for performance.

You want to understand the order of operations in your query.

Syntax

PostgreSQL

EXPLAIN [ANALYZE] your_query_here;

Add ANALYZE to run the query and get actual timing and row counts.

Without ANALYZE, EXPLAIN only shows the planned steps, not real execution.

Examples

Shows the query plan without running the query.

PostgreSQL

EXPLAIN SELECT * FROM employees WHERE id = 5;

Runs the query and shows actual time and rows processed.

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE id = 5;

Shows how PostgreSQL plans to join two tables.

PostgreSQL

EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

Sample Program

This runs the query to find employee with id 3 and shows the plan with real times.

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE id = 3;

Important Notes

Look for 'Seq Scan' which means scanning whole table; indexes are faster if used.

Costs show estimated effort; lower is usually better.

Actual time and rows show real performance when using ANALYZE.

Summary

EXPLAIN shows how PostgreSQL plans to run your query.

Use EXPLAIN ANALYZE to see real execution details.

Reading EXPLAIN helps find slow parts and improve queries.