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