PostgreSQL:Explain
lastmod 2025-02-15created 2021-12-023 edits
Measures query performance (EXPLAIN).
Just prepend EXPLAIN ANALYZE to any query.
Cost
This option is used automatically when you use EXPLAIN. It shows the estimated startup cost and total cost for each step of the execution plan, along with the estimated number of rows and the (estimated) size of one row.
COST calculation is TIME BASE. There is a formula for estimating COST, and there are factors that affect its magnitude. These factor values can be changed at the system or session level, which in turn changes the execution plan. For example, when a Sequential read occurs, the cost formula is as follows:
The system parameters above are the two seq_page_cost and cpu_tuple_cost, and their default values are as follows:
- seq_page_cost = 1
- cpu_tuple_cost = 0.01
So a table made up of 23 disk pages with 5,000 rows has a cost of 73.
FORMAT
An option that specifies the output format to make it easier to read. Available formats include TEXT, XML, JSON, YAML, and so on. You can specify the format as below.
Available in Postgresql 9.x and later.
TIMING
Shows the actual startup time and total elapsed time for each step of the execution plan. Because this option shows actual elapsed data, it only works when used together with the ANALYZE option.
Example
EXPLAIN ANALYZE
WITH d AS (
SELECT
DISTINCT ad.a4y_uid AS device_uid,
ad.name AS device_name,
ad.description AS device_description,
ad.chart_color AS chart_color,
COUNT(s.time) OVER(PARTITION BY s.a4y_uid) AS service_count,
MAX(s.time) OVER(PARTITION BY s.a4y_uid) AS service_last_time
FROM a4y_device ad
LEFT JOIN a4y_service s ON s.a4y_uid=ad.a4y_uid
WHERE ad.group_slug='aa' AND ad.project_slug='aa'
)
SELECT
d.device_name,
d.device_description,
d.chart_color,
d.service_count,
d.service_last_time,
asl.*
FROM d
LEFT JOIN a4y_state_last asl ON asl.uid=d.device_uid;
Output:
Hash Right Join (cost=24.09..25.14 rows=1 width=438) (actual time=0.224..0.237 rows=2 loops=1)
Hash Cond: (asl.uid = d.device_uid)
-> Seq Scan on a4y_state_last asl (cost=0.00..1.03 rows=3 width=76) (actual time=0.006..0.007 rows=3 loops=1)
-> Hash (cost=24.07..24.07 rows=1 width=366) (actual time=0.195..0.198 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on d (cost=24.04..24.07 rows=1 width=366) (actual time=0.181..0.190 rows=2 loops=1)
-> Unique (cost=24.04..24.06 rows=1 width=370) (actual time=0.178..0.187 rows=2 loops=1)
-> Sort (cost=24.04..24.05 rows=1 width=370) (actual time=0.177..0.181 rows=4 loops=1)
Sort Key: ad.a4y_uid, ad.name, ad.description, ad.chart_color, (count(s."time") OVER (?)), (max(s."time") OVER (?))
Sort Method: quicksort Memory: 25kB
-> WindowAgg (cost=24.01..24.03 rows=1 width=370) (actual time=0.103..0.150 rows=4 loops=1)
-> Sort (cost=24.01..24.02 rows=1 width=362) (actual time=0.079..0.082 rows=4 loops=1)
Sort Key: s.a4y_uid
Sort Method: quicksort Memory: 25kB
-> Hash Right Join (cost=11.21..24.00 rows=1 width=362) (actual time=0.056..0.069 rows=4 loops=1)
Hash Cond: (s.a4y_uid = ad.a4y_uid)
-> Seq Scan on a4y_service s (cost=0.00..12.20 rows=220 width=12) (actual time=0.010..0.011 rows=5 loops=1)
-> Hash (cost=11.20..11.20 rows=1 width=350) (actual time=0.033..0.034 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on a4y_device ad (cost=0.00..11.20 rows=1 width=350) (actual time=0.019..0.021 rows=2 loops=1)
Filter: (((group_slug)::text = 'aa'::text) AND ((project_slug)::text = 'aa'::text))
Rows Removed by Filter: 1
Planning Time: 0.594 ms
Execution Time: 0.486 ms