explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bgdJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 5,672.153 ↓ 0.0 0 1

Limit (cost=3,772,769.06..3,773,376.61 rows=1 width=475) (actual time=5,672.153..5,672.153 rows=0 loops=1)

2. 0.002 5,672.151 ↓ 0.0 0 1

GroupAggregate (cost=3,772,769.06..3,773,376.61 rows=1 width=475) (actual time=5,672.151..5,672.151 rows=0 loops=1)

3. 0.027 5,672.149 ↓ 0.0 0 1

Sort (cost=3,772,769.06..3,772,769.06 rows=1 width=475) (actual time=5,672.149..5,672.149 rows=0 loops=1)

  • Sort Key: mr.m_requisition_id, mp.m_product_id, mp.name, mp.description, ms.m_attributesetinstance_id, cu.name, mwh.m_warehouse_id, mr.m_pricel
  • Sort Method: quicksort Memory: 25kB
4. 0.001 5,672.122 ↓ 0.0 0 1

Nested Loop Left Join (cost=34.77..3,772,769.05 rows=1 width=475) (actual time=5,672.122..5,672.122 rows=0 loops=1)

  • Join Filter: (COALESCE(ms.m_attributesetinstance_id, 0::numeric) = aseti.m_attributesetinstance_id)
5. 0.001 5,672.121 ↓ 0.0 0 1

Nested Loop Left Join (cost=34.77..3,752,947.48 rows=1 width=361) (actual time=5,672.121..5,672.121 rows=0 loops=1)

  • Join Filter: ((ms.m_product_id = mp.m_product_id) AND (COALESCE(ml.m_warehouse_id, 0::numeric) = mwh.m_warehouse_id))
6. 0.000 5,672.120 ↓ 0.0 0 1

Nested Loop (cost=0.00..3,752,912.65 rows=1 width=287) (actual time=5,672.120..5,672.120 rows=0 loops=1)

7. 2,011.443 5,672.120 ↓ 0.0 0 1

Nested Loop (cost=0.00..3,752,911.31 rows=1 width=271) (actual time=5,672.120..5,672.120 rows=0 loops=1)

  • Join Filter: (mrl.m_requisition_id = mr.m_requisition_id)
  • Rows Removed by Join Filter: 79870
  • -> Seq Scan on m_requisition mr (cost=0.00..6855.26 rows=5931 width=21) (actual time=0.860..141.118 rows=5705 loops=1
8. 3,429.727 3,660.677 ↓ 14.0 14 1

Nested Loop (cost=0.00..3,745,981.91 rows=1 width=257) (actual time=336.296..3,660.677 rows=14 loops=1)

  • Join Filter: (cu.c_uom_id = mrl.c_uom_id)
  • Rows Removed by Join Filter: 308
  • -> Seq Scan on m_requisitionline mrl (cost=0.00..3711381.89 rows=5 width=36) (actual time=148.733..149.106 rows
  • Filter: ((ad_client_id = 1000001::numeric) AND (isactive = 'Y'::bpchar) AND (docstatus = 'CO'::bpchar) AND (m_war
  • Rows Removed by Filter: 111927
9. 0.000 230.950 ↓ 23.0 23 1

Nested Loop (cost=0.00..34,599.95 rows=1 width=237) (actual time=128.624..230.950 rows=23 loops=1)

  • Filter: ((m_product_id = 2632053::numeric) AND (COALESCE(requisiteqty, 0::numeric) > (SubPlan 5)))
  • Rows Removed by Filter: 434443
10. 230.882 230.882 ↑ 1.0 1 1

Nested Loop (cost=0.00..34,598.51 rows=1 width=83) (actual time=128.617..230.882 rows=1 loops=1)

  • Join Filter: (COALESCE(mp.m_attributeset_id, 0::numeric) = aset.m_attributeset_id)
  • Rows Removed by Join Filter: 9
  • -> Seq Scan on m_product mp (cost=0.00..34597.29 rows=1 width=59) (actual time=128.591..230.853 row
  • Filter: (m_product_id = 2632053::numeric)
  • Rows Removed by Filter: 539393
  • -> Seq Scan on m_attributeset aset (cost=0.00..1.10 rows=10 width=40) (actual time=0.003..0.005 row
11. 0.022 0.022 ↓ 1.0 23 1

Seq Scan on c_uom cu (cost=0.00..1.22 rows=22 width=154) (actual time=0.004..0.022 rows=23 loops=1)

12.          

SubPlan (forNested Loop)

13. 2.875 2.875 ↑ 1.0 1 575

Aggregate (cost=8.44..8.45 rows=1 width=5) (actual time=0.005..0.005 rows=1 loops=575)

  • -> Index Scan using m_packageline_m_requisitionline_id on m_packageline mpl2_2 (cost=0.42..8.44 r
  • Index Cond: (m_requisitionline_id = mrl.m_requisitionline_id)
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_warehouse mwh (cost=0.00..1.34 rows=1 width=16) (never executed)

  • Filter: (m_warehouse_id = 1000000::numeric)
15. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=34.77..34.80 rows=1 width=50) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Sort (cost=34.77..34.78 rows=1 width=50) (never executed)

  • Sort Key: ml.isdefault, ms.m_product_id, ms.m_attributesetinstance_id, ms.m_locator_id, ml.m_warehouse_id
17. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..34.76 rows=1 width=50) (never executed)

  • Join Filter: (ms.m_locator_id = ml.m_locator_id)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using mstorageqtyminusresv on m_storage ms (cost=0.42..33.15 rows=1 width=26) (never executed)

  • Index Cond: (m_product_id = 2632053::numeric)
  • Filter: ((COALESCE(m_attributesetinstance_id, 0::numeric) = 2617381::numeric) AND ((qtyonhand - qtyreserved
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_locator ml (cost=0.00..1.27 rows=27 width=40) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_attributesetinstance aseti (cost=0.00..13,045.14 rows=542,114 width=121) (never executed)

21.          

SubPlan (forGroupAggregate)

22. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.44..8.45 rows=1 width=5) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using m_packageline_m_requisitionline_id on m_packageline mpl2 (cost=0.42..8.44 rows=1 width=5) (never executed)

  • Index Cond: (m_requisitionline_id = mrl.m_requisitionline_id)
24. 0.000 0.000 ↓ 0.0 0

Sort (cost=1.36..1.36 rows=1 width=114) (never executed)

  • Sort Key: mloc2.m_locator_id
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_locator mloc2 (cost=0.00..1.35 rows=1 width=114) (never executed)

  • Filter: (m_warehouse_id = mwh.m_warehouse_id)
26. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=588.70..588.71 rows=1 width=4) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..588.70 rows=1 width=4) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_movement mm (cost=0.00..523.88 rows=2 width=6) (never executed)

  • Filter: (docstatus = 'IP'::bpchar)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using m_movementline_movement on m_movementline mml2 (cost=0.42..32.40 rows=1 width=11) (never executed)

  • Index Cond: (m_movement_id = mm.m_movement_id)
  • Filter: ((m_locator_id = ms.m_locator_id) AND (m_product_id = mp.m_product_id) AND (m_attributesetinstance_id = ms.m_attributeset
30. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.44..8.45 rows=1 width=5) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using m_packageline_m_requisitionline_id on m_packageline mpl2_1 (cost=0.42..8.44 rows=1 width=5) (never executed)

  • Index Cond: (m_requisitionline_id = mrl.m_requisitionline_id)