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)
-
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)