EXPLAIN ANALYZE for query profiling in PostgreSQL

Introduction

EXPLAIN ANALYZE helps you see how a database runs your query and how long each step takes. This helps find slow parts and improve them.

When your query takes too long and you want to find the slow part.

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

When you are learning how queries work inside the database.

When you want to compare two queries to see which is faster.

When you want to check if indexes or joins are used efficiently.

Syntax

PostgreSQL

EXPLAIN ANALYZE your_query_here;

Replace your_query_here with the actual SQL query you want to check.

EXPLAIN ANALYZE runs the query and shows the real execution details, so it may take time.

Examples

Shows how the database runs a simple query filtering employees by department.

PostgreSQL

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

Profiles a query that counts all rows in the orders table.

PostgreSQL

EXPLAIN ANALYZE SELECT COUNT(*) FROM orders;

Shows the execution plan for a join between employees and departments.

PostgreSQL

EXPLAIN ANALYZE SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id;

Sample Program

This query shows how PostgreSQL executes a search for employees with salary over 50000. It helps identify if indexes are used or if a full scan happens.

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000;

Important Notes

EXPLAIN ANALYZE actually runs the query, so changes data if the query modifies data.

Use EXPLAIN without ANALYZE if you want to see the plan without running the query.

Look for high 'actual time' or many 'loops' to find slow parts.

Summary

EXPLAIN ANALYZE shows how a query runs and how long each step takes.

It helps find slow parts and improve query speed.

Always check the output carefully to understand the query plan.