EXPLAIN ANALYZE for actual execution in PostgreSQL

Introduction

EXPLAIN ANALYZE helps you see how a database runs your query and how long each step takes. It shows the real work done, not just the plan.

When you want to find out why a query is slow.

When you want to see how the database processes your query step-by-step.

When you want to compare different ways of writing a query to find the fastest one.

When you want to check if indexes are being used properly.

When you want to understand the cost and time of each part of your query.

Syntax

PostgreSQL

EXPLAIN ANALYZE your_query_here;

Replace your_query_here with the SQL query you want to analyze.

EXPLAIN ANALYZE actually runs the query, so it may take time and affect data if it's a write operation.

Examples

Shows the real execution details of selecting employees from the Sales department.

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

Runs and explains the insert operation, showing how long it took.

PostgreSQL

EXPLAIN ANALYZE INSERT INTO logs (event) VALUES ('login');

Shows the execution plan and timing for updating product prices in the Books category.

PostgreSQL

EXPLAIN ANALYZE UPDATE products SET price = price * 1.1 WHERE category = 'Books';

Sample Program

This query shows the execution plan and actual time taken to list all tables in the public schema.

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public';

Important Notes

EXPLAIN ANALYZE runs the query, so be careful with queries that change data.

The output shows estimated cost and actual time, helping you compare plan vs real work.

Look for steps with high actual time to find slow parts of your query.

Summary

EXPLAIN ANALYZE runs your query and shows detailed timing and steps.

It helps find slow parts and understand how the database executes your SQL.

Use it to improve query speed and check if indexes or joins work well.