Using EXPLAIN ANALYZE to Measure Actual Query Execution Time

If the EXPLAIN command lets you peek into a crystal ball and see how PostgreSQL “plans” to run your query, then EXPLAIN ANALYZE turns you into a real detective who finds out what actually happened.

Key differences between EXPLAIN and EXPLAIN ANALYZE:

EXPLAIN – this is the theory, showing how PostgreSQL plans to execute your query. You see estimated values like number of rows (rows) and execution cost (cost).

EXPLAIN ANALYZE – this is the real deal. PostgreSQL actually runs the query and shows you:

• The actual number of rows processed at each step.
• The real execution time for every operation.
• A comparison with the plan’s assumptions (rows and cost).

Example: if your query is supposed to process 100 rows, but actually processes 10,000 rows, EXPLAIN ANALYZE will immediately expose that messy fact!

Basic Syntax and Usage

Just like EXPLAIN, EXPLAIN ANALYZE is super easy to use. Just add the word ANALYZE to your EXPLAIN command.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Here’s what PostgreSQL does:

• It runs the query.
• Logs every operation in the execution plan, including the actual stats.
• Returns a full description of the query execution process.

What Data Does EXPLAIN ANALYZE Provide?

Actual operation execution time:

• Actual Start Time: when the operation started.
• Actual End Time: when the operation finished.

Total number of rows processed:

This helps you see how accurate the plan’s assumptions were (the rows values).

Buffer info:

How disk and memory-oriented buffers were used.

Example of Using EXPLAIN ANALYZE

Let’s check out a concrete example. We have a students table with student data:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Let’s run a query to fetch students older than 20:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Sample result:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Let’s break down the result:

• Seq Scan – means PostgreSQL is doing a sequential scan of the table.
• cost=0.00..14.00 – this is the estimated cost of the operation.
• rows=2 – PostgreSQL expects the query to return 2 rows (and it’s right!).
• actual time=0.025..0.026 – the real time taken for the operation (in milliseconds).
• Rows Removed by Filter: 2 – two rows were filtered out because they didn’t match the WHERE condition.

Comparing Theory and Practice

This is where the magic of EXPLAIN ANALYZE happens: it shows how the query was actually executed, and lets you compare it to the theoretical execution plan.

Let’s look at a more complex example.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Sample result:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

What do we see:

1. PostgreSQL ran the query in 0.057 milliseconds.
2. Only one row (rows=1) matches the WHERE conditions.
3. Three rows were filtered out (Rows Removed by Filter: 3).

Summary

Using EXPLAIN ANALYZE lets you find bottlenecks and figure out how to optimize your queries. For example:

• If Seq Scan is too “heavy”, maybe it’s time to add an index.
• If PostgreSQL’s assumptions are way off from the real data, check your table stats (ANALYZE) or your index structure.

2

Task

SQL SELF, level 41, lesson 3

Locked

Analyzing Query Execution with Filtering and Sorting

Analyzing Query Execution with Filtering and Sorting

Open