PostgreSQL 18 EXPLAIN Improvements

PostgreSQL 18 makes EXPLAIN more useful for real-world performance troubleshooting by exposing more of what happens during query execution. Beyond plan shape and execution time, it provides better visibility into buffer activity, repeated index access, memory and disk usage, WAL behavior, parallel execution, and window-function processing. 

This makes it easier to identify the actual source of a performance issue and make more targeted tuning decisions. 

In this blog, we’ll explore the key EXPLAIN improvements in PostgreSQL 18 and see how they can help diagnose performance issues more effectively. 

What’s New in PostgreSQL 18 EXPLAIN 

1. Table of Contents

   • 1) BUFFERS Is Now Automatic with EXPLAIN ANALYZE
   • 2) Index Search Counts (Index Searches)

   1) BUFFERS Is Now Automatic with EXPLAIN ANALYZE 

   Before PostgreSQL 18, EXPLAIN ANALYZE did not show buffer stats unless you explicitly added BUFFERS. 

   EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5001; 
   EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 5001; 

   PostgreSQL 18 makes EXPLAIN ANALYZE more useful by including buffer statistics automatically. This helps reveal what execution time alone cannot: where the data is coming from. 

   shared hit=120 read=12500 → disk-heavy 

   shared hit=12500 read=120 → cache-heavy 

   When investigating performance, correlate time, rows/loops, and buffer activity. 

   The following example creates a large table with multiple rows per customer and uses an index to retrieve one customer’s rows. We then run the same EXPLAIN ANALYZE query in PostgreSQL 17 and PostgreSQL 18. 

   -- Setup 
    
   CREATE TABLE buffer_test (id INT, customer_id INT); 
   INSERT INTO buffer_test 
   SELECT x, (x % 10000) + 1 
   FROM generate_series(1,1000000) x; 
   CREATE INDEX ON buffer_test(customer_id); 
   
   VACUUM ANALYZE buffer_test; 
   
   EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) 
   
   SELECT * FROM buffer_test 
   
   WHERE customer_id = 5001; 

 

EXPLAIN  Output PG 17 

 Bitmap Heap Scan on buffer_test (actual rows=100 loops=1) 

 Recheck Cond: (customer_id = 5001) 

 Heap Blocks: exact=100 

  ->  Bitmap Index Scan on buffer_test_customer_id_idx (actual rows=100 loops=1) 

       Index Cond: (customer_id = 5001) 

Planning Time: 0.076 ms 

• Execution Time: 0.225 ms 
  (7rows) 

EXPLAIN  output PG 18 

Bitmap Heap Scan on buffer_test (actual rows=100.00 loops=1) 

  Recheck Cond: (customer_id = 5001) 

  Heap Blocks: exact=100 

  Buffers: shared hit=103 

  -> Bitmap Index Scan on buffer_test_customer_id_idx (actual rows=100.00 loops=1) 

       Index Cond: (customer_id = 5001) 

       Index Searches: 1 

       Buffers: shared hit=3 

Planning: 

  Buffers: shared hit=6 

Planning Time: 0.097 ms 

Execution Time: 0.142 ms 

(12 rows) 

 

In the PostgreSQL 18 output, focus on Buffers: shared hit and shared read to determine whether the query is primarily using cached pages or reading from storage. A high read count may indicate an I/O-heavy execution, while a high hit count indicates that most pages were already available in shared buffers. 

 A query with substantial physical reads may be more I/O-sensitive, while high shared hits indicate the required pages were already cached. 

 

2) Index Search Counts (Index Searches) 

PostgreSQL 18 adds Index Searches to show how many separate index searches were performed by an index scan. This is especially useful for nested loop joins, where an inner index scan may be searched repeatedly for rows produced by the outer side. 

The following example joins customers_idx with orders_idx. The query retrieves 10 customers, and for each customer PostgreSQL searches the orders index for matching rows. This makes it easy to see how PostgreSQL 18 exposes repeated index access that was not directly visible in PostgreSQL 17. 
 

-- Setup 
CREATE TABLE customers_idx (  id INT PRIMARY KEY ); 

CREATE TABLE orders_idx (   id INT,   customer_id INT ); 

INSERT INTO customers_idx 

SELECT x FROM generate_series(1,1000) x; 

INSERT INTO orders_idx 

SELECT x, (x % 1000) + 1 FROM generate_series(1,100000) x; 

CREATE INDEX orders_customer_idx 

ON orders_idx(customer_id); 

VACUUM ANALYZE customers_idx; 

VACUUM ANALYZE orders_idx; 


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) 

SELECT c.id, o.id FROM customers_idx c JOIN orders_idx o 

 ON o.customer_id = c.id  WHERE c.id <= 10; 

 

EXPLAIN Output PG 17 

 

Nested Loop (actual rows=1000 loops=1) 

  -> Index Only Scan using customers_idx_pkey on customers_idx c (actual rows=10 loops=1) 

       Index Cond: (id <= 10) 

       Heap Fetches: 0 

  -> Bitmap Heap Scan on orders_idx o (actual rows=100 loops=10) 

       Recheck Cond: (c.id = customer_id) 

       Heap Blocks: exact=1000 

       -> Bitmap Index Scan on orders_customer_idx (actual rows=100 loops=10) 

            Index Cond: (customer_id = c.id) 

Planning Time: 0.305 ms 

Execution Time: 0.666 ms 

(11 rows) 

EXPLAIN Output PG 18 

Nested Loop (actual rows=1000.00 loops=1) 

  Buffers: shared hit=1023 

  -> Index Only Scan using customers_idx_pkey on customers_idx c (actual rows=10.00 loops=1) 

       Index Cond: (id <= 10) 

       Heap Fetches: 0 

       Index Searches: 1 

       Buffers: shared hit=3 

  -> Bitmap Heap Scan on orders_idx o (actual rows=100.00 loops=10) 

       Recheck Cond: (c.id = customer_id) 

       Heap Blocks: exact=1000 

       Buffers: shared hit=1020 

       -> Bitmap Index Scan on orders_customer_idx (actual rows=100.00 loops=10) 

            Index Cond: (customer_id = c.id) 

            Index Searches: 10 

            Buffers: shared hit=20 

Planning: 

  Buffers: shared hit=77 read=3 

Planning Time: 0.362 ms 

Execution Time: 0.590 ms 

(19 rows) 
 

Compare Index Searches with loops, actual rows, and Buffers. Here, the inner index is searched 10 times, exposing the repeated index probes performed by the nested loop. 

A high Index Searches count can reveal expensive repeated index access and help determine whether the join strategy, indexing, or query structure should be reviewed. 

 

 3) Fractional Actual Row Counts 

EXPLAIN now supports fractional row averages for repeatedly executed nodes, for example: 

rows=0.25 loops=1000 

This is not “partial rows”; it is average output per execution. It helps reveal behavior in: 

• nested loops 

• parameterized scans 

• correlated subqueries 

• selective joins 

The following example uses a correlated subquery that executes repeatedly but returns a row only for a fraction of those executions. PostgreSQL 18 preserves fractional actual rows values, providing a more accurate picture of how often the node produces a result. 

 

-- Setup: 
CREATE TABLE frac_outer (id INT PRIMARY KEY); 

  

CREATE TABLE frac_inner (id INT PRIMARY KEY,value TEXT); 

  

INSERT INTO frac_outer 

SELECT x FROM generate_series(1,1000) x; 

-- Only 1 out of every 4 outer rows has a match 

 INSERT INTO frac_inner  
SELECT x, 'match' FROM generate_series(4,1000,4) x; 
 
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) 
SELECT o.id, 
(SELECT i.value FROM frac_inner i WHERE i.id = o.id) 
FROM frac_outer o; 

EXPLAIN Output PG 17: 

Seq Scan on frac_outer o (actual rows=1000 loops=1) 

  SubPlan 1 

    -> Index Scan using frac_inner_pkey on frac_inner i (actual rows=0 loops=1000) 

         Index Cond: (id = o.id) 

Planning Time: 0.082 ms 

Execution Time: 0.790 ms 
(6 rows)

 

EXPLAIN Output PG 18 

Seq Scan on frac_outer o (actual rows=1000.00 loops=1) 

  Buffers: shared hit=1255 

  SubPlan 1 

    -> Index Scan using frac_inner_pkey on frac_inner i (actual rows=0.25 loops=1000) 

         Index Cond: (id = o.id) 

         Index Searches: 1000 

         Buffers: shared hit=1250 

Planning: 

  Buffers: shared hit=27 

Planning Time: 0.190 ms 

Execution Time: 0.752 ms 

(11 rows) 

Compare actual rows with loops. An actual rows=0.25 over 1,000 loops indicates that the node returns a row on average once every four executions. 
This is useful when troubleshooting nested loops and correlated subqueries where rounding the average to zero can hide meaningful execution behavior. 

 

4) Better Memory/Disk Visibility for More Nodes 

PostgreSQL 18 extends memory and disk reporting to Material, Window Aggregate, and CTE-related nodes. This helps identify when growing intermediate results start using disk and cause query performance to degrade. 

Rather than immediately increasing work_mem, use EXPLAIN ANALYZE to determine which node is consuming resources and why. 

The following query calculates a running total over one million rows with work_mem intentionally limited to 1 MB. This creates memory pressure and causes the Sort operation to spill to disk, making it easier to see how PostgreSQL 18 identifies resource usage at individual plan nodes. 

-- Setup 
CREATE TABLE memory_test (id INT, dept_id INT, amount NUMERIC); 

 INSERT INTO memory_test 
SELECT x, (x % 10) + 1, random() * 1000 
FROM generate_series(1,1000000) x; 

 VACUUM ANALYZE memory_test; 

SET work_mem = '1MB'; 

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) 

SELECT id, 

       dept_id,amount, SUM(amount) OVER ( 

        PARTITION BY dept_id ORDER BY id) 

FROM memory_test; 

EXPLAIN Output PG 17 

WindowAgg (actual rows=1000000 loops=1) 

  -> Sort (actual rows=1000000 loops=1) 

       Sort Key: dept_id, id 

       Sort Method: external merge  Disk: 28592kB 

       -> Seq Scan on memory_test (actual rows=1000000 loops=1) 

Planning Time: 0.159 ms 

Execution Time: 1069.011 ms 

(7 rows) 

 

EXPLAIN Output PG 18 

WindowAgg (actual rows=1000000.00 loops=1) 

  Window: w1 AS (PARTITION BY dept_id ORDER BY id) 

  Storage: Memory Maximum Storage: 17kB 

  Buffers: shared hit=6371, temp read=10716 written=10781 

  -> Sort (actual rows=1000000.00 loops=1) 

       Sort Key: dept_id, id 

       Sort Method: external merge  Disk: 28592kB 

       Buffers: shared hit=6371, temp read=10716 written=10781 

       -> Seq Scan on memory_test (actual rows=1000000.00 loops=1) 

            Buffers: shared hit=6370 

Planning Time: 0.055 ms 

Execution Time: 1129.466 ms 

(12 rows) 

The WindowAgg uses only 17 KB, while the Sort spills about 28 MB to disk. This clearly identifies the Sort, rather than the WindowAgg, as the source of the disk spill. 

This additional visibility helps pinpoint the resource-intensive node before making tuning changes such as increasing work_mem. 

 

5) Better WAL Visibility (buffers full) 

For write-heavy statements, PostgreSQL already supports: 

EXPLAIN (ANALYZE, WAL) UPDATE ...; 

PostgreSQL 18 adds another signal: how often WAL buffers became full. Look at WAL as a set: 

• Records :  WAL records generated 

• Fpi : Full-page images written to WAL 

• Bytes : Total WAL volume generated 

• buffers full : Number of times WAL buffers became full 

• rows modified: Number of rows affected by the operation 

 

This is useful for bulk UPDATE/DELETE/INSERT diagnostics and replication-impact analysis. 
 
The following example updates 250,000 rows after a checkpoint, generating substantial WAL activity. 

 

-- Setup 
CREATE TABLE wal_test (id INT PRIMARY KEY,status INT,payload TEXT); 

 INSERT INTO wal_test 

SELECT x, 0, repeat('x', 500) FROM generate_series(1,500000) x; 

 VACUUM ANALYZE wal_test; 

 CHECKPOINT; 

 EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF) 

UPDATE wal_test SET status = 1 WHERE id <= 250000; 

EXPLAIN Output PG 17 

Update on wal_test (actual rows=0 loops=1) 

  WAL: records=753424 fpi=17365 bytes=187303863 

  -> Index Scan using wal_test_pkey on wal_test (actual rows=250000 loops=1) 

       Index Cond: (id <= 250000) 

Planning Time: 0.231 ms 

Execution Time: 2105.951 ms 

(6 rows) 

 

EXPLAIN Output PG 18 

Update on wal_test (actual rows=0.00 loops=1) 

  Buffers: shared hit=1821461 dirtied=34716 written=17355 

  WAL: records=753424 fpi=17365 bytes=184498617 buffers full=11293 

  -> Index Scan using wal_test_pkey on wal_test (actual rows=250000.00 loops=1) 

       Index Cond: (id <= 250000) 

       Index Searches: 1 

       Buffers: shared hit=17353 

Planning: 

  Buffers: shared hit=14 dirtied=1 

Planning Time: 0.187 ms 

Execution Time: 2149.051 ms 

(11 rows) 

 Check buffers full alongside WAL records, FPIs, and bytes to identify how often WAL buffers became full during the write operation. 

 In PostgreSQL 18, inspect records, fpi, bytes, and buffers full together with the number of affected rows. These values help assess the WAL cost of a bulk operation and its possible effect on write throughput, storage, and replication. Frequent buffers full events can indicate sustained WAL pressure, but should be evaluated alongside total WAL volume and execution behavior 

6) Improved Parallel Bitmap Heap Scan Stats 

PostgreSQL 18 adds worker cache statistics for parallel bitmap heap scans, improving per-worker visibility in parallel plans. When analyzing parallel execution, go beyond Workers Planned and Workers Launched and correlate worker-level behavior with buffers and row flow. 
 
The following example uses a Parallel Bitmap Heap Scan to retrieve matching rows through multiple processes. PostgreSQL 18 exposes additional worker-level statistics, allowing the activity of individual workers to be compared with the overall parallel scan. 

 

-- Setup 
CREATE TABLE pb_test AS SELECT x AS id, x % 10 AS category 

FROM generate_series(1,100000) x; 

  
CREATE INDEX pb_test_category_idx ON pb_test(category); 

  
VACUUM ANALYZE pb_test; 

SET max_parallel_workers_per_gather = 2; 
SET min_parallel_table_scan_size = 0; 
SET parallel_setup_cost = 0; 
SET parallel_tuple_cost = 0; 
SET enable_seqscan = off; 
SET enable_indexscan = off; 

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS OFF, TIMING OFF) 

SELECT *  FROM pb_test 
WHERE category < 3; 

 

EXPLAIN Output PG 17 

 

 

Gather (actual rows=30000 loops=1) 

  Output: id, category 

  Workers Planned: 2 

  Workers Launched: 1 

  Buffers: shared hit=443 read=27 

  -> Parallel Bitmap Heap Scan on public.pb_test (actual rows=15000 loops=2) 

       Output: id, category 

       Recheck Cond: (pb_test.category < 3) 

       Heap Blocks: exact=443 

       Buffers: shared hit=443 read=27 

       Worker 0: actual rows=0 loops=1 

       -> Bitmap Index Scan on pb_test_category_idx (actual rows=30000 loops=1) 

            Index Cond: (pb_test.category < 3) 

            Buffers: shared read=27 

Query Identifier: 6421728296517627451 

Planning: 

  Buffers: shared hit=9 

Planning Time: 0.065 ms 

Execution Time: 12.218 ms 

 

(19 rows) 

 

EXPLAIN Output PG 18 

Gather (actual rows=30000.00 loops=1) 

  Output: id, category 

  Workers Planned: 2 

  Workers Launched: 1 

  Buffers: shared hit=445 read=27 

  -> Parallel Bitmap Heap Scan on public.pb_test (actual rows=15000.00 loops=2) 

       Output: id, category 

       Recheck Cond: (pb_test.category < 3) 

       Heap Blocks: exact=443 

       Buffers: shared hit=445 read=27 

       Worker 0: actual rows=0.00 loops=1 

         Buffers: shared hit=2 

       -> Bitmap Index Scan on pb_test_category_idx (actual rows=30000.00 loops=1) 

            Index Cond: (pb_test.category < 3) 

            Index Searches: 1 

            Buffers: shared read=27 

Query Identifier: 6365259442062613493 

Planning: 

  Buffers: shared hit=9 

Planning Time: 0.085 ms 

Execution Time: 17.366 ms 

(21 rows) 

 

Workers Planned and Workers Launched alone do not show how much useful work each worker performed. Worker-level statistics can reveal cases where work distribution is uneven or a launched worker contributes very little. 

7) Disabled Plan Nodes Are Explicitly Identified 

When experimenting with planner toggles: 

SET enable_seqscan = off; 
SET enable_hashjoin = off; 
SET enable_nestloop = off; 

PostgreSQL 18 can indicate disabled nodes more clearly in plan output, making “what-if” diagnostics easier to interpret. 
 
The following example disables sequential scans and executes a leading-wildcard search that has no suitable B-tree index access path. PostgreSQL still needs to perform a sequential scan, but PostgreSQL 18 makes this explicit in the plan with Disabled: true. 

 

-- Setup 

CREATE TABLE disabled_test ( id INT, notes TEXT); 

 INSERT INTO disabled_test 
SELECT x,  
CASE 
WHEN x % 100 = 0 THEN 'order checked manually' 
ELSE 'normal order' 
END 
FROM generate_series(1,100000) x; 

  VACUUM ANALYZE disabled_test; 

  SET enable_seqscan = off; 
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) 
SELECT * FROM disabled_test WHERE notes LIKE '%checked%'; 

 

EXPLAIN Output PG 17: 

Seq Scan on disabled_test (actual rows=1000 loops=1) 

  Filter: (notes ~~ '%checked%'::text) 

  Rows Removed by Filter: 99000 

Planning Time: 0.065 ms 

Execution Time: 7.045 ms 

 

(5 rows) 

 

EXPLAIN Output PG 18: 

Seq Scan on disabled_test (actual rows=1000.00 loops=1) 

  Disabled: true 

  Filter: (notes ~~ '%checked%'::text) 

  Rows Removed by Filter: 99000 

  Buffers: shared hit=642 

Planning: 

  Buffers: shared hit=8 

Planning Time: 0.096 ms 

Execution Time: 6.992 ms 

 

(9 rows) 

 
This makes planner experiments easier to interpret because you can distinguish between a normally selected plan node and one PostgreSQL used even though that planner method had been disabled. 

 

8) Better Window Function Visibility 

Plans now expose additional details about window-function arguments. Combined with richer Window Aggregate memory/disk reporting, this improves troubleshooting for analytical workloads using: 

• ROW_NUMBER() 

• RANK() 

• SUM() OVER (...) 

• AVG() OVER (...) 

• LAG() / LEAD() 

 

The following example uses LAG() to retrieve the previous value within each department. PostgreSQL 18 exposes more information about the window itself, making it easier to associate the window function with its partitioning, ordering, and resource usage. 

 

-- Setup 

CREATE TABLE window_test (id INT,dept_id INT,amount NUMERIC); 

INSERT INTO window_test VALUES 

(1, 1, 100), (2, 1, 200), (3, 1, 300), (4, 2, 150), (5, 2, 250); 

 VACUUM ANALYZE window_test; 
 
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF) 

SELECT id, dept_id,amount, 

       LAG(amount, 1, 0) OVER ( 

           PARTITION BY dept_id 

           ORDER BY id 

       ) AS previous_amount 

FROM window_test; 

 

EXPLAIN Output PG 17 

WindowAgg (actual rows=5 loops=1) 

  Output: id, dept_id, amount, lag(amount, 1, '0'::numeric) OVER (?) 

  -> Sort (actual rows=5 loops=1) 

       Output: id, dept_id, amount 

       Sort Key: window_test.dept_id, window_test.id 

       Sort Method: quicksort  Memory: 25kB 

       -> Seq Scan on public.window_test (actual rows=5 loops=1) 

            Output: id, dept_id, amount 

Query Identifier: -674688654798365229 

Planning Time: 0.083 ms 

Execution Time: 0.038 ms 

(11 rows)

 

EXPLAIN Output PG 18 

WindowAgg (actual rows=5.00 loops=1) 

  Output: id, dept_id, amount, lag(amount, 1, '0'::numeric) OVER w1 

  Window: w1 AS (PARTITION BY window_test.dept_id ORDER BY window_test.id) 

  Storage: Memory Maximum Storage: 17kB 

  Buffers: shared hit=38 

  -> Gather Merge (actual rows=5.00 loops=1) 

       Output: id, dept_id, amount 

       Workers Planned: 1 

       Workers Launched: 1 

       Buffers: shared hit=38 

       -> Sort (actual rows=2.50 loops=2) 

            Output: id, dept_id, amount 

            Sort Key: window_test.dept_id, window_test.id 

            Sort Method: quicksort  Memory: 25kB 

            Buffers: shared hit=38 

            Worker 0: actual rows=0.00 loops=1 

              Sort Method: quicksort  Memory: 25kB 

              Buffers: shared hit=37 

            -> Parallel Seq Scan on public.window_test (actual rows=2.50 loops=2) 

                 Disabled: true 

                 Output: id, dept_id, amount 

                 Buffers: shared hit=1 

                 Worker 0: actual rows=0.00 loops=1 

Query Identifier: -8082484858136532801 

Planning: 

  Buffers: shared hit=13 

Planning Time: 0.106 ms 

Execution Time: 13.623 ms 

(28 rows)

 
 

 Check the Window, Output, and Storage fields to understand the window definition, function arguments, and WindowAgg memory usage. Instead of displaying only OVER (?), PostgreSQL 18 makes it easier to identify the applicable window definition and correlate it with resource consumption. 

 

PostgreSQL 17 and Earlier vs PostgreSQL 18 

 

Conclusion 

With PostgreSQL 18, EXPLAIN provides deeper visibility into query execution through automatic buffer statistics, index search counts, fractional row reporting, improved memory and WAL details, worker-level cache statistics, and clearer window-function information. These enhancements make it easier to identify bottlenecks and make evidence-based tuning decisions.