Interpreting Explain Plans¶
Let us review the below explain plans and understand key terms which will help us in interpreting them.
Seq Scan
Index Scan
Nested Loop
Here are the explain plans for different queries.
Explain plan for query to get number of orders.
EXPLAIN
SELECT count(1) FROM orders;
QUERY PLAN
-------------------------------------------------------------------
Aggregate (cost=1386.04..1386.05 rows=1 width=8)
-> Seq Scan on orders (cost=0.00..1213.83 rows=68883 width=0)
(2 rows)
Explain plan for query to get number of orders by date.
EXPLAIN
SELECT order_date, count(1) AS order_count
FROM orders
GROUP BY order_date;
QUERY PLAN
-------------------------------------------------------------------
HashAggregate (cost=1558.24..1561.88 rows=364 width=16)
Group Key: order_date
-> Seq Scan on orders (cost=0.00..1213.83 rows=68883 width=8)
(3 rows)
Explain plan for query to get order details for a given order id.
EXPLAIN
SELECT * FROM orders
WHERE order_id = 2;
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using orders_pkey on orders (cost=0.29..8.31 rows=1 width=26)
Index Cond: (order_id = 2)
(2 rows)
Explain plan for query to get order and order item details for a given order id.
EXPLAIN
SELECT o.*,
oi.order_item_subtotal
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_id = 2;
QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop (cost=0.29..3427.82 rows=4 width=34)
-> Index Scan using orders_pkey on orders o (cost=0.29..8.31 rows=1 width=26)
Index Cond: (order_id = 2)
-> Seq Scan on order_items oi (cost=0.00..3419.47 rows=4 width=12)
Filter: (order_item_order_id = 2)
(5 rows)
Note
We should understand the order in which the query plans should be interpreted.
Explain plan for a query with multiple joins
EXPLAIN
SELECT
o.order_date,
d.department_id,
d.department_name,
c.category_name,
p.product_name,
round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_item_order_id
JOIN products p
ON p.product_id = oi.order_item_product_id
JOIN categories c
ON c.category_id = p.product_category_id
JOIN departments d
ON d.department_id = c.category_department_id
GROUP BY
o.order_date,
d.department_id,
d.department_name,
c.category_id,
c.category_name,
p.product_id,
p.product_name
ORDER BY o.order_date,
revenue DESC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=76368.54..76799.03 rows=172198 width=211)
Sort Key: o.order_date, (round((sum(oi.order_item_subtotal))::numeric, 2)) DESC
-> Finalize GroupAggregate (cost=25958.31..43735.23 rows=172198 width=211)
Group Key: o.order_date, d.department_id, c.category_id, p.product_id
-> Gather Merge (cost=25958.31..39886.09 rows=101293 width=187)
Workers Planned: 1
-> Partial GroupAggregate (cost=24958.30..27490.62 rows=101293 width=187)
Group Key: o.order_date, d.department_id, c.category_id, p.product_id
-> Sort (cost=24958.30..25211.53 rows=101293 width=187)
Sort Key: o.order_date, d.department_id, c.category_id, p.product_id
-> Hash Join (cost=2495.48..7188.21 rows=101293 width=187)
Hash Cond: (c.category_department_id = d.department_id)
-> Hash Join (cost=2472.43..6897.32 rows=101293 width=79)
Hash Cond: (p.product_category_id = c.category_id)
-> Hash Join (cost=2470.13..6609.69 rows=101293 width=63)
Hash Cond: (oi.order_item_product_id = p.product_id)
-> Hash Join (cost=2411.87..6284.70 rows=101293 width=20)
Hash Cond: (oi.order_item_order_id = o.order_id)
-> Parallel Seq Scan on order_items oi (cost=0.00..2279.93 rows=101293 width=16)
-> Hash (cost=1213.83..1213.83 rows=68883 width=12)
-> Seq Scan on orders o (cost=0.00..1213.83 rows=68883 width=12)
-> Hash (cost=41.45..41.45 rows=1345 width=47)
-> Seq Scan on products p (cost=0.00..41.45 rows=1345 width=47)
-> Hash (cost=1.58..1.58 rows=58 width=20)
-> Seq Scan on categories c (cost=0.00..1.58 rows=58 width=20)
-> Hash (cost=15.80..15.80 rows=580 width=112)
-> Seq Scan on departments d (cost=0.00..15.80 rows=580 width=112)
(27 rows)