EXPLAIN ANALYZE is the single most important tool for understanding and optimizing PostgreSQL query performance, but most people read it like a book, sequentially, when they should be reading it like a detective, looking for the outliers.
Let’s see it in action. Imagine this simple table and query:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
signup_date DATE
);
INSERT INTO users (username, signup_date)
SELECT 'user_' || generate_series(1, 1000000), NOW()::date - (generate_series(1, 1000000) * interval '1 day');
CREATE INDEX idx_users_signup_date ON users (signup_date);
Now, let’s query for users who signed up in the last 30 days:
EXPLAIN ANALYZE
SELECT * FROM users WHERE signup_date >= NOW()::date - interval '30 days';
Here’s a sample output:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using idx_users_signup_date on users (cost=0.42..271.86 rows=3000 width=40) (actual time=0.020..1.680 rows=2999 loops=1)
Index Cond: (signup_date >= (CURRENT_DATE - 30))
Planning Time: 0.120 ms
Execution Time: 1.750 ms
(4 rows)
The problem EXPLAIN ANALYZE solves is that PostgreSQL, despite its intelligence, can make suboptimal choices about how to execute your SQL queries. It might scan an entire table when it only needs a few rows, or join two large tables in an inefficient order. EXPLAIN ANALYZE shows you what the database actually did, not just what it planned to do.
The output is a tree representing the query plan. Each node in the tree is an operation (like Seq Scan, Index Scan, Hash Join, Nested Loop). For each node, you get:
cost=start..end: An estimated cost.startis the estimated cost to return the first row,endis the estimated cost to return all rows. This is the planner’s best guess.rows: The estimated number of rows this node will output.width: The estimated average width (in bytes) of the rows outputted.actual time=start..end: The actual time taken (in milliseconds) to execute this node.startis the time to return the first row,endis the time to return all rows.rows: The actual number of rows outputted by this node.loops: How many times this node was executed.
The key to reading EXPLAIN ANALYZE is to compare the estimated values with the actual values. Deviations are where the problems lie.
In our example, the Index Scan node is the only operation.
- Estimated cost:
0.42..271.86. The planner thought it would be moderately expensive. - Estimated rows:
3000. It guessed we’d get about 3000 rows. - Actual time:
0.020..1.680. It was very fast, especially for the first row. - Actual rows:
2999. It returned almost exactly what it estimated.
The estimates here are pretty close to reality, and the execution time is low. But what if the query was slower, and the EXPLAIN ANALYZE showed this:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..15000.00 rows=50000 width=40) (actual time=100.500..1200.750 rows=50000 loops=1)
Filter: (signup_date >= (CURRENT_DATE - 30))
Rows Removed by Filter: 950000
Planning Time: 0.250 ms
Execution Time: 1250.500 ms
(4 rows)
Here’s the breakdown:
Seq Scan on users: This is the big red flag. It means PostgreSQL read the entireuserstable, row by row.cost=0.00..15000.00: The planner estimated this would be very expensive.rows=50000: It estimated it would find 50,000 rows matching the condition.actual time=100.500..1200.750: It took over a second to scan the whole table. The100.500is the time to get the first row,1200.750is the time to get the last.rows=50000: It actually found 50,000 rows.Rows Removed by Filter: 950000: This is crucial. TheSeq Scanread 1,000,000 rows total (50,000 matched + 950,000 filtered out).
The problem here is that the planner should have used the idx_users_signup_date index. The fact that it chose Seq Scan indicates a major discrepancy between its statistics and reality, or a poorly written query.
The most surprising thing about EXPLAIN ANALYZE is that the total actual time for the query is not the sum of the actual time of its child nodes. It’s the actual time of the outermost node. This is because the actual time in a parent node includes the time spent in its children.
The mental model is a hierarchy of work. The database doesn’t execute operations in isolation; they depend on each other. An Index Scan might feed rows to a Hash Join, which then feeds rows to a Sort. The actual time reported at each level is the cumulative time for that node and everything below it, for that specific execution of the node.
When you see a large difference between estimated and actual rows, it’s often because PostgreSQL’s statistics about the data are stale or inaccurate. Running ANALYZE users; (without EXPLAIN) updates these statistics, and then re-running EXPLAIN ANALYZE might show a different, potentially better, plan.
The most common reason for a Seq Scan on a table that should be using an index is that the condition in the WHERE clause is not "sargable" (searchable argument). This means the database can’t use the index efficiently. For example, using a function on the indexed column like WHERE lower(username) = 'alice' prevents index use on username, whereas WHERE username = 'alice' would use it.
Another common culprit is when the planner thinks a Seq Scan is faster because it estimates that a large percentage of the table will match the condition. If the planner estimates 50% of rows match, it might decide scanning the whole table is quicker than the overhead of index lookups. If your ANALYZE statistics are off and it’s actually only 1% matching, you’re being penalized for bad stats.
For joins, look for Nested Loop joins where the inner loop is executed many times (loops > 1) and the actual time for the inner node is high. This often indicates a missing index on the join key of the inner table.
If you see a HashAggregate or Sort operation taking a long time, it might be because there’s not enough memory allocated (work_mem). If the data being aggregated or sorted exceeds work_mem, PostgreSQL spills to disk, which is dramatically slower. Increasing work_mem for the session can help: SET session_replication_role = replica; SET work_mem = '128MB'; EXPLAIN ANALYZE ....
Finally, if the query execution time is consistently high but the EXPLAIN ANALYZE shows low costs and times for all nodes, the issue might be outside the query itself: network latency, application-level processing, or even a very busy server.
The next thing you’ll likely encounter after optimizing query plans is understanding how connection pooling affects performance.