Slow database queries are the primary cause of application latency and high infrastructure costs. When a query that used to take 50ms suddenly spikes to 5s, you cannot rely on guesswork. You need to see exactly how the PostgreSQL query planner processes your request. By using EXPLAIN ANALYZE combined with BUFFERS, you can see the difference between data served from RAM and data fetched from slow disk storage.
This guide demonstrates how to interpret execution plans to find bottlenecks like sequential scans, memory-heavy sorts, and inefficient joins. You will learn how to transform raw plan data into actionable database optimizations.
TL;DR — Use EXPLAIN (ANALYZE, BUFFERS) SELECT ... to see actual execution times and memory usage. Look for high "Shared Read" counts to identify disk I/O bottlenecks and "Seq Scan" on large tables to identify missing indexes.
Understanding EXPLAIN vs. ANALYZE
💡 Analogy: Think of EXPLAIN as a GPS route preview. It shows you the path the database plans to take, estimating traffic and time. EXPLAIN ANALYZE is the actual driving record. It tells you exactly which roads were taken, where you got stuck in traffic, and how long the trip really lasted.
In PostgreSQL 16 and 17, the query planner uses statistics about your data to decide the most efficient way to retrieve rows. A standard EXPLAIN command only shows the planner's estimate. It calculates a "cost" based on arbitrary units (where a sequential page read is 1.0). While estimates are helpful for quick checks, they can be misleading if your table statistics are outdated.
Adding the ANALYZE keyword forces PostgreSQL to execute the query. This provides real-world metrics: actual time spent in each node of the plan, the actual number of rows returned, and the memory used. This is essential for debugging because the planner might estimate 10 rows but actually process 1 million, leading to a massive performance discrepancy.
When to Analyze Your Queries
Optimization is a continuous process, but three specific scenarios require an immediate deep dive into EXPLAIN ANALYZE. First, when you notice high CPU utilization on your database server without a corresponding increase in request volume. This often indicates "Nested Loop" joins or "Hash Joins" occurring on unindexed columns, forcing the CPU to compare every row in Table A against every row in Table B.
Second, you should run an analysis when your "Buffer Cache Hit Ratio" drops. If your application frequently fetches data that isn't in RAM, your performance will be limited by disk I/O. During my recent testing with a 100GB dataset, queries that took 20ms while in the cache jumped to 1.5s when forced to read from a standard SSD. Using the BUFFERS option is the only way to see this specific bottleneck.
Finally, run an analysis before and after adding an index. Developers often assume an index will speed up a query, but PostgreSQL might ignore it if the index is too large or if the query returns a high percentage of the total rows. Verifying the plan ensures your "optimization" isn't actually making the database work harder to maintain index structures without providing a retrieval benefit.
How to Use EXPLAIN ANALYZE and BUFFERS
Step 1: Running the Command
To get the most comprehensive data, always use the BUFFERS parameter. This shows how many blocks were read from the PostgreSQL buffer cache (RAM) versus the OS cache or disk. Note that ANALYZE executes the query, so if you are testing a DELETE or UPDATE, run it inside a transaction and roll it back.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.status = 'pending';
ROLLBACK;Step 2: Identifying "Shared Hit" vs "Shared Read"
Look for the Buffers: line in the output. This is the heart of I/O performance tuning. Shared Hit means the data was already in the PostgreSQL shared buffer (RAM). Shared Read means the data had to be fetched from the OS cache or the physical disk. If your Shared Read count is high, your query is "cold" and will be slow.
-- Example Output Detail
-> Index Scan using orders_status_idx on orders (cost=0.42..8.44 rows=1 width=44)
Actual time=0.012..0.015 rows=10 loops=1
Buffers: shared hit=4 read=2Step 3: Calculating Row Misestimates
Compare the rows estimate in the cost section with the Actual rows. If the planner expects 1 row but finds 10,000, your work_mem might be insufficient, or your table statistics are stale. In such cases, running ANALYZE table_name; (the standalone command) can refresh the statistics and improve future plan choices.
Common Pitfalls in Execution Plans
⚠️ Common Mistake: Relying on EXPLAIN without ANALYZE for complex queries. The planner does not know about data skew. It might assume a value is rare when it actually appears in 90% of your rows, leading it to choose an inefficient Index Scan over a faster Sequential Scan.
The most common "red flag" in a plan is a Seq Scan (Sequential Scan) on a large table. While a Seq Scan is efficient for small tables (under 1,000 rows), it is a disaster for tables with millions of records. It indicates that either an index is missing or the query is written in a way that prevents index usage—for example, using a function on a column like WHERE DATE(created_at) = '2023-01-01'. This forces the DB to calculate the function for every single row.
Another pitfall is the External merge Disk sort. This occurs when the Sort node requires more memory than allocated by the work_mem setting. When this happens, PostgreSQL writes temporary data to disk, which is orders of magnitude slower than sorting in RAM. If you see "Disk" mentioned in the sort method of your EXPLAIN ANALYZE output, you must increase work_mem for that session.
-- Bad: Function prevents index usage
EXPLAIN ANALYZE SELECT * FROM logs WHERE lower(message) = 'error';
-- Good: Use a functional index or direct match
CREATE INDEX logs_lower_message_idx ON logs (lower(message));Pro-Tips for Query Optimization
To maximize performance, use CTEs (Common Table Expressions) carefully. In older PostgreSQL versions, CTEs acted as "optimization fences," meaning the planner could not optimize across the CTE boundary. While PostgreSQL 12+ has improved this, complex CTEs can still lead to "Materializing" large intermediate results. If your plan shows a CTE Scan with high costs, try refactoring it into a subquery or a temporary table.
Adjusting work_mem on a per-query basis is a high-impact tactic. If EXPLAIN ANALYZE shows a Hash Join or Sort spilling to disk, you can increase memory just for that specific connection. This prevents the global memory exhaustion that would occur if you increased work_mem for every single connection in postgresql.conf.
SET work_mem = '64MB';
-- Run your heavy report query here
SELECT ...
RESET work_mem;📌 Key Takeaways
- Always use BUFFERS to see the real impact of disk I/O.
- Seq Scan on large tables usually means you need a new index.
- If Actual Rows differs significantly from Plan Rows, update your statistics with
ANALYZE. - Check for External merge Disk to identify queries that need more
work_mem.
Frequently Asked Questions
Q. Why is EXPLAIN ANALYZE taking so long to run?
A. Because ANALYZE actually executes the query. If the query itself is slow, the analysis will be slow. To see the plan without waiting for execution, use only EXPLAIN (without ANALYZE), though you will lose the "Actual Time" and "Buffers" data.
Q. What is a "Parallel Seq Scan" and is it good?
A. A Parallel Seq Scan means PostgreSQL is using multiple CPU workers to scan a table simultaneously. While faster than a single-threaded scan, it still indicates the absence of a useful index for that query's filter criteria.
Q. Does EXPLAIN ANALYZE include network latency?
A. No. The "Actual time" shown in the plan represents the time spent inside the database engine. It does not include the time taken to transmit the result set over the network to your application or the overhead of the database driver.
Post a Comment