explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2oZt

Settings
# exclusive inclusive rows x rows loops node
1. 2.481 3,813.763 ↓ 667.0 667 1

Sort (cost=501.75..501.76 rows=1 width=1,560) (actual time=3,813.724..3,813.763 rows=667 loops=1)

  • Sort Key: storage.name
  • Sort Method: quicksort Memory: 178kB
2.          

CTE params

3. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.002..0.003 rows=1 loops=1)

4.          

CTE cost

5. 13.466 1,005.417 ↓ 12,318.0 12,318 1

WindowAgg (cost=48.61..48.63 rows=1 width=52) (actual time=991.024..1,005.417 rows=12,318 loops=1)

6. 5.646 991.951 ↓ 12,318.0 12,318 1

Sort (cost=48.61..48.61 rows=1 width=20) (actual time=990.993..991.951 rows=12,318 loops=1)

  • Sort Key: cs.m_product_id, cs.updated DESC
  • Sort Method: quicksort Memory: 1,347kB
7. 3.873 986.305 ↓ 12,318.0 12,318 1

Group (cost=48.59..48.60 rows=1 width=20) (actual time=981.081..986.305 rows=12,318 loops=1)

  • Group Key: cs.m_product_id, cs.currentcostprice, cs.updated
8. 16.690 982.432 ↓ 12,318.0 12,318 1

Sort (cost=48.59..48.59 rows=1 width=20) (actual time=981.077..982.432 rows=12,318 loops=1)

  • Sort Key: cs.m_product_id, cs.currentcostprice, cs.updated
  • Sort Method: quicksort Memory: 1,347kB
9. 935.638 965.742 ↓ 12,318.0 12,318 1

Nested Loop (cost=0.45..48.58 rows=1 width=20) (actual time=4.095..965.742 rows=12,318 loops=1)

  • Join Filter: CASE WHEN ((getcostinglevel(cs.ad_client_id, cs.m_product_id))::text = 'O'::text) THEN (cs.ad_org_id = params.org_id) ELSE (cs.ad_org_id = '0'::numeric) END
10. 0.030 0.162 ↑ 1.0 2 1

Nested Loop (cost=0.03..9.61 rows=2 width=84) (actual time=0.063..0.162 rows=2 loops=1)

  • Join Filter: (((ce.ad_client_id = '0'::numeric) OR (ce.ad_client_id = params.client_id)) AND ((ce.ad_org_id = '0'::numeric) OR (ce.ad_org_id = params.org_id)))
  • Rows Removed by Join Filter: 69
11. 0.037 0.065 ↑ 1.0 1 1

Hash Join (cost=0.03..2.98 rows=1 width=77) (actual time=0.051..0.065 rows=1 loops=1)

  • Hash Cond: (cin.ad_client_id = params.client_id)
12. 0.019 0.019 ↑ 2.0 34 1

Seq Scan on ad_clientinfo cin (cost=0.00..2.68 rows=68 width=13) (actual time=0.006..0.019 rows=34 loops=1)

13. 0.006 0.009 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=64) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on params (cost=0.00..0.02 rows=1 width=64) (actual time=0.002..0.003 rows=1 loops=1)

15. 0.067 0.067 ↑ 1.0 71 1

Seq Scan on m_costelement ce (cost=0.00..5.21 rows=71 width=16) (actual time=0.009..0.067 rows=71 loops=1)

  • Filter: (costingmethod = 'A'::bpchar)
  • Rows Removed by Filter: 106
16. 29.942 29.942 ↓ 1,539.8 6,159 2

Index Scan using ender_m_cost_cost_m_costelement_id on m_cost cs (cost=0.42..18.41 rows=4 width=43) (actual time=0.034..14.971 rows=6,159 loops=2)

  • Index Cond: ((m_costelement_id = ce.m_costelement_id) AND (c_acctschema_id = cin.c_acctschema1_id) AND (ad_client_id = cin.ad_client_id))
  • Filter: (isactive = 'Y'::bpchar)
  • Rows Removed by Filter: 798
17.          

CTE storage

18. 1.632 3,809.998 ↓ 667.0 667 1

GroupAggregate (cost=452.83..453.04 rows=1 width=369) (actual time=3,808.357..3,809.998 rows=667 loops=1)

  • Group Key: s.ad_org_id, s.m_product_id, p.value, p.name, w.name, loc.value, c.name, cp.name, r.level_min, r.level_max, r.level_min_vol, r.level_max_vol, r.replenishtype, con.multiplyrate, con2.multiplyrate, p.c_uom_id, p.c_uomvolume_id, con.dividerate, con2.dividerate, con.c_uom_to_id, con2.c_uom_to_id, (COALESCE(cc.currentcostprice, 0.0)), ((COALESCE(cc.currentcostprice, 0.0) * s.qtyonhand)), lot.lot
19. 3.026 3,808.366 ↓ 668.0 668 1

Sort (cost=452.83..452.83 rows=1 width=244) (actual time=3,808.305..3,808.366 rows=668 loops=1)

  • Sort Key: s.ad_org_id, s.m_product_id, p.value, p.name, w.name, loc.value, c.name, cp.name, r.level_min, r.level_max, r.level_min_vol, r.level_max_vol, r.replenishtype, con.multiplyrate, con2.multiplyrate, p.c_uom_id, p.c_uomvolume_id, con.dividerate, con2.dividerate, con.c_uom_to_id, con2.c_uom_to_id, (COALESCE(cc.currentcostprice, 0.0)), ((COALESCE(cc.currentcostprice, 0.0) * s.qtyonhand)), lot.lot
  • Sort Method: quicksort Memory: 201kB
20. 1.298 3,805.340 ↓ 668.0 668 1

Nested Loop Left Join (cost=9.00..452.82 rows=1 width=244) (actual time=1,009.250..3,805.340 rows=668 loops=1)

21. 0.967 3,802.038 ↓ 668.0 668 1

Nested Loop Left Join (cost=8.72..452.49 rows=1 width=195) (actual time=1,009.228..3,802.038 rows=668 loops=1)

22. 0.814 3,799.067 ↓ 668.0 668 1

Nested Loop Left Join (cost=8.43..452.16 rows=1 width=178) (actual time=1,009.209..3,799.067 rows=668 loops=1)

23. 3.008 3,796.249 ↓ 668.0 668 1

Nested Loop Left Join (cost=8.16..451.87 rows=1 width=168) (actual time=1,009.191..3,796.249 rows=668 loops=1)

  • Join Filter: ((r.ad_client_id = params_1.client_id) AND ((r.ad_org_id = '0'::numeric) OR (r.ad_org_id = params_1.org_id)) AND ((r.m_warehouse_id = '0'::numeric) OR (r.m_warehouse_id = params_1.warehouse_id)))
  • Rows Removed by Join Filter: 5,564
24. 1.329 3,773.201 ↓ 668.0 668 1

Nested Loop Left Join (cost=7.74..450.90 rows=1 width=252) (actual time=1,009.143..3,773.201 rows=668 loops=1)

25. 1,291.817 3,769.200 ↓ 668.0 668 1

Nested Loop Left Join (cost=7.46..450.60 rows=1 width=248) (actual time=1,009.111..3,769.200 rows=668 loops=1)

  • Join Filter: (cc.m_product_id = s.m_product_id)
  • Rows Removed by Join Filter: 8,215,453
26. 0.841 213.585 ↓ 667.0 667 1

Nested Loop (cost=7.46..450.56 rows=1 width=216) (actual time=9.424..213.585 rows=667 loops=1)

27. 1.537 210.743 ↓ 667.0 667 1

Nested Loop (cost=7.18..450.27 rows=1 width=206) (actual time=9.416..210.743 rows=667 loops=1)

  • Join Filter: (por.m_product_id = p.m_product_id)
28. 24.126 205.204 ↓ 667.0 667 1

Nested Loop (cost=6.76..449.71 rows=1 width=154) (actual time=9.385..205.204 rows=667 loops=1)

  • Join Filter: (loc.m_locator_id = s.m_locator_id)
  • Rows Removed by Join Filter: 42,674
29. 5.900 28.289 ↓ 21,827.0 21,827 1

Nested Loop (cost=6.34..449.09 rows=1 width=185) (actual time=7.022..28.289 rows=21,827 loops=1)

30. 0.007 0.074 ↑ 1.0 1 1

Nested Loop (cost=0.57..8.74 rows=1 width=167) (actual time=0.068..0.074 rows=1 loops=1)

  • Join Filter: ((params_1.locator_id = loc.m_locator_id) AND (params_1.warehouse_id = loc.m_warehouse_id))
31. 0.007 0.038 ↑ 1.0 1 1

Nested Loop (cost=0.28..8.32 rows=1 width=153) (actual time=0.034..0.038 rows=1 loops=1)

32. 0.009 0.009 ↑ 1.0 1 1

CTE Scan on params params_1 (cost=0.00..0.02 rows=1 width=128) (actual time=0.006..0.009 rows=1 loops=1)

33. 0.022 0.022 ↑ 1.0 1 1

Index Scan using m_warehouse_pkey on m_warehouse w (cost=0.28..8.30 rows=1 width=25) (actual time=0.022..0.022 rows=1 loops=1)

  • Index Cond: (m_warehouse_id = params_1.warehouse_id)
34. 0.029 0.029 ↑ 1.0 1 1

Index Scan using locator_warehouseval on m_locator loc (cost=0.29..0.40 rows=1 width=28) (actual time=0.029..0.029 rows=1 loops=1)

  • Index Cond: (m_warehouse_id = w.m_warehouse_id)
  • Filter: (isactive = 'Y'::bpchar)
35. 15.725 22.315 ↓ 169.2 21,827 1

Bitmap Heap Scan on exme_productoorg por (cost=5.77..439.07 rows=129 width=18) (actual time=6.949..22.315 rows=21,827 loops=1)

  • Recheck Cond: ((ad_client_id = params_1.client_id) AND (ad_org_id = params_1.org_id))
  • Filter: (isactive = 'Y'::bpchar)
  • Rows Removed by Filter: 1,414
  • Heap Blocks: exact=1,903
36. 6.590 6.590 ↓ 176.1 23,244 1

Bitmap Index Scan on po_idx (cost=0.00..5.74 rows=132 width=0) (actual time=6.590..6.590 rows=23,244 loops=1)

  • Index Cond: ((ad_client_id = params_1.client_id) AND (ad_org_id = params_1.org_id))
37. 152.789 152.789 ↓ 2.0 2 21,827

Index Scan using productstore on m_storage s (cost=0.42..0.60 rows=1 width=32) (actual time=0.005..0.007 rows=2 loops=21,827)

  • Index Cond: ((m_product_id = por.m_product_id) AND (ad_client_id = por.ad_client_id) AND (ad_org_id = por.ad_org_id))
  • Filter: (isactive = 'Y'::bpchar)
38. 4.002 4.002 ↑ 1.0 1 667

Index Scan using m_product_pkey on m_product p (cost=0.42..0.55 rows=1 width=58) (actual time=0.006..0.006 rows=1 loops=667)

  • Index Cond: (m_product_id = s.m_product_id)
  • Filter: (ad_client_id = ANY ('{0,10001008}'::numeric[]))
39. 2.001 2.001 ↑ 1.0 1 667

Index Scan using c_uom_pkey on c_uom c (cost=0.28..0.29 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=667)

  • Index Cond: (c_uom_id = p.c_uom_id)
40. 2,263.798 2,263.798 ↓ 12,318.0 12,318 667

CTE Scan on cost cc (cost=0.00..0.02 rows=1 width=48) (actual time=1.486..3.394 rows=12,318 loops=667)

41. 2.672 2.672 ↑ 1.0 1 668

Index Scan using m_attributesetinstance_pkey on m_attributesetinstance lot (cost=0.28..0.30 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=668)

  • Index Cond: (s.m_attributesetinstance_id = m_attributesetinstance_id)
42. 20.040 20.040 ↓ 1.3 8 668

Index Scan using m_replenish_key on m_replenish r (cost=0.42..0.84 rows=6 width=44) (actual time=0.015..0.030 rows=8 loops=668)

  • Index Cond: (m_product_id = p.m_product_id)
  • Filter: (isactive = 'Y'::bpchar)
  • Rows Removed by Filter: 0
43. 2.004 2.004 ↑ 1.0 1 668

Index Scan using c_uom_pkey on c_uom cp (cost=0.28..0.29 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=668)

  • Index Cond: (c_uom_id = p.c_uomvolume_id)
44. 2.004 2.004 ↓ 0.0 0 668

Index Scan using c_uom_conversion_uom on c_uom_conversion con (cost=0.29..0.33 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=668)

  • Index Cond: ((c_uom_id = p.c_uom_id) AND (c_uom_to_id = p.c_uomvolume_id))
45. 2.004 2.004 ↓ 0.0 0 668

Index Scan using c_uom_conversion_uom on c_uom_conversion con2 (cost=0.29..0.33 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=668)

  • Index Cond: ((c_uom_id = p.c_uomvolume_id) AND (c_uom_to_id = p.c_uom_id))
46. 3,811.282 3,811.282 ↓ 667.0 667 1

CTE Scan on storage (cost=0.00..0.06 rows=1 width=1,560) (actual time=3,808.377..3,811.282 rows=667 loops=1)

Planning time : 55.117 ms
Execution time : 3,816.885 ms