EXPLAIN is one of the most valuable tools for understanding how Postgres executes SQL queries. It helps to see the execution plan chosen by the query planner, making it easier to understand why a query performs the way it does.
By using different EXPLAIN options, we can examine estimated costs, actual execution statistics, memory usage, buffer activity, I/O operations, WAL generation, serialization overhead, and planner settings.
This blog demonstrates every EXPLAIN option with practical examples, allowing you to understand what each option does and when it should be used while tuning and troubleshooting PostgreSQL queries.
You can see the available options that can be used with the explain command like this.
\h explain
Result:
Command: EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
SETTINGS [ boolean ]
GENERIC_PLAN [ boolean ]
BUFFERS [ boolean ]
SERIALIZE [ { NONE | TEXT | BINARY } ]
WAL [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
MEMORY [ boolean ]
IO [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
Now, create some test tables to explore all the explain options with complete query examples.
CREATE TABLE demo_partner (
id serial PRIMARY KEY,
name text NOT NULL,
country text NOT NULL
);
CREATE TABLE demo_order (
id bigserial PRIMARY KEY,
partner_id int NOT NULL REFERENCES demo_partner(id),
state text NOT NULL, -- draft / sale / done / cancel
amount_total numeric(12,2) NOT NULL,
note text,
create_date timestamp NOT NULL
);
CREATE TABLE demo_order_line (
id bigserial PRIMARY KEY,
order_id bigint NOT NULL REFERENCES demo_order(id),
product text NOT NULL,
qty int NOT NULL,
price_unit numeric(12,2) NOT NULL
);
Now, insert huge values into each table like this.
INSERT INTO demo_partner (name, country)
SELECT 'Partner ' || g,
(ARRAY['IN','US','DE','FR','AE'])[1 + g % 5]
FROM generate_series(1, 10000) g;
INSERT INTO demo_order (partner_id, state, amount_total, note, create_date)
SELECT 1 + (g % 10000),
(ARRAY['draft','sale','done','cancel'])[1 + g % 4],
round((random() * 10000)::numeric, 2),
md5(g::text) || md5((g * 7)::text) || md5((g * 13)::text),
timestamp '2025-01-01' + (g % 600) * interval '1 day'
FROM generate_series(1, 1000000) g;
INSERT INTO demo_order_line (order_id, product, qty, price_unit)
SELECT 1 + (g % 1000000),
'Product ' || (g % 500),
1 + g % 10,
round((random() * 500)::numeric, 2)
FROM generate_series(1, 2000000) g;
Now, create some simple btree index on these tables to get better performance.
CREATE INDEX demo_order_partner_idx ON demo_order (partner_id);
CREATE INDEX demo_order_state_idx ON demo_order (state);
CREATE INDEX demo_order_cdate_idx ON demo_order (create_date);
CREATE INDEX demo_line_order_idx ON demo_order_line (order_id);
Execute a vacuum analyze to help the planner to get the latest statistics about these tables.
VACUUM ANALYZE demo_partner, demo_order, demo_order_line;
Create the extension named pg_buffercache to explore more details related to query execution.
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
We can check the complete functionality of this pg_buffercache extension like this.
\dx+ pg_buffercache
Result:
Objects in extension "pg_buffercache"
Object description
--------------------------------------------------
function pg_buffercache_evict_all()
function pg_buffercache_evict(integer)
function pg_buffercache_evict_relation(regclass)
function pg_buffercache_numa_pages()
function pg_buffercache_pages()
function pg_buffercache_summary()
function pg_buffercache_usage_counts()
type pg_buffercache
type pg_buffercache[]
type pg_buffercache_numa
type pg_buffercache_numa[]
view pg_buffercache
view pg_buffercache_numa
(13 rows)
1. Plain EXPLAIN
EXPLAIN
SELECT * FROM demo_order WHERE partner_id = 42;
Result:
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=5.19..381.74 rows=99 width=128)
Recheck Cond: (partner_id = 42)
-> Bitmap Index Scan on demo_order_partner_idx (cost=0.00..5.17 rows=99 width=0)
Index Cond: (partner_id = 42)
(4 rows)
This is just an execution plan only, and it does not execute the query. This execution plan shows that Postgres uses a Bitmap Index scan on the demo_order_partner_idx index to find all rows where partner_id = 42.
Then, it performs a Bitmap Heap Scan to fetch the corresponding rows from the demo_order table. The planner estimates that about 99 rows will be returned, with an average row size of 128 bytes.
2. ANALYZE
In postgres 18, BUFFERS is included automatically with ANALYZE
EXPLAIN (ANALYZE)
SELECT * FROM demo_order WHERE partner_id = 42;
Result:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=5.19..381.74 rows=99 width=128) (actual time=0.201..0.841 rows=100.00 loops=1)
Recheck Cond: (partner_id = 42)
Heap Blocks: exact=100
Buffers: shared read=103
-> Bitmap Index Scan on demo_order_partner_idx (cost=0.00..5.17 rows=99 width=0) (actual time=0.042..0.043 rows=100.00 loops=1)
Index Cond: (partner_id = 42)
Index Searches: 1
Buffers: shared read=3
Planning Time: 0.099 ms
Execution Time: 1.006 ms
(10 rows)
Unlike plain EXPLAIN, EXPLAIN (ANALYZE) actually executes the query and displays the real execution statistics along with the estimated plan, like the above one.
In this example, it uses the same Bitmap Index scan followed by a bitmap heap scan to retrieve the matching rows. The planner estimated 99 rows, while the query actually returned 100 rows in about 1.006 ms.
The output also shows the actual execution time for each plan node, the number of loops, the heap blocks accessed, the shared buffers read, the planning time, and the total execution time, making it useful for comparing estimated versus actual query performance.
ANALYZE false is the same as plain EXPLAIN.
EXPLAIN (ANALYZE false)
SELECT * FROM demo_order WHERE partner_id = 42;
Result:
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=5.19..381.74 rows=99 width=128)
Recheck Cond: (partner_id = 42)
-> Bitmap Index Scan on demo_order_partner_idx (cost=0.00..5.17 rows=99 width=0)
Index Cond: (partner_id = 42)
(4 rows)
3. VERBOSE
EXPLAIN (VERBOSE)
SELECT o.id, p.name, o.amount_total
FROM demo_order o
JOIN demo_partner p ON p.id = o.partner_id
WHERE o.state = 'sale' AND p.country = 'IN'
LIMIT 20;
Result:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=0.30..17.54 rows=20 width=26)
Output: o.id, p.name, o.amount_total
-> Nested Loop (cost=0.30..42083.74 rows=48800 width=26)
Output: o.id, p.name, o.amount_total
Inner Unique: true
-> Seq Scan on public.demo_order o (cost=0.00..32908.00 rows=244000 width=18)
Output: o.id, o.partner_id, o.state, o.amount_total, o.note, o.create_date
Filter: (o.state = 'sale'::text)
-> Memoize (cost=0.30..0.32 rows=1 width=16)
Output: p.name, p.id
Cache Key: o.partner_id
Cache Mode: logical
-> Index Scan using demo_partner_pkey on public.demo_partner p (cost=0.29..0.31 rows=1 width=16)
Output: p.name, p.id
Index Cond: (p.id = o.partner_id)
Filter: (p.country = 'IN'::text)
Query Identifier: -5093509354952238597
(17 rows)
EXPLAIN (VERBOSE) provides a more detailed execution plan than a plain EXPLAIN.
In addition to the execution plan, it displays the output columns produced by each plan node, schema-qualified table names, cache information, and other internal details.
In this example, postgres performs a sequential scan on the demo_order table, and it uses a Nested Loop join to match rows from demo_partner and a memoize node to cache repeated lookups of the same partner_id.
The output also includes the query identifier and detailed information about each operation, making it useful for understanding exactly how the query is executed.
4. COSTS
EXPLAIN (COSTS false)
SELECT state, count(*) FROM demo_order GROUP BY state;
Result:
QUERY PLAN
-------------------------------------------------------------------------------------
Finalize GroupAggregate
Group Key: state
-> Gather Merge
Workers Planned: 2
-> Partial GroupAggregate
Group Key: state
-> Parallel Index Only Scan using demo_order_state_idx on demo_order
(7 rows)
EXPLAIN (COSTS false) displays the execution plan without showing the estimated startup and total costs. This makes the output shorter and easier to read when you only want to understand the execution strategy.
In this example, Postgres performs a Parallel Index only scan on the demo_order_state_idx index, aggregates the results in parallel using Partial GroupAggregate, merges the results with Gather Merge, and finally produces the grouped counts using Finalize GroupAggregate.
EXPLAIN (COSTS true)
SELECT state, count(*) FROM demo_order GROUP BY state;
Result:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=1000.45..15647.73 rows=4 width=13)
Group Key: state
-> Gather Merge (cost=1000.45...). 15647.64 rows=10 width=13)
Workers Planned: 2
-> Partial GroupAggregate (cost=0.42..14646.47 rows=4 width=13)
Group Key: state
-> Parallel Index Only Scan using demo_order_state_idx on demo_order (cost=0.42..12563.09 rows=416667 width=5)
(7 rows)
EXPLAIN (COSTS true) displays the execution plan along with the planner's estimated startup and total costs for each operation. These cost values help the postgres to choose the most efficient execution plan but do not represent actual execution time.
Here, the postgres uses a parallel index-only scan on the index named demo_order_state_idx, performs partial aggregation in parallel, merges the results using Gather Merge, and completes the aggregation with Finalize group aggregate, while showing the estimated costs for every plan node.
5. SETTINGS
Inside a transaction, set the values for the parameter named work_mem, random_page_cost and enable_hashjoin like this.
BEGIN;
SET LOCAL work_mem = '256MB';
SET LOCAL random_page_cost = 1.1;
SET LOCAL enable_hashjoin = off;
Now, execute the below query with set settings as the explain command option like this.
EXPLAIN (SETTINGS)
SELECT o.id, l.product
FROM demo_order o
JOIN demo_order_line l ON l.order_id = o.id
WHERE o.partner_id = 42;
Result:
QUERY PLAN
----------------------------------------------------------------------------------------------------
Nested Loop (cost=0.85..458.97 rows=198 width=19)
-> Index Scan using demo_order_partner_idx on demo_order o (cost=0.42..112.16 rows=99 width=8)
Index Cond: (partner_id = 42)
-> Index Scan using demo_line_order_idx on demo_order_line l (cost=0.43..3.48 rows=2 width=19)
Index Cond: (order_id = o.id)
Settings: work_mem = '256MB', random_page_cost = '1.1', enable_hashjoin = 'off'
(6 rows)
EXPLAIN (SETTINGS) displays the execution plan along with any planner-related configuration parameters that have been changed from their default values and influenced the plan.
In this plan, it uses a Nested Loop join with index scans on both tables. The output also shows the modified settings like work_mem = '256MB', random_page_cost = '1.1', and enable_hashjoin = 'off', and these make it easy to identify the configuration changes that affected the planner's decision.
Now, execute the rollback command to exit from this transaction.
ROLLBACK;
6. GENERIC_PLAN
EXPLAIN (GENERIC_PLAN)
SELECT * FROM demo_order
WHERE partner_id = $1 AND create_date >= $2;
Result:
QUERY PLAN
---------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=5.18..385.68 rows=33 width=128)
Recheck Cond: (partner_id = $1)
Filter: (create_date >= $2)
-> Bitmap Index Scan on demo_order_partner_idx (cost=0.00..5.17 rows=100 width=0)
Index Cond: (partner_id = $1)
(5 rows)
EXPLAIN (GENERIC_PLAN) shows the generic execution plan for a parameterized query without requiring actual parameter values. Instead of generating a plan based on specific values, postgres creates a reusable plan using placeholders such as $1 and $2.
In this example, postgres chooses a Bitmap Index scan on demo_order_partner_idx followed by a Bitmap Heap Scan, applying the create_date condition as a filter.
This option is useful for examining how prepared statements are planned before any parameter values are supplied.
7. BUFFERS
EXPLAIN (BUFFERS)
SELECT * FROM demo_order WHERE partner_id = 42;
Result:
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=5.19..381.74 rows=99 width=128)
Recheck Cond: (partner_id = 42)
-> Bitmap Index Scan on demo_order_partner_idx (cost=0.00..5.17 rows=99 width=0)
Index Cond: (partner_id = 42)
(4 rows)
EXPLAIN (BUFFERS) reports buffer usage during query planning and execution. However, when ANALYZE is not specified, the query is not executed, so only the execution plan is displayed and no buffer statistics are shown.
To view details such as shared buffer hits, reads, dirtied pages, or written pages, BUFFERS should be used together with ANALYZE.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM demo_order WHERE amount_total > 9000;
Result:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=26719.04..26719.05 rows=1 width=8) (actual time=129.621..132.469 rows=1.00 loops=1)
Buffers: shared hit=100 read=20308
-> Gather (cost=26718.83..26719.04 rows=2 width=8) (actual time=128.182..132.447 rows=3.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=100 read=20308
-> Partial Aggregate (cost=25718.83..25718.84 rows=1 width=8) (actual time=124.761..124.768 rows=1.00 loops=3)
Buffers: shared hit=100 read=20308
-> Parallel Seq Scan on demo_order (cost=0.00..25616.33 rows=40998 width=0) (actual time=0.080..71.869 rows=33425.33 loops=3)
Filter: (amount_total > '9000'::numeric)
Rows Removed by Filter: 299908
Buffers: shared hit=100 read=20308
Planning:
Buffers: shared hit=3
Planning Time: 0.126 ms
Execution Time: 132.505 ms
EXPLAIN (ANALYZE, BUFFERS) executes the query and displays both the actual execution statistics and buffer usage.
In this plan, it performs a parallel sequential scan on demo_order, filters rows where amount_total > 9000, and then computes the count using parallel aggregation.
The output also shows buffer statistics, including shared hit=100 (pages found in memory) and shared read=20308 (pages read from disk), along with the planning time and total execution time. This option is useful for analyzing both query performance and memory/disk I/O behaviour.
Now, force a sort that spills to disk to see "temp read/written" by set a low value to the parameter named work_mem like this.
BEGIN;
SET LOCAL work_mem = '64kB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM demo_order ORDER BY note LIMIT 100000;
Result:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=177825.24..189471.89 rows=100000 width=128) (actual time=1875.506..2376.493 rows=100000.00 loops=1)
Buffers: shared hit=76 read=21005, temp read=73240 written=95687
-> Gather Merge (cost=177825.24..297698.60 rows=1029252 width=128) (actual time=1873.933..2125.179 rows=100000.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=76 read=21005, temp read=73240 written=95687
-> Sort (cost=176825.22..177897.36 rows=428855 width=128) (actual time=1859.238..1906.652 rows=33574.67 loops=3)
Sort Key: note
Sort Method: external merge Disk: 48088kB
Buffers: shared hit=76 read=21005, temp read=73240 written=95687
Worker 0: Sort Method: external merge Disk: 47416kB
Worker 1: Sort Method: external merge Disk: 47472kB
-> Parallel Seq Scan on demo_order (cost=0.00..25293.55 rows=428855 width=128) (actual time=0.244..496.844 rows=333333.33 loops=3)
Buffers: shared read=21005
Planning:
Buffers: shared hit=180 read=12
Planning Time: 0.542 ms
JIT:
Functions: 1
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.055 ms (Deform 0.000 ms), Inlining 0.000 ms, Optimization 0.098 ms, Emission 1.466 ms, Total 1.619 ms
Execution Time: 2521.456 ms
(22 rows)
In this example, work_mem is set to 64kB, which is too small to perform the sort entirely in memory. As a result, postgres uses an external merge sort, spilling the sort operation to temporary disk files.
The execution plan shows temp read and temp written buffer statistics along with the Sort Method named external merge and the amount of disk space used for sorting. This demonstrates how insufficient work_mem can increase disk I/O and execution time, making EXPLAIN (ANALYZE, BUFFERS) a useful tool for identifying queries that could benefit from a larger work_mem setting.
8. SERIALIZE
EXPLAIN (ANALYZE, SERIALIZE) -- default = TEXT
SELECT id, note FROM demo_order WHERE state = 'done';
Result:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=2828.53..26404.44 rows=253433 width=105) (actual time=9.712..400.358 rows=250000.00 loops=1)
Recheck Cond: (state = 'done'::text)
Heap Blocks: exact=20408
Buffers: shared hit=73 read=20548 written=4
-> Bitmap Index Scan on demo_order_state_idx (cost=0.00..2765.17 rows=253433 width=0) (actual time=7.459..7.461 rows=250000.00 loops=1)
Index Cond: (state = 'done'::text)
Index Searches: 1
Buffers: shared read=213
Planning:
Buffers: shared hit=145 read=3
Planning Time: 0.475 ms
Serialization: time=354.055 ms output=27317kB format=text
Execution Time: 1433.079 ms
(13 rows)
EXPLAIN (ANALYZE, SERIALIZE) executes the query and, in addition to the execution statistics, measures the time required to serialize the query result before sending it to the client.
In this example, postgres retrieves 250,000 rows using a Bitmap Index scan and Bitmap heap Scan. The Serialization section shows that converting the result to text format took 354.055 ms and produced approximately 27 MB of output. This option is useful for measuring the overhead of preparing large result sets for client transmission.
EXPLAIN (ANALYZE, SERIALIZE TEXT)
SELECT id, note FROM demo_order WHERE state = 'done';
Result:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=2828.53..26404.44 rows=253433 width=105) (actual time=12.690..431.151 rows=250000.00 loops=1)
Recheck Cond: (state = 'done'::text)
Heap Blocks: exact=20408
Buffers: shared hit=49 read=20572 written=5
-> Bitmap Index Scan on demo_order_state_idx (cost=0.00..2765.17 rows=253433 width=0) (actual time=8.606..8.608 rows=250000.00 loops=1)
Index Cond: (state = 'done'::text)
Index Searches: 1
Buffers: shared read=213
Planning Time: 0.123 ms
Serialization: time=385.461 ms output=27317kB format=text
Execution Time: 1525.417 ms
(11 rows)
EXPLAIN (ANALYZE, SERIALIZE TEXT) explicitly measures the time required to serialize the query result in text format before it is sent to the client.
In this plan, it retrieves the matching rows using a Bitmap Index Scan and Bitmap Heap Scan.
The Serialization section shows that converting the result to text format took 385.461 ms and generated approximately 27 MB of output. Since TEXT is the default serialization format, this produces the same type of serialization statistics as EXPLAIN (ANALYZE, SERIALIZE).
EXPLAIN (ANALYZE, SERIALIZE BINARY)
SELECT id, note FROM demo_order WHERE state = 'done';
Result:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=2828.53..26404.44 rows=253433 width=105) (actual time=11.099..358.176 rows=250000.00 loops=1)
Recheck Cond: (state = 'done'::text)
Heap Blocks: exact=20408
Buffers: shared read=20621 written=31
-> Bitmap Index Scan on demo_order_state_idx (cost=0.00..2765.17 rows=253433 width=0) (actual time=8.018..8.020 rows=250000.00 loops=1)
Index Cond: (state = 'done'::text)
Index Searches: 1
Buffers: shared read=213
Planning Time: 0.087 ms
Serialization: time=324.604 ms output=27833kB format=binary
Execution Time: 1292.288 ms
(11 rows)
EXPLAIN (ANALYZE, SERIALIZE BINARY) executes the query and measures the time required to serialize the result in binary format before sending it to the client.
Here, it retrieves the matching rows using a Bitmap Index Scan and Bitmap Heap Scan. The Serialization section shows that converting the result to binary format took 324.604 ms and produced approximately 27.8 MB of output. Compared to text serialization, binary serialization is typically faster because it avoids converting values into their textual representation.
EXPLAIN (ANALYZE, SERIALIZE NONE)
SELECT id, note FROM demo_order WHERE state = 'done';
Result:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=2828.53..26404.44 rows=253433 width=105) (actual time=9.388..353.991 rows=250000.00 loops=1)
Recheck Cond: (state = 'done'::text)
Heap Blocks: exact=20408
Buffers: shared read=20621 written=1
-> Bitmap Index Scan on demo_order_state_idx (cost=0.00..2765.17 rows=253433 width=0) (actual time=6.766..6.768 rows=250000.00 loops=1)
Index Cond: (state = 'done'::text)
Index Searches: 1
Buffers: shared read=213
Planning Time: 0.074 ms
Execution Time: 663.897 ms
(10 rows)
EXPLAIN (ANALYZE, SERIALIZE NONE) executes the query without measuring the serialization cost.
As a result, the output does not include a Serialization section, and the reported execution time reflects only the query execution itself.
9. WAL
Execute CHECKPOINT first so the first touch of each page produces an FPI (Full Page Image).
CHECKPOINT;
Execute the explain command with the wal option like this.
BEGIN;
EXPLAIN (ANALYZE, WAL)
UPDATE demo_order
SET state = 'done', amount_total = amount_total * 1.05
WHERE partner_id BETWEEN 1 AND 200;
Result:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Update on demo_order (cost=283.04..21809.36 rows=0 width=0) (actual time=338.895..338.903 rows=0.00 loops=1)
Buffers: shared hit=341910 read=1038 dirtied=1956 written=571
WAL: records=121040 fpi=1385 bytes=21368591 buffers full=1577
-> Bitmap Heap Scan on demo_order (cost=283.04..21809.36 rows=20548 width=54) (actual time=1.145..38.412 rows=20000.00 loops=1)
Recheck Cond: ((partner_id >= 1) AND (partner_id <= 200))
Heap Blocks: exact=505
Buffers: shared hit=377 read=147
-> Bitmap Index Scan on demo_order_partner_idx (cost=0.00..277.91 rows=20548 width=0) (actual time=0.942..0.944 rows=20000.00 loops=1)
Index Cond: ((partner_id >= 1) AND (partner_id <= 200))
Index Searches: 1
Buffers: shared hit=3 read=16
Planning:
Buffers: shared hit=15 read=14
Planning Time: 0.338 ms
Execution Time: 341.002 ms
(15 rows)
EXPLAIN (ANALYZE, WAL) executes the query and reports the amount of Write-Ahead Log (WAL) generated during the operation.
Based on this plan, it updates 20,000 rows using a Bitmap Index Scan and Bitmap Heap Scan. The output includes a WAL section showing the number of WAL records generated, full-page images (FPIs), total WAL bytes written, and the number of times WAL buffers became full.
This option is useful for analyzing the WAL overhead of write operations such as INSERT, UPDATE, and DELETE, especially when evaluating replication or write performance.
10. TIMING:
EXPLAIN (ANALYZE, TIMING true)
SELECT p.country, sum(o.amount_total)
FROM demo_order o JOIN demo_partner p ON p.id = o.partner_id
GROUP BY p.country;
Result:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=29853.20..29854.75 rows=5 width=35) (actual time=2014.018..2017.075 rows=5.00 loops=1)
Group Key: p.country
Buffers: shared hit=13780 read=7433 dirtied=19336 written=6396
-> Gather Merge (cost=29853.20..29854.59 rows=12 width=35) (actual time=2013.996..2017.029 rows=15.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=13780 read=7433 dirtied=19336 written=6396
-> Sort (cost=28853.17..28853.18 rows=5 width=35) (actual time=2008.060..2008.087 rows=5.00 loops=3)
Sort Key: p.country
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=13780 read=7433 dirtied=19336 written=6396
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=28853.05..28853.11 rows=5 width=35) (actual time=2008.014..2008.039 rows=5.00 loops=3)
Group Key: p.country
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=13764 read=7433 dirtied=19336 written=6396
Worker 0: Batches: 1 Memory Usage: 32kB
Worker 1: Batches: 1 Memory Usage: 32kB
-> Hash Join (cost=289.00..26708.78 rows=428855 width=9) (actual time=31.570..1515.323 rows=333333.33 loops=3)
Hash Cond: (o.partner_id = p.id)
Buffers: shared hit=13764 read=7433 dirtied=19336 written=6396
-> Parallel Seq Scan on demo_order o (cost=0.00..25293.55 rows=428855 width=10) (actual time=0.147..504.580 rows=333333.33 loops=3)
Buffers: shared hit=13572 read=7433 dirtied=19336 written=6396
-> Hash (cost=164.00..164.00 rows=10000 width=7) (actual time=31.322..31.326 rows=10000.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
Buffers: shared hit=192
-> Seq Scan on demo_partner p (cost=0.00..164.00 rows=10000 width=7) (actual time=0.025..15.629 rows=10000.00 loops=3)
Buffers: shared hit=192
Planning:
Buffers: shared hit=135 read=19 dirtied=2
Planning Time: 0.456 ms
Execution Time: 2017.146 ms
(33 rows)
EXPLAIN (ANALYZE, TIMING true) executes the query and reports the actual execution time for every plan node.
In this example, Postgres uses a Parallel Sequential Scan, Hash Join, Partial HashAggregate, and Finalize GroupAggregate to calculate the total order amount grouped by country. The output includes the time spent in each operation, along with buffer usage, memory consumption, and the overall planning and execution times. This option is useful for identifying which parts of a query consume the most execution time and may need optimization.
EXPLAIN (ANALYZE, TIMING false)
SELECT p.country, sum(o.amount_total)
FROM demo_order o JOIN demo_partner p ON p.id = o.partner_id
GROUP BY p.country;
Result:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=29853.20..29854.75 rows=5 width=35) (actual rows=5.00 loops=1)
Group Key: p.country
Buffers: shared hit=14490 read=6723
-> Gather Merge (cost=29853.20..29854.59 rows=12 width=35) (actual rows=15.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=14490 read=6723
-> Sort (cost=28853.17..28853.18 rows=5 width=35) (actual rows=5.00 loops=3)
Sort Key: p.country
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=14490 read=6723
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=28853.05..28853.11 rows=5 width=35) (actual rows=5.00 loops=3)
Group Key: p.country
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=14474 read=6723
Worker 0: Batches: 1 Memory Usage: 32kB
Worker 1: Batches: 1 Memory Usage: 32kB
-> Hash Join (cost=289.00..26708.78 rows=428855 width=9) (actual rows=333333.33 loops=3)
Hash Cond: (o.partner_id = p.id)
Buffers: shared hit=14474 read=6723
-> Parallel Seq Scan on demo_order o (cost=0.00..25293.55 rows=428855 width=10) (actual rows=333333.33 loops=3)
Buffers: shared hit=14282 read=6723
-> Hash (cost=164.00..164.00 rows=10000 width=7) (actual rows=10000.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
Buffers: shared hit=192
-> Seq Scan on demo_partner p (cost=0.00..164.00 rows=10000 width=7) (actual rows=10000.00 loops=3)
Buffers: shared hit=192
Planning:
Buffers: shared hit=16
Planning Time: 0.159 ms
Execution Time: 166.474 ms
(33 rows)
EXPLAIN (ANALYZE, TIMING false) executes the query but does not collect the execution time for each individual plan node.
Instead, it reports the actual rows processed, loops, buffer usage, and only the overall Planning Time and Execution Time. Disabling per-node timing reduces the overhead of timing measurements, making it useful for benchmarking query performance while still providing accurate row counts and execution statistics.
11. SUMMARY
EXPLAIN (SUMMARY)
SELECT * FROM demo_order WHERE id = 500000;
Result:
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using demo_order_pkey on demo_order (cost=0.42..8.44 rows=1 width=128)
Index Cond: (id = 500000)
Planning Time: 0.119 ms
(3 rows)
EXPLAIN (SUMMARY) displays the execution plan along with a summary of the planning phase.
Since ANALYZE is not specified, the query is not executed, so only the Planning Time is shown. In this example, Postgres uses an Index Scan on the primary key to locate the row with id = 500000, and the output reports that planning the query took 0.119 ms.
EXPLAIN (ANALYZE, SUMMARY false)
SELECT * FROM demo_order WHERE id = 500000;
Result:
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using demo_order_pkey on demo_order (cost=0.42..8.44 rows=1 width=128) (actual time=0.037..0.042 rows=1.00 loops=1)
Index Cond: (id = 500000)
Index Searches: 1
Buffers: shared hit=4 read=2
(4 rows)
EXPLAIN (ANALYZE, SUMMARY false) executes the query and shows the actual execution statistics but omits the final summary lines such as Planning Time and Execution Time.
12. MEMORY
EXPLAIN (MEMORY)
SELECT *
FROM demo_order o
JOIN demo_partner p ON p.id = o.partner_id
JOIN demo_order_line l ON l.order_id = o.id
WHERE o.state = 'sale';
Result:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Hash Join (cost=35251.19..114359.76 rows=488001 width=184)
Hash Cond: (o.partner_id = p.id)
-> Hash Join (cost=34962.19..112789.22 rows=488001 width=165)
Hash Cond: (l.order_id = o.id)
-> Seq Scan on demo_order_line l (cost=0.00..36667.00 rows=2000000 width=37)
-> Hash (cost=27162.97..27162.97 rows=251138 width=128)
-> Bitmap Heap Scan on demo_order o (cost=3018.74..27162.97 rows=251138 width=128)
Recheck Cond: (state = 'sale'::text)
-> Bitmap Index Scan on demo_order_state_idx (cost=0.00..2955.96 rows=251138 width=0)
Index Cond: (state = 'sale'::text)
-> Hash (cost=164.00..164.00 rows=10000 width=19)
-> Seq Scan on demo_partner p (cost=0.00..164.00 rows=10000 width=19)
Planning:
Memory: used=96kB allocated=128kB
JIT:
Functions: 19
Options: Inlining false, Optimization false, Expressions true, Deforming true
(17 rows)
EXPLAIN (MEMORY) displays the execution plan along with the amount of memory used during query planning.
From this plan, it uses Hash Joins to join the three tables, along with a Bitmap Index Scan and Bitmap Heap Scan to filter rows from demo_order. The Planning section reports the planner's memory usage (used=96 kB, allocated=128 kB), making this option useful for understanding the memory consumed while generating the execution plan.
13. IO (new in PG19): asynchronous I/O activity per node.
SHOW io_method;
Result:
io_method
-----------
worker
(1 row)
You can also check this parameters metadata from pg_settings like this.
select * from pg_settings where name = 'io_method';
Result:
-[ RECORD 1 ]---+---------------------------------------------------
name | io_method
setting | worker
unit |
category | Resource Usage / I/O
short_desc | Selects the method for executing asynchronous I/O.
extra_desc |
context | postmaster
vartype | enum
source | default
min_val |
max_val |
enumvals | {sync,worker}
boot_val | worker
reset_val | worker
sourcefile |
sourceline |
pending_restart | f
Now, use the pg_buffercache extension functionality named pg_buffercache_evict_relation to evict all the pages of the model named demo_order from the postgres shared buffers.
SELECT * FROM pg_buffercache_evict_relation('demo_order');Result:
buffers_evicted | buffers_flushed | buffers_skipped
-----------------+-----------------+-----------------
4891 | 0 | 0
(1 row)
Now, execute the explain query with IO as option like this.
EXPLAIN (ANALYZE, IO)
SELECT count(*), avg(amount_total) FROM demo_order;
Result:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=27659.23..27659.24 rows=1 width=40) (actual time=949.010..950.777 rows=1.00 loops=1)
Buffers: shared hit=4797 read=15612
-> Gather (cost=27659.00..27659.21 rows=2 width=40) (actual time=948.763..950.753 rows=3.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4797 read=15612
-> Partial Aggregate (cost=26659.00..26659.01 rows=1 width=40) (actual time=945.405..945.411 rows=1.00 loops=3)
Buffers: shared hit=4797 read=15612
-> Parallel Seq Scan on demo_order (cost=0.00..24575.67 rows=416667 width=6) (actual time=0.127..472.136 rows=333333.33 loops=3)
Prefetch: avg=5.46 max=15 capacity=94
I/O: count=2627 waits=3 size=5.94 in-progress=1.10
Buffers: shared hit=4797 read=15612
Worker 0: Prefetch: avg=5.47 max=15 capacity=94
I/O: count=880 waits=0 size=5.85 in-progress=1.09
Worker 1: Prefetch: avg=5.41 max=15 capacity=94
I/O: count=881 waits=1 size=6.01 in-progress=1.08
Planning Time: 0.060 ms
Execution Time: 950.820 ms
(18 rows)
EXPLAIN (ANALYZE, IO) executes the query and reports I/O statistics for each plan node.
From this plan it executes a parallel sequential scan on demo_order to calculate the count and average of amount_total.
The output includes Prefetch information and I/O statistics, such as the number of I/O operations, waits, data size read, and in-progress requests, along with buffer usage and execution time.
This option is useful for analyzing disk I/O behaviour and asynchronous I/O activity during query execution.
14. FORMAT: TEXT | XML | JSON | YAML
EXPLAIN (FORMAT TEXT)
SELECT * FROM demo_order WHERE partner_id = 42;
Result:
QUERY PLAN
---------------------------------------------------------------------------------------
Bitmap Heap Scan on demo_order (cost=5.22..393.17 rows=102 width=128)
Recheck Cond: (partner_id = 42)
-> Bitmap Index Scan on demo_order_partner_idx (cost=0.00..5.19 rows=102 width=0)
Index Cond: (partner_id = 42)
(4 rows)
EXPLAIN (FORMAT TEXT) displays the execution plan in the postgresql’'s default human-readable text format.
It is easy to read directly from the psql terminal and is best suited for manually understanding how a query will be executed.
EXPLAIN (FORMAT JSON)
SELECT * FROM demo_order WHERE partner_id = 42;
Result:
QUERY PLAN
---------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Bitmap Heap Scan", +
"Parallel Aware": false, +
"Async Capable": false, +
"Relation Name": "demo_order", +
"Alias": "demo_order", +
"Startup Cost": 5.22, +
"Total Cost": 393.17, +
"Plan Rows": 102, +
"Plan Width": 128, +
"Disabled": false, +
"Recheck Cond": "(partner_id = 42)", +
"Plans": [ +
{ +
"Node Type": "Bitmap Index Scan", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Index Name": "demo_order_partner_idx",+
"Startup Cost": 0.00, +
"Total Cost": 5.19, +
"Plan Rows": 102, +
"Plan Width": 0, +
"Disabled": false, +
"Index Cond": "(partner_id = 42)" +
} +
] +
} +
} +
]
(1 row)
EXPLAIN (FORMAT JSON) returns the execution plan in JSON format instead of plain text. Each plan node and its properties are represented as structured JSON objects, making the output easy to parse programmatically or use with visualization tools. This format is useful for applications, scripts, and tools that need to process or analyze execution plans automatically.
EXPLAIN (FORMAT YAML)
SELECT * FROM demo_order WHERE partner_id = 42;
Result:
QUERY PLAN
----------------------------------------------
- Plan: +
Node Type: "Bitmap Heap Scan" +
Parallel Aware: false +
Async Capable: false +
Relation Name: "demo_order" +
Alias: "demo_order" +
Startup Cost: 5.22 +
Total Cost: 393.17 +
Plan Rows: 102 +
Plan Width: 128 +
Disabled: false +
Recheck Cond: "(partner_id = 42)" +
Plans: +
- Node Type: "Bitmap Index Scan" +
Parent Relationship: "Outer" +
Parallel Aware: false +
Async Capable: false +
Index Name: "demo_order_partner_idx"+
Startup Cost: 0.00 +
Total Cost: 5.19 +
Plan Rows: 102 +
Plan Width: 0 +
Disabled: false +
Index Cond: "(partner_id = 42)"
(1 row)
EXPLAIN (FORMAT YAML) returns the execution plan in YAML format.
Like the JSON format, it represents the execution plan as structured data but in a more human-readable layout using indentation instead of braces. This format is useful for configuration-style workflows, automation, and tools that support YAML while still making the execution plan easier to read than JSON.
EXPLAIN (FORMAT XML)
SELECT * FROM demo_order WHERE partner_id = 42;
Result:
m QUERY PLAN
------------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain"> +
<Query> +
<Plan> +
<Node-Type>Bitmap Heap Scan</Node-Type> +
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Relation-Name>demo_order</Relation-Name> +
<Alias>demo_order</Alias> +
<Startup-Cost>5.22</Startup-Cost> +
<Total-Cost>393.17</Total-Cost> +
<Plan-Rows>102</Plan-Rows> +
<Plan-Width>128</Plan-Width> +
<Disabled>false</Disabled> +
<Recheck-Cond>(partner_id = 42)</Recheck-Cond> +
<Plans> +
<Plan> +
<Node-Type>Bitmap Index Scan</Node-Type> +
<Parent-Relationship>Outer</Parent-Relationship>+
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Index-Name>demo_order_partner_idx</Index-Name> +
<Startup-Cost>0.00</Startup-Cost> +
<Total-Cost>5.19</Total-Cost> +
<Plan-Rows>102</Plan-Rows> +
<Plan-Width>0</Plan-Width> +
<Disabled>false</Disabled> +
<Index-Cond>(partner_id = 42)</Index-Cond> +
</Plan> +
</Plans> +
</Plan> +
</Query> +
</explain>
(1 row)
EXPLAIN (FORMAT XML) returns the execution plan in XML format. The plan is represented as a structured XML document, with each execution node and its properties enclosed in XML elements. This format is useful for applications, reporting tools, and systems that process XML data, allowing the execution plan to be parsed and analyzed programmatically.
Understanding the different EXPLAIN options is an important step toward improving query performance in postgres. While the basic execution plan provides an overview of how a query will run, additional options such as ANALYZE, BUFFERS, SETTINGS, MEMORY, IO, WAL, and SERIALIZE reveal valuable details about execution behavior and resource usage.
By selecting the appropriate option for a specific scenario, you can identify performance bottlenecks, verify planner decisions, and make more informed tuning changes. With regular use of EXPLAIN, diagnosing and optimizing SQL queries becomes a more systematic and efficient process.