Query Profiling and Statistics in SQL: Using EXPLAIN ANALYZE, Buffer Metrics, and Row Estimates to Diagnose Slow Queries
Most slow SQL queries aren't fixed by random index additions — they're fixed by reading what the query planner is actually telling you. This expert-level lesson teaches you to systematically diagnose any slow query using EXPLAIN ANALYZE, buffer metrics, and PostgreSQL's statistics system, so you stop guessing and start solving.
🔥 Expert29 min readAug 21, 2026Updated Aug 29, 2026
Query Profiling and Statistics in SQL: Using EXPLAIN ANALYZE, Buffer Metrics, and Row Estimates to Diagnose Slow Queries
Introduction
You've got a query that's crawling. It ran fine last week, it ran fine when you tested it on a sample dataset, and now it's hammering your production database and your users are screaming. You add an index. Nothing changes. You rewrite the join order. Still slow. You start guessing, and guessing with production databases is a dangerous game.
The problem isn't that you don't know SQL — it's that you're flying blind. PostgreSQL (and most enterprise SQL databases) contains a sophisticated internal reasoning engine called the query planner, and it generates a detailed execution plan every single time you run a query. That plan tells you exactly what the database decided to do, why it made those decisions, and most importantly, where reality diverged from expectations. Every slow query has a story buried in its execution plan. Learning to read that story is the difference between a developer who randomly tries fixes and a database professional who diagnoses problems with precision.
By the end of this lesson, you'll be able to open any execution plan, understand what the planner was thinking, identify where the plan went wrong, and make targeted interventions that actually fix the problem. We'll focus on PostgreSQL because its output is the most detailed and the concepts transfer directly to other systems like MySQL 8+, Oracle, and SQL Server.
What you'll learn:
How the PostgreSQL query planner works and how to read EXPLAIN and EXPLAIN ANALYZE output at an expert level
What buffer metrics (shared hits, reads, dirtied, written) tell you about I/O behavior and how to use them diagnostically
How to interpret row estimate accuracy and why bad estimates cause catastrophically wrong execution plans
How planner statistics work, how to update them, and how to tune them for skewed data distributions
A repeatable diagnostic workflow for systematically isolating performance problems in complex queries
Basic indexing concepts (B-tree indexes, when indexes get used)
General awareness that query planning exists (you've seen EXPLAIN output before)
PostgreSQL 13+ (though most concepts apply to 10+)
If you've never seen EXPLAIN output before, spend 20 minutes with the PostgreSQL documentation on basic query planning before continuing here.
How the Query Planner Actually Makes Decisions
Before you can diagnose a bad plan, you need to understand what "a plan" actually is and how the planner constructs one.
When you submit a SQL query, PostgreSQL doesn't execute it immediately. It first passes your query through the parser, then the rewriter (which handles rules and views), and finally the planner/optimizer. The planner's job is to take your declarative SQL statement — which says what you want, not how to get it — and convert it into an imperative execution plan: a tree of physical operations with a specific order of operations.
The planner considers multiple ways to execute your query and assigns a cost to each option. Cost in PostgreSQL is a unitless number calibrated so that 1.0 roughly equals the cost of reading one 8KB page from disk. The planner then chooses the plan with the lowest estimated total cost.
That word estimated is doing enormous work in that sentence. The planner doesn't know exactly how long operations will take — it estimates based on:
Statistics about your data — stored in pg_statistic and accessible via pg_stats
Cost parameters — configurable GUC parameters like seq_page_cost, random_page_cost, cpu_tuple_cost, etc.
Cardinality estimates — how many rows the planner expects each operation to produce
When any of these inputs are wrong, the planner chooses the wrong plan. Most slow queries come down to bad estimates producing a bad plan. Your job as a diagnostic expert is to figure out which estimate was wrong and why.
Reading EXPLAIN Output: Building a Foundation
Let's start with a realistic scenario. We have an e-commerce database with an orders table (12 million rows), an order_items table (38 million rows), and a customers table (2.1 million rows). We're investigating this query:
SELECT
c.customer_id,
c.email,
COUNT(o.order_id) AS order_count,
SUM(oi.unit_price * oi.quantity) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.region = 'NORTHEAST'
AND o.order_date >= '2023-01-01'
GROUP BY c.customer_id, c.email
HAVING SUM(oi.unit_price * oi.quantity) > 500.00
ORDER BY total_spent DESC;
Start with a plain EXPLAIN (no ANALYZE):
EXPLAIN
SELECT
c.customer_id,
c.email,
COUNT(o.order_id) AS order_count,
SUM(oi.unit_price * oi.quantity) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.region = 'NORTHEAST'
AND o.order_date >= '2023-01-01'
GROUP BY c.customer_id, c.email
HAVING SUM(oi.unit_price * oi.quantity) > 500.00
ORDER BY total_spent DESC;
This is a plan tree. The indentation tells you the parent-child relationships. Execution flows from the leaves (innermost, most indented nodes) upward to the root. Let's decode the anatomy.
Anatomy of a Plan Node
Every node looks like this:
Node Type (cost=startup..total rows=estimated_rows width=estimated_row_width)
cost=startup..total: The startup cost is the estimated cost before the node produces its first row. The total cost is the estimated cost when the last row is produced. For operations like sorts and aggregates, startup cost is high because they can't output anything until they've consumed all input.
rows: The planner's estimate of how many rows this node will output.
width: The estimated average width of each output row in bytes.
Reading the Tree Bottom-Up
Look at our plan. The deepest operations are:
Bitmap Index Scan on orders_order_date_idx — Scans the index for all orders with order_date >= '2023-01-01'
Bitmap Heap Scan on orders — Uses the bitmap from step 1 to fetch the matching rows from the heap (the actual table data)
Seq Scan on customers — Reads all 2.1M customer rows, filtering on region = 'NORTHEAST'
The Bitmap Index Scan + Bitmap Heap Scan combination is PostgreSQL's middle ground between a pure sequential scan and a pure index scan. The database first scans the index to build a bitmap of which pages contain matching rows, then reads those pages in physical order (to maximize sequential I/O). It's particularly efficient when a query returns 1-20% of the table.
The Hash Join operations build an in-memory hash table from one side (the "inner" relation) and probe it with rows from the other side (the "outer" relation). This is generally the most efficient join type when both inputs are large and there's no suitable index for a nested loop join.
Key insight
The planner chose to filter customers with a sequential scan rather than an index on region. This tells you one of two things: either there's no index on customers.region, or the planner estimated that 211,271 rows (about 10% of the table) would match — making a sequential scan cheaper than an index scan, which would involve 211K random I/O operations.
EXPLAIN ANALYZE: Where Estimates Meet Reality
Plain EXPLAIN shows you what the planner intends to do. EXPLAIN ANALYZE actually runs the query and shows you what happened, then overlays the estimates on top of the actuals. This is where diagnosis becomes possible.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
c.customer_id,
c.email,
COUNT(o.order_id) AS order_count,
SUM(oi.unit_price * oi.quantity) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.region = 'NORTHEAST'
AND o.order_date >= '2023-01-01'
GROUP BY c.customer_id, c.email
HAVING SUM(oi.unit_price * oi.quantity) > 500.00
ORDER BY total_spent DESC;
The BUFFERS option is non-negotiable in professional diagnostics. Always include it. The output now looks like:
Now we have real data. Let's systematically extract the diagnostic signals.
Reading Actual vs. Estimated Rows
This is the most important diagnostic comparison. Create a mental table:
Node
Estimated Rows
Actual Rows
Ratio
Seq Scan on order_items
3,812,044
38,120,440
10x underestimate
Hash Join (orders × customers)
283,249
312,847
1.1x (fine)
Hash Join (order_items × orders)
2,555,336
7,823,441
3x underestimate
HashAggregate
60,714
89,234
1.5x underestimate
Sort
60,714
89,234
1.5x (propagated)
The critical number is the order_items sequential scan returning 38.1 million rows instead of the estimated 3.8 million. The planner thought the table had 10x fewer rows than it actually does. This is a classic case of stale statistics — ANALYZE hasn't run on this table recently, and 34 million rows have been inserted or updated since the last statistics collection.
Rule of thumb
Estimate ratios under 2x are generally acceptable. Ratios of 3-10x cause the planner to make suboptimal decisions. Ratios above 10x almost always lead to catastrophically wrong plans. In this case, the 10x underestimate on order_items caused every join cardinality estimate above it in the tree to be wrong, compounding the error.
Buffer Metrics: Reading Your I/O Story
The BUFFERS output is where most analysts stop reading, because it looks like noise. It's not. Buffer metrics are one of the most valuable diagnostic tools in the entire output.
PostgreSQL's buffer pool (the shared buffer cache) sits between your queries and physical disk. When a query needs a data page:
It checks the shared buffer cache first — this is a shared hit
If not in cache, it reads from disk (or OS page cache) — this is a read
If the query modified a page — this is a dirtied
If modified pages were flushed to disk during the query — this is a written
Interpreting Buffer Numbers
From our query output, look at the Seq Scan on order_items:
Buffers: shared hit=198234, read=41823
This tells us:
198,234 pages were found in PostgreSQL's shared buffer cache
41,823 pages had to be read from outside the cache (either OS cache or physical disk)
The table has roughly 240,057 pages total (198234 + 41823). At 8KB per page, that's about 1.9 GB. The fact that 83% of the data was in shared buffers suggests this table is frequently accessed — or this query was run recently on a warm cache.
Now look at the sort node:
Sort Method: external merge Disk: 9824kB
This is a major red flag. External merge sort means the sort couldn't fit in memory (work_mem) and had to spill to disk. An external merge sort is typically 5-100x slower than an in-memory sort. In this case, 9.8 MB of sort data spilled to disk because work_mem wasn't large enough to hold the sort buffer.
The written buffers at the sort level (1,204 pages) confirm disk writes occurred during query execution.
The Buffer Metrics Diagnostic Framework
Use this framework when examining buffer output:
High read count relative to hits: The data isn't in cache. This is a cold-cache problem. On a well-tuned system, frequently-accessed data should be mostly in shared_buffers. If you're seeing high read counts consistently, either shared_buffers is too small, or this data is infrequently accessed and the I/O cost is real.
High hit count on unexpectedly large scans: The data is in cache but you're still doing a lot of work. This means the operation is CPU-bound, not I/O-bound. Adding more I/O capacity won't help; you need to reduce the amount of data processed (better indexes, better predicates, partition pruning).
Written buffers during query: The query is causing checkpoint activity or is modifying data at a rate that exceeds background writer capacity. This can slow down other concurrent queries.
Buffers much larger than the estimated row count suggests: Cross-reference the page count with the expected table size. If a node shows read=500000 but the table should only be 100,000 pages, something is very wrong (possibly a bloated table with a high dead tuple count after heavy deletes/updates).
Pro tip
Run your query twice in rapid succession and compare the buffer numbers. If read drops dramatically on the second run, the data is being cached by the OS but not fitting in shared_buffers. This means increasing shared_buffers might help — but only up to the point where the working set fits. If read stays the same, the working set genuinely doesn't fit in any cache tier.
Row Estimates and the Statistics System
Bad row estimates cause bad plans. To fix bad estimates, you need to understand where they come from.
How PostgreSQL Estimates Row Counts
PostgreSQL stores statistics about each column in pg_statistic, surfaced through the view pg_stats. Let's look at what's there for our order_items table:
SELECT
attname,
n_distinct,
correlation,
null_frac,
avg_width,
most_common_vals,
most_common_freqs,
histogram_bounds
FROM pg_stats
WHERE tablename = 'order_items'
AND attname IN ('order_id', 'unit_price', 'quantity');
The key fields:
n_distinct: The estimated number of distinct values. Positive values are absolute counts; negative values are fractions of total rows (e.g., -0.95 means 95% of rows have distinct values).
correlation: A value from -1 to 1 measuring how well the physical row order correlates with the sort order of this column. 1.0 means the data is perfectly sorted on disk by this column. Values near 0 mean random order. This heavily influences whether an index scan is worth it.
null_frac: Fraction of rows with NULL values.
most_common_vals / most_common_freqs: Arrays of the most frequent values and their frequencies. The planner uses these for equality predicates.
histogram_bounds: Array of values dividing the non-null data into equal-frequency buckets, used for range predicates.
The number of buckets in the histogram (and entries in MCV lists) is controlled by default_statistics_target, which defaults to 100. This gives the planner 100 histogram buckets — enough for uniform distributions but often insufficient for skewed data.
The Statistics Staleness Problem
Let's check when statistics were last collected:
SELECT
schemaname,
tablename,
n_live_tup,
n_dead_tup,
last_autovacuum,
last_autoanalyze,
last_analyze,
n_mod_since_analyze
FROM pg_stat_user_tables
WHERE tablename = 'order_items';
If n_mod_since_analyze is a large fraction of n_live_tup, your statistics are stale. The autovacuum daemon triggers ANALYZE when n_mod_since_analyze exceeds autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * n_live_tup. On a 38-million-row table with default settings (scale factor 0.2), autovacuum won't trigger until 7.6 million rows have changed. That's a lot of staleness tolerance.
In our case, if the order_items table had 3.8 million rows when ANALYZE last ran and has since grown to 38 million, the stored statistics describe the table as it was at 10% of its current size. The planner is making estimates based on fundamentally incorrect population data.
The immediate fix:
ANALYZE order_items;
-- Or analyze with verbose output to see what changed:
ANALYZE VERBOSE order_items;
But this is a band-aid. If the table is growing rapidly, you need to tune autovacuum for this specific table:
ALTER TABLE order_items SET (
autovacuum_analyze_scale_factor = 0.01, -- Trigger at 1% change, not 20%
autovacuum_analyze_threshold = 1000 -- Plus 1000 rows baseline
);
On a 38-million-row table, a 1% trigger means analysis runs after 381,000 changes — still a lot of drift, but far better than 7.6 million.
Fixing Skewed Data with statistics_target
Sometimes statistics are fresh but still produce bad estimates because the data is highly skewed — most values cluster around a few points, and the histogram doesn't capture the distribution well enough.
Consider a status column in our orders table with values like 'DELIVERED' (95% of rows), 'PENDING' (3%), 'CANCELLED' (1.9%), and 'PROCESSING' (0.1%). A query filtering on status = 'PROCESSING' might match 12,000 rows, but if the planner doesn't know about this specific value's frequency, it might estimate based on the column's average frequency (25% for 4 values) — producing a catastrophic 3 million row estimate.
The fix is to increase the statistics target for this column:
ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;
Now the MCV list can hold 500 entries instead of 100, giving the planner much more precise frequency information for specific values.
Check the impact:
SELECT
most_common_vals,
most_common_freqs
FROM pg_stats
WHERE tablename = 'orders'
AND attname = 'status';
You should now see 'PROCESSING' explicitly in the MCV list with its actual frequency, allowing the planner to estimate the row count accurately.
Multi-Column Statistics: When Columns Are Correlated
Here's a subtle but extremely common problem. Suppose our orders table has both a region column and a status column, and you write:
WHERE region = 'NORTHEAST' AND status = 'PROCESSING'
The planner estimates the row count by multiplying the selectivities:
But in reality, if PROCESSING orders only appear in certain regions (because of how your fulfillment system works), the actual selectivity might be 0.0008. The planner is assuming statistical independence between columns when they're actually correlated. On a 12-million-row table, the planner estimates 1,800 rows; the actual count is 9,600 — a 5x underestimate that could push the planner toward a nested loop join when a hash join would be faster.
PostgreSQL 10+ supports extended statistics to address this:
CREATE STATISTICS orders_region_status_corr (dependencies, ndistinct, mcv)
ON region, status
FROM orders;
ANALYZE orders;
The dependencies option tells the planner which columns are functionally or statistically correlated. The ndistinct option gives better multi-column distinct counts. The mcv option (PostgreSQL 12+) creates a multi-column MCV list.
After creating extended statistics and running ANALYZE, check what the planner learned:
SELECT stxname, stxkind, stxndistinct, stxdependencies
FROM pg_statistic_ext_data
JOIN pg_statistic_ext ON pg_statistic_ext_data.stxoid = pg_statistic_ext.oid
WHERE stxname = 'orders_region_status_corr';
Advanced EXPLAIN Options and Formatting
EXPLAIN with FORMAT JSON
For programmatic analysis or when using visualization tools, JSON format is invaluable:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;
The JSON output exposes every field the planner tracks, including some not shown in the text format:
"Actual Startup Time" and "Actual Total Time" separately
"Actual Loops" — crucial for nested loop joins where inner nodes execute many times
Copy the JSON output into explain.dalibo.com or pgMustard for a visual plan tree that color-codes expensive nodes.
Enabling I/O Timing
By default, PostgreSQL doesn't track how long I/O operations take — only how many pages were accessed. Enable timing at the session level for diagnostics:
SET track_io_timing = on;
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;
This tells you 8.2 seconds of your query's wall-clock time was spent waiting for I/O. If I/O time is a large fraction of total execution time, your bottleneck is storage throughput. If I/O time is small but execution time is high, the bottleneck is CPU processing or memory bandwidth.
Warning
track_io_timing has a small performance overhead (it calls clock_gettime() on every I/O operation) and should not be left enabled permanently on high-throughput systems. Enable it for diagnostic sessions, disable it afterward.
The loops Field: The Nested Loop Trap
This is one of the most commonly misread parts of EXPLAIN ANALYZE output. When you have a nested loop join, the inner node executes once per row from the outer node. The actual time in the output is per loop, not total:
Nested Loop (actual time=0.023..18234.112 rows=7823441 loops=1)
-> Index Scan on orders (actual time=0.012..0.034 rows=1 loops=318293)
Index Cond: (order_id = oi.order_id)
That actual time=0.012..0.034 looks fast. But loops=318293 means it ran 318,293 times. The actual total time for this node is:
318,293 × 0.034 ms = 10,822 ms ≈ 10.8 seconds
The buffer counts are also per-loop in older PostgreSQL versions (this was fixed in PostgreSQL 15 where cumulative buffers are shown). Always multiply buffer counts by loops for accurate totals.
A Systematic Diagnostic Workflow
Stop randomly tweaking things. Here's the workflow used by experienced database engineers:
Step 1: Capture the Full Plan
-- Always use ANALYZE, BUFFERS, and enable I/O timing
SET track_io_timing = on;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
<your query>;
Use VERBOSE to see output column lists — this helps you spot unnecessary projections and column references that force expensive operations.
Step 2: Find the Most Expensive Nodes
Look at total elapsed time in the Sort or root node. Then find which child nodes consumed the most time. In text format, the root node's actual time is cumulative (total query time). You need to subtract children from parents to find node-specific costs.
In JSON format, each node has "Actual Total Time" which is also cumulative. Write or use a tool that computes exclusive time per node.
A quick mental approach: sort the nodes by their total time estimate and start investigating the top 2-3 outliers.
Step 3: Compare Estimates to Actuals
For each expensive node, compare rows (estimated) to the actual rows. Calculate the ratio. Flag anything over 3x.
Work bottom-up. The lowest node with a bad estimate is the root cause. Upstream bad estimates are usually just propagated error. In our example, the order_items sequential scan is the lowest node with a 10x error. Everything above it has bad estimates because they're multiplied by a 10x-wrong input.
Step 5: Check Statistics Currency
SELECT
last_autoanalyze,
last_analyze,
n_mod_since_analyze,
n_live_tup,
ROUND(100.0 * n_mod_since_analyze / NULLIF(n_live_tup, 0), 2) AS pct_modified
FROM pg_stat_user_tables
WHERE tablename = '<your table>';
If pct_modified is above 5%, run ANALYZE. If the table is growing rapidly, tune autovacuum.
Step 6: Investigate Join Type Choices
Each join type has a different cost profile:
Join Type
Best When
Nested Loop
Inner relation is small, or there's a very selective index on the join column
Hash Join
Both relations are large, joining on equality, and work_mem is sufficient
Merge Join
Both relations are pre-sorted on the join key, or the sort cost is amortized
If the planner chose Nested Loop but you're seeing millions of loops, it estimated the outer relation would be tiny (and was wrong). Force a hash join to test:
SET enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS) <your query>;
Warning
SET enable_nestloop = off is a diagnostic tool, not a production fix. Re-enable it after testing. If forcing a different join type dramatically improves performance, the underlying problem is a bad row estimate that pushed the planner toward the wrong plan. Fix the statistics, not the join type hint.
Step 7: Check for Spills and Memory Issues
Scan the output for:
Sort Method: external merge — sort spilled to disk
Batches: N where N > 1 in HashAggregate or Hash Join — hash operation batched to disk
Memory Usage: <large number> — consuming too much memory, potentially causing other queries to spill
Spills can be addressed by increasing work_mem for the session:
SET work_mem = '256MB';
EXPLAIN (ANALYZE, BUFFERS) <your query>;
If performance improves dramatically, consider increasing work_mem at the session level for this type of query, or globally if your server memory allows. Be careful: work_mem is allocated per sort/hash operation per query, and a single complex query might have a dozen such operations simultaneously.
Step 8: Validate with Query Rewrite or Index Additions
Once you've identified the root cause, make your targeted intervention and rerun EXPLAIN ANALYZE. Compare:
Execution time
Estimated vs. actual rows (did the estimates improve?)
Buffer counts (did we reduce I/O?)
Sort method (did we eliminate the disk spill?)
Document both plans. You should be able to explain exactly why the new plan is better, not just observe that it is.
Hands-On Exercise
Set up the following schema and data in your PostgreSQL instance:
-- Create tables
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
supplier_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
product_id INT REFERENCES products(product_id),
sale_date DATE NOT NULL,
quantity INT NOT NULL,
sale_price NUMERIC(10,2) NOT NULL,
region TEXT NOT NULL,
salesperson_id INT NOT NULL
);
-- Insert 500k products (skewed category distribution)
INSERT INTO products (name, category, base_price, supplier_id)
SELECT
'Product_' || i,
CASE
WHEN random() < 0.70 THEN 'ELECTRONICS'
WHEN random() < 0.85 THEN 'CLOTHING'
WHEN random() < 0.95 THEN 'FOOD'
ELSE 'OTHER'
END,
(random() * 1000 + 10)::NUMERIC(10,2),
(random() * 500 + 1)::INT
FROM generate_series(1, 500000) i;
-- Insert 5 million sales (deliberately NOT analyzing afterward)
INSERT INTO sales (product_id, sale_date, quantity, sale_price, region, salesperson_id)
SELECT
(random() * 499999 + 1)::INT,
DATE '2022-01-01' + (random() * 730)::INT,
(random() * 50 + 1)::INT,
(random() * 990 + 10)::NUMERIC(10,2),
CASE
WHEN random() < 0.40 THEN 'NORTHEAST'
WHEN random() < 0.65 THEN 'SOUTH'
WHEN random() < 0.85 THEN 'MIDWEST'
ELSE 'WEST'
END,
(random() * 200 + 1)::INT
FROM generate_series(1, 5000000) i;
-- Analyze products but NOT sales (to simulate stale statistics)
ANALYZE products;
-- DO NOT run ANALYZE on sales
Now run this investigative query:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
p.category,
s.region,
COUNT(*) AS total_sales,
SUM(s.quantity * s.sale_price) AS total_revenue,
AVG(s.sale_price) AS avg_price
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE p.category = 'ELECTRONICS'
AND s.sale_date BETWEEN '2023-01-01' AND '2023-12-31'
AND s.sale_price > 200
GROUP BY p.category, s.region
ORDER BY total_revenue DESC;
Your tasks:
Capture the full EXPLAIN ANALYZE BUFFERS output and identify the estimated vs. actual row count for the sales table scan. Calculate the ratio.
Check pg_stat_user_tables for the sales table. What does n_mod_since_analyze tell you?
Run ANALYZE sales; and then re-run EXPLAIN ANALYZE BUFFERS on the same query. Compare:
Did the row estimate for the sales scan improve?
Did the join strategy change?
Did execution time change?
Look at the buffer output. Calculate the hit rate (hits / (hits + reads)). What does this tell you about the cache behavior?
If there's a sort or hash spill in the plan, experiment with SET work_mem = '64MB' and rerun. Did the spill disappear?
Add an index on sales(sale_date, sale_price) and see how the plan changes:
CREATE INDEX idx_sales_date_price ON sales(sale_date, sale_price);
ANALYZE sales;
Did the planner use it? If not, why not? (Hint: check the correlation value in pg_stats.)
Common Mistakes & Troubleshooting
Mistake 1: Trusting EXPLAIN Without ANALYZE
EXPLAIN alone shows you estimates. It tells you absolutely nothing about whether those estimates are correct. Always use EXPLAIN ANALYZE when diagnosing actual performance problems. The only time to use plain EXPLAIN is when you can't afford to run the query (e.g., a destructive DML operation) — in which case, use EXPLAIN on a similar SELECT with the same WHERE clause.
Mistake 2: Looking Only at Total Query Time
Profilers seduced by a single 48-second number spend time optimizing the wrong node. Always trace the expensive time to the specific node that's causing it. A 48-second query might spend 45 of those seconds in a single HashAggregate that's spilling to disk — and the fix is just a work_mem increase, not a schema redesign.
Mistake 3: Assuming an Index Will Always Help
An index on a column with high correlation (data stored nearly in sorted order) is very efficient. An index on a column with correlation near 0 (random order) forces random I/O for every row fetched, which is often slower than a sequential scan when retrieving more than 1-2% of the table. Check correlation in pg_stats before building indexes for range predicates.
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'sales';
A correlation of 0.02 on sale_date means dates are scattered randomly through the physical pages. A range scan on sale_date covering 5% of the table would touch almost every page anyway — making the index largely useless for that specific access pattern.
Mistake 4: Setting enable_nestloop = off in Production
Disabling join types is a diagnostic technique. If you leave enable_nestloop = off in production, you'll sometimes get worse plans because nested loop really is the best option for small inner relations with selective indexes. Fix the root cause (statistics, indexes, configuration), don't suppress the symptom.
Mistake 5: Ignoring the Loops Multiplier
This one causes real diagnostic errors. A node that looks cheap in isolation (0.034ms per execution) can be your biggest time sink if it executes 500,000 times in a nested loop. Always multiply actual time by loops to get the true contribution of that node.
Mistake 6: Running EXPLAIN ANALYZE on a Cold Cache
Buffer metrics are meaningless unless you know the cache state. On a freshly started PostgreSQL instance, everything is a cache miss. In production, frequently-accessed data is warm. If you're testing with a cold cache, your read counts and I/O times will be much higher than in production — and vice versa.
For reproducible testing, you can either:
Run the query twice and use the second run's numbers (warm cache simulation)
Clear the cache between runs: pg_prewarm extension for warming specific tables, or (Linux only) echo 3 > /proc/sys/vm/drop_caches to clear the OS cache (use with caution)
Mistake 7: Not Accounting for Parallel Query Plans
If you see Workers Planned: 4 and Workers Launched: 4, your query is using parallel execution. The buffer counts and timing in each worker node are per worker. Total buffer usage is the sum across all workers plus the leader. Timing is also reported per worker and can be misleading — wall-clock time reflects the longest-running worker, not the sum.
Note loops=5 — the query ran across 5 execution contexts (4 workers + 1 leader). Row counts and buffer counts must be multiplied by 5 to get totals.
Summary & Next Steps
You now have a complete diagnostic toolkit for SQL query profiling. Let's consolidate the mental model:
The diagnostic hierarchy:
Execution time tells you there's a problem
Plan tree structure tells you what strategy the planner chose
Estimated vs. actual rows tells you where the planner was wrong
Statistics currency explains why the planner was wrong
Buffer metrics tell you whether the problem is I/O bound or CPU/memory bound
Spill indicators tell you whether work_mem is a constraint
Loops multiplier tells you true cost of nested operations
The universal algorithm: find the deepest node with a large estimate-to-actual discrepancy, trace it to stale or insufficient statistics, fix the statistics, then check whether the plan structure improved. Repeat for the next problematic node.
Next steps in your learning journey:
Partition pruning diagnostics: How to read EXPLAIN ANALYZE output for partitioned tables and verify that partition pruning is actually occurring
Index internals: Understanding B-tree page splits, bloat, and when to REINDEX
Parallel query tuning: How max_parallel_workers_per_gather, min_parallel_table_scan_size, and relation size interact to control parallelism
pg_stat_statements: Aggregate query performance across thousands of executions to find your highest-impact optimization targets systemically
Wait event analysis: Combining EXPLAIN ANALYZE findings with pg_stat_activity wait events to diagnose lock contention and resource exhaustion
The goal isn't to memorize output formats — it's to build an internal model of how the planner thinks, so that when you see an unexpected plan, you immediately know what question to ask. Every slow query is a communication from your database telling you something is wrong with the information it has. Your job is to listen.
