A Simple Guide to PostgreSQL EXPLAIN ANALYZE

When an application gets slow the database is often the first place we look. The usual reaction is to start guessing. Maybe we need an index here or maybe we should rewrite this join. This is like trying to fix an engine by randomly turning screws. There is a much better way.

PostgreSQL gives you a tool that tells you exactly what it’s doing. It’s called EXPLAIN. It shows you the query plan the series of steps the database intends to take to execute your query. This is a map of your query’s journey. But a map of the intended journey isn’t as good as a report from the actual trip. That’s where ANALYZE comes in.

Running EXPLAIN ANALYZE tells PostgreSQL to not only plan the query but to actually execute it and report back on what really happened. It gives you the actual time spent on each step and the actual number of rows it handled. This moves you from guessing to knowing.

From Guessing to Knowing

Let’s see it in action. Imagine we have a users table. A very common task is finding a user by their email address.

First let’s create the table and add some data.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Let’s add a million users to make the problem obvious
INSERT INTO users (username, email) 
SELECT 'user' || i, 'user' || i || '@example.com' FROM generate_series(1, 1000000) AS i;

Now we’ll try to find one specific user by email. We will use EXPLAIN ANALYZE to see what happens under the hood.

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

The output will look something like this. The exact numbers will vary but the structure is what matters.

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Gather  (cost=1000.00..16004.04 rows=1 width=53) (actual time=14.364..128.878 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on users  (cost=0.00..15003.94 rows=1 width=53) (actual time=112.564..112.565 rows=0 loops=3)
        Filter: ((email)::text = 'user500000@example.com'::text)
        Rows Removed by Filter: 333333
Planning Time: 0.112 ms
Execution Time: 128.913 ms

Don’t be intimidated by this output. The most important line is Parallel Seq Scan on users. Seq Scan is short for Sequential Scan. It means PostgreSQL had to read the entire table row by row from beginning to end to find the one we wanted. It looked at a million rows to find one. The Execution Time of over 100ms confirms this was slow.

The Simple Fix

The problem is obvious once you see the plan. The database has no fast way to look up a user by email. We can fix that with an index. An index is a special lookup table that the database can use to find rows quickly.

CREATE INDEX idx_users_on_email ON users(email);

Now let’s run the exact same EXPLAIN ANALYZE command again.

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

The output will now be completely different and much better.

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_users_on_email on users  (cost=0.43..8.45 rows=1 width=53) (actual time=0.045..0.046 rows=1 loops=1)
  Index Cond: ((email)::text = 'user500000@example.com'::text)
Planning Time: 0.145 ms
Execution Time: 0.075 ms

Look at that. The Seq Scan is gone replaced by an Index Scan. The database used our new index to find the user directly without scanning the whole table. And look at the Execution Time. It’s gone from over 100 milliseconds to less than one millisecond. The query is now over a thousand times faster. We didn't have to guess. The query plan told us the problem was a sequential scan and the solution was an index.

Reading the Output

When you read EXPLAIN ANALYZE output focus on two things first.

1. The Plan: Look for operations like Seq Scan on large tables. This is often a sign you are missing an index. You want to see Index Scan or Bitmap Heap Scan instead.
2. The Time: Look at the actual time. This tells you how long each step really took. It will guide you to the most expensive part of your query.

The cost figures are estimates the planner makes. They are useful for comparing plans but the actual time is the ground truth.

What About Joins?

This same principle applies to more complex queries. If you have a slow query involving a JOIN EXPLAIN ANALYZE will show you how PostgreSQL is joining the tables. It might be using a Nested Loop a Hash Join or a Merge Join. The plan will also show you if it’s doing a Seq Scan on one of the tables in the join which is a common problem. The solution is often adding an index on the foreign key column used in the join condition.

Don’t optimize what you don’t measure. For database queries EXPLAIN ANALYZE is the measuring tape.

It can seem complex at first but you don’t need to understand every detail to get value from it. Start by looking for the obvious problems like sequential scans on big tables. That alone will help you solve a huge number of performance issues.

Stop guessing and start analyzing. It is the most direct path to a faster application.

— Rishi Banerjee
September 2025