explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RUvN

Settings
# exclusive inclusive rows x rows loops node
1. 41.288 670.709 ↑ 172.3 596 1

Nested Loop (cost=0.00..157,504.99 rows=102,675 width=127) (actual time=201.968..670.709 rows=596 loops=1)

  • Output: m.id, m.names, m.target_product_id, m.operation, m.qty, m.unit, (0)
  • Functions: 48
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 3.562 ms, Inlining 0.000 ms, Optimization 1.235 ms, Emission 26.428 ms, Total 31.225 ms
2. 54.441 54.441 ↑ 1.0 4,107 1

Seq Scan on product.product p (cost=0.00..530.07 rows=4,107 width=1,356) (actual time=28.187..54.441 rows=4,107 loops=1)

  • Output: p.*
3. 65.712 574.980 ↓ 0.0 0 4,107

Append (cost=0.00..37.97 rows=25 width=127) (actual time=0.124..0.140 rows=0 loops=4,107)

4. 42.399 176.601 ↓ 0.0 0 4,107

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.043..0.043 rows=0 loops=4,107)

  • Output: m.id, m.names, m.target_product_id, m.operation, m.qty, m.unit, 0
  • Join Filter: (m.modifier_group_id = pmg.modifier_group_id)
5. 127.317 127.317 ↓ 0.0 0 4,107

Seq Scan on product.product_modifier_group pmg (cost=0.00..7.19 rows=1 width=16) (actual time=0.030..0.031 rows=0 loops=4,107)

  • Output: pmg.id, pmg.product_id, pmg.modifier_group_id, pmg.created_at, pmg.created_by, pmg.updated_at, pmg.updated_by, pmg.deleted_at, pmg.deleted_by
  • Filter: ((pmg.deleted_at IS NULL) AND (pmg.product_id = (p.*).id))
  • Rows Removed by Filter: 255
6. 6.885 6.885 ↓ 0.0 0 255

Seq Scan on product.modifier m (cost=0.00..6.20 rows=40 width=139) (actual time=0.027..0.027 rows=0 loops=255)

  • Output: m.id, m.names, m.traits, m.created_at, m.created_by, m.updated_at, m.updated_by, m.deleted_at, m.deleted_by, m.modifier_group_id, m.target_product_id, m.operation, m.qty, m.unit, m.customer_visible
  • Filter: (m.customer_visible AND (m.deleted_at IS NULL) AND ((m.operation)::text = ANY ('{INFO,ADD,REPLACE}'::text[])))
  • Rows Removed by Filter: 160
7. 50.085 332.667 ↓ 0.0 0 4,107

Nested Loop (cost=5.27..23.71 rows=1 width=125) (actual time=0.066..0.081 rows=0 loops=4,107)

  • Output: m_1.id, m_1.names, m_1.target_product_id, m_1.operation, ri.qty, ri.unit, ri."position
  • Inner Unique: true
8. 68.421 275.169 ↓ 0.0 0 4,107

Nested Loop (cost=5.00..22.64 rows=1 width=141) (actual time=0.052..0.067 rows=0 loops=4,107)

  • Output: m_1.id, m_1.names, m_1.target_product_id, m_1.operation, m_1.modifier_group_id, ri.qty, ri.unit, ri."position
9. 63.234 184.815 ↑ 10.0 1 4,107

Nested Loop (cost=4.85..20.86 rows=10 width=27) (actual time=0.032..0.045 rows=1 loops=4,107)

  • Output: ri.qty, ri.unit, ri."position", ri.product_id
10. 53.694 102.675 ↓ 0.0 0 4,107

Nested Loop (cost=4.57..19.28 rows=2 width=16) (actual time=0.021..0.025 rows=0 loops=4,107)

  • Output: rg.id
11. 32.856 32.856 ↓ 0.0 0 4,107

Index Scan using recipe_product_id_idx on product.recipe r (cost=0.28..8.30 rows=1 width=16) (actual time=0.007..0.008 rows=0 loops=4,107)

  • Output: r.id
  • Index Cond: (r.product_id = (p.*).id)
  • Filter: ((r.deleted_at IS NULL) AND ((r.type)::text = 'NORMAL'::text) AND COALESCE(((now())::date <@ r.valid_during), true))
  • Rows Removed by Filter: 0
12. 11.610 16.125 ↑ 2.0 1 645

Bitmap Heap Scan on product.recipe_group rg (cost=4.29..10.96 rows=2 width=32) (actual time=0.019..0.025 rows=1 loops=645)

  • Output: rg.recipe_id, rg.id
  • Recheck Cond: (rg.recipe_id = r.id)
  • Filter: (rg.deleted_at IS NULL)
  • Heap Blocks: exact=549
13. 4.515 4.515 ↑ 2.0 1 645

Bitmap Index Scan on idx_recipe_group (cost=0.00..4.29 rows=2 width=0) (actual time=0.007..0.007 rows=1 loops=645)

  • Index Cond: (rg.recipe_id = r.id)
14. 18.906 18.906 ↑ 1.7 3 822

Index Scan using recipe_item_group_id_idx on product.recipe_item ri (cost=0.28..0.74 rows=5 width=43) (actual time=0.008..0.023 rows=3 loops=822)

  • Output: ri.qty, ri.unit, ri."position", ri.group_id, ri.product_id
  • Index Cond: (ri.group_id = rg.id)
  • Filter: (ri.deleted_at IS NULL)
15. 21.933 21.933 ↓ 0.0 0 2,437

Index Scan using modifier_target_product_id_idx on product.modifier m_1 (cost=0.14..0.17 rows=1 width=130) (actual time=0.006..0.009 rows=0 loops=2,437)

  • Output: m_1.id, m_1.names, m_1.traits, m_1.created_at, m_1.created_by, m_1.updated_at, m_1.updated_by, m_1.deleted_at, m_1.deleted_by, m_1.modifier_group_id, m_1.target_product_id, m_1.operation, m_1.qty, m_1.unit, m_1.customer_visible
  • Index Cond: (m_1.target_product_id = ri.product_id)
  • Filter: (m_1.customer_visible AND (m_1.deleted_at IS NULL) AND ((m_1.operation)::text = 'REMOVE'::text))
  • Rows Removed by Filter: 0
16. 7.413 7.413 ↑ 1.0 1 1,059

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_1 (cost=0.27..1.06 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1,059)

  • Output: pmg_1.id, pmg_1.product_id, pmg_1.modifier_group_id, pmg_1.created_at, pmg_1.created_by, pmg_1.updated_at, pmg_1.updated_by, pmg_1.deleted_at, pmg_1.deleted_by
  • Index Cond: ((pmg_1.product_id = (p.*).id) AND (pmg_1.modifier_group_id = m_1.modifier_group_id))
  • Filter: (pmg_1.deleted_at IS NULL)
Execution time : 677.480 ms