Interpreting Explain Plans

Topic
Materials

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;

Copy to clipboard

                            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)

Copy to clipboard

  • 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;

Copy to clipboard

                            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)

Copy to clipboard

  • Explain plan for query to get order details for a given order id.

EXPLAIN
SELECT * FROM orders
WHERE order_id = 2;

Copy to clipboard

                                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)

Copy to clipboard

  • 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;

Copy to clipboard

                                    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)

Copy to clipboard

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;

Copy to clipboard

                                                              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)