explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QQ6K

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 766.273 ↓ 0.0 0 1

Limit (cost=21,041.85..21,649.40 rows=1 width=475) (actual time=766.273..766.273 rows=0 loops=1)

2. 0.002 766.272 ↓ 0.0 0 1

GroupAggregate (cost=21,041.85..21,649.40 rows=1 width=475) (actual time=766.272..766.272 rows=0 loops=1)

3. 0.027 766.270 ↓ 0.0 0 1

Sort (cost=21,041.85..21,041.85 rows=1 width=475) (actual time=766.270..766.270 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.002 766.243 ↓ 0.0 0 1

Nested Loop Left Join (cost=36.04..21,041.84 rows=1 width=475) (actual time=766.243..766.243 rows=0 loops=1)

5. 0.000 766.241 ↓ 0.0 0 1

Nested Loop Left Join (cost=35.62..21,033.38 rows=1 width=361) (actual time=766.241..766.241 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.001 766.241 ↓ 0.0 0 1

Nested Loop (cost=0.85..20,998.56 rows=1 width=287) (actual time=766.241..766.241 rows=0 loops=1)

7. 0.000 766.240 ↓ 0.0 0 1

Nested Loop (cost=0.85..20,997.21 rows=1 width=271) (actual time=766.240..766.240 rows=0 loops=1)

  • Join Filter: (mrl.c_uom_id = cu.c_uom_id)
8. 239.829 766.240 ↓ 0.0 0 1

Nested Loop (cost=0.85..20,995.72 rows=1 width=133) (actual time=766.240..766.240 rows=0 loops=1)

  • -> Index Scan using m_requisitionline_m_requisition_id on m_requisitionline mrl (cost=0.42..47.37 rows=1 width=
9. 526.372 526.411 ↓ 19.2 5,705 1

Nested Loop (cost=0.42..6,924.24 rows=297 width=104) (actual time=12.989..526.411 rows=5,705 loops=1)

  • -> Seq Scan on m_requisition mr (cost=0.00..6855.26 rows=5931 width=21) (actual time=12.952..496.401 rows
  • Index Cond: (m_requisition_id = mr.m_requisition_id)
  • Filter: ((m_product_id = 2632053::numeric) AND (COALESCE(requisiteqty, 0::numeric) > (SubPlan 5)))
  • Rows Removed by Filter: 4
10. 0.039 0.039 ↑ 1.0 1 1

Nested Loop (cost=0.42..9.67 rows=1 width=83) (actual time=0.036..0.039 rows=1 loops=1)

  • Join Filter: (COALESCE(mp.m_attributeset_id, 0::numeric) = aset.m_attributeset_id)
  • Rows Removed by Join Filter: 9
  • -> Index Scan using m_product_pkey on m_product mp (cost=0.42..8.44 rows=1 width=59) (actual time=0
  • Index Cond: (m_product_id = 2632053::numeric)
  • -> Seq Scan on m_attributeset aset (cost=0.00..1.10 rows=10 width=40) (actual time=0.003..0.006 row
  • Filter: ((ad_client_id = 1000001::numeric) AND (isactive = 'Y'::bpchar) AND (docstatus = 'CO'::bpchar
  • Rows Removed by Filter: 111927
11.          

SubPlan (forNested Loop)

12. 0.000 0.000 ↓ 0.0 0

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

  • -> 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)
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_uom cu (cost=0.00..1.22 rows=22 width=154) (never executed)

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

Index Scan using m_attributesetinstance_pkey on m_attributesetinstance aseti (cost=0.42..8.44 rows=1 width=121) (never executed)

  • Index Cond: (COALESCE(ms.m_attributesetinstance_id, 0::numeric) = m_attributesetinstance_id)
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)