explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8xkU : storage

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.053 2.676 ↑ 2.7 3 1

HashAggregate (cost=373.26..373.66 rows=8 width=184) (actual time=2.668..2.676 rows=3 loops=1)

  • Group Key: m_storage.m_product_id, m_storage.m_locator_id, COALESCE(m_storage.m_attributesetinstance_id, '0'::numeric), m_storage.ad_client_id, m_storage.ad_org_id
  • Filter: ((sum(m_storage.qtyonhand) <> '0'::numeric) OR (sum(m_storage.qtyordered) <> '0'::numeric) OR (sum(m_storage.qtyreserved) <> '0'::numeric))
  • Rows Removed by Filter: 2
2. 0.025 2.623 ↓ 1.2 10 1

Subquery Scan on m_storage (cost=4.31..372.98 rows=8 width=184) (actual time=0.033..2.623 rows=10 loops=1)

3. 0.025 2.598 ↓ 1.2 10 1

Append (cost=4.31..372.90 rows=8 width=85) (actual time=0.031..2.598 rows=10 loops=1)

4. 0.018 0.209 ↓ 1.8 7 1

Result (cost=4.31..28.04 rows=4 width=115) (actual time=0.029..0.209 rows=7 loops=1)

5. 0.017 0.191 ↓ 1.8 7 1

Append (cost=4.31..28.04 rows=4 width=115) (actual time=0.025..0.191 rows=7 loops=1)

6. 0.018 0.060 ↓ 2.0 6 1

Subquery Scan on *SELECT* 1 (cost=4.31..15.33 rows=3 width=114) (actual time=0.023..0.060 rows=6 loops=1)

7. 0.031 0.042 ↓ 2.0 6 1

Bitmap Heap Scan on m_storageonhand s (cost=4.31..15.30 rows=3 width=114) (actual time=0.019..0.042 rows=6 loops=1)

  • Recheck Cond: (m_product_id = '1020919'::numeric)
  • Heap Blocks: exact=6
8. 0.011 0.011 ↓ 2.0 6 1

Bitmap Index Scan on m_storageonhand_pkey (cost=0.00..4.31 rows=3 width=0) (actual time=0.011..0.011 rows=6 loops=1)

  • Index Cond: (m_product_id = '1020919'::numeric)
9. 0.006 0.114 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=11.41..12.70 rows=1 width=117) (actual time=0.103..0.114 rows=1 loops=1)

10. 0.040 0.108 ↑ 1.0 1 1

Hash Join (cost=11.41..12.69 rows=1 width=117) (actual time=0.099..0.108 rows=1 loops=1)

  • Hash Cond: (w.m_warehouse_id = sr.m_warehouse_id)
11. 0.027 0.027 ↑ 1.0 20 1

Seq Scan on m_warehouse w (cost=0.00..1.20 rows=20 width=32) (actual time=0.005..0.027 rows=20 loops=1)

12. 0.005 0.041 ↑ 1.0 1 1

Hash (cost=11.39..11.39 rows=1 width=108) (actual time=0.041..0.041 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.016 0.036 ↑ 1.0 1 1

Bitmap Heap Scan on m_storagereservation sr (cost=4.30..11.39 rows=1 width=108) (actual time=0.034..0.036 rows=1 loops=1)

  • Recheck Cond: ((m_product_id = '1020919'::numeric) AND (issotrx = 'Y'::bpchar))
  • Filter: (qty <> '0'::numeric)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
14. 0.020 0.020 ↑ 1.0 2 1

Bitmap Index Scan on m_storagereservation_pkey (cost=0.00..4.30 rows=2 width=0) (actual time=0.020..0.020 rows=2 loops=1)

  • Index Cond: ((m_product_id = '1020919'::numeric) AND (issotrx = 'Y'::bpchar))
15. 0.007 0.084 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=8.33..9.62 rows=1 width=117) (actual time=0.079..0.084 rows=1 loops=1)

16. 0.039 0.077 ↑ 1.0 1 1

Hash Join (cost=8.33..9.61 rows=1 width=117) (actual time=0.074..0.077 rows=1 loops=1)

  • Hash Cond: (w_1.m_warehouse_id = so.m_warehouse_id)
17. 0.022 0.022 ↑ 1.0 20 1

Seq Scan on m_warehouse w_1 (cost=0.00..1.20 rows=20 width=32) (actual time=0.002..0.022 rows=20 loops=1)

18. 0.005 0.016 ↑ 1.0 1 1

Hash (cost=8.31..8.31 rows=1 width=108) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.011 0.011 ↑ 1.0 1 1

Index Scan using m_storagereservation_pkey on m_storagereservation so (cost=0.28..8.31 rows=1 width=108) (actual time=0.009..0.011 rows=1 loops=1)

  • Index Cond: ((m_product_id = '1020919'::numeric) AND (issotrx = 'N'::bpchar))
  • Filter: (qty <> '0'::numeric)
  • Rows Removed by Filter: 1
20. 0.002 0.016 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=164.99..165.28 rows=1 width=40) (actual time=0.016..0.016 rows=0 loops=1)

21. 0.003 0.014 ↓ 0.0 0 1

HashAggregate (cost=164.99..165.27 rows=1 width=40) (actual time=0.014..0.014 rows=0 loops=1)

  • Group Key: pl.m_product_id, pl.m_locator_id, pl.ad_client_id, pl.ad_org_id, pl.m_attributesetinstance_id, pl.isendproduct
22. 0.002 0.011 ↓ 0.0 0 1

Nested Loop (cost=4.68..164.97 rows=1 width=40) (actual time=0.011..0.011 rows=0 loops=1)

23. 0.002 0.009 ↓ 0.0 0 1

Bitmap Heap Scan on m_productionline pl (cost=4.40..56.49 rows=14 width=46) (actual time=0.009..0.009 rows=0 loops=1)

  • Recheck Cond: (m_product_id = '1020919'::numeric)
24. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on m_productionline_product (cost=0.00..4.40 rows=14 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (m_product_id = '1020919'::numeric)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using m_production_pkey on m_production p (cost=0.29..7.74 rows=1 width=6) (never executed)

  • Index Cond: (m_production_id = pl.m_production_id)
  • Filter: (((docstatus)::text = ANY ('{AP,IP}'::text[])) AND (iscreated = 'Y'::bpchar) AND (processed = 'N'::bpchar))
26. 0.006 1.409 ↑ 1.0 1 1

Subquery Scan on *SELECT* 5 (cost=84.69..84.98 rows=1 width=33) (actual time=1.405..1.409 rows=1 loops=1)

27. 0.529 1.403 ↑ 1.0 1 1

HashAggregate (cost=84.69..84.97 rows=1 width=33) (actual time=1.401..1.403 rows=1 loops=1)

  • Group Key: ml.m_product_id, ml.m_locator_id, ml.ad_client_id, ml.ad_org_id, ml.m_attributesetinstance_id
28. 0.213 0.874 ↓ 2.0 2 1

Nested Loop (cost=0.28..84.68 rows=1 width=33) (actual time=0.856..0.874 rows=2 loops=1)

29. 0.405 0.405 ↓ 21.3 64 1

Seq Scan on m_movement p_1 (cost=0.00..59.75 rows=3 width=6) (actual time=0.020..0.405 rows=64 loops=1)

  • Filter: ((processed = 'N'::bpchar) AND (docstatus = 'IP'::bpchar))
  • Rows Removed by Filter: 1,227
30. 0.256 0.256 ↓ 0.0 0 64

Index Scan using m_movementline_movement on m_movementline ml (cost=0.28..8.30 rows=1 width=39) (actual time=0.004..0.004 rows=0 loops=64)

  • Index Cond: (m_movement_id = p_1.m_movement_id)
  • Filter: (m_product_id = '1020919'::numeric)
  • Rows Removed by Filter: 1
31. 0.006 0.855 ↑ 1.0 1 1

Subquery Scan on *SELECT* 6 (cost=84.69..84.98 rows=1 width=33) (actual time=0.852..0.855 rows=1 loops=1)

32. 0.048 0.849 ↑ 1.0 1 1

HashAggregate (cost=84.69..84.97 rows=1 width=33) (actual time=0.848..0.849 rows=1 loops=1)

  • Group Key: ml_1.m_product_id, ml_1.m_locatorto_id, ml_1.ad_client_id, ml_1.ad_org_id, ml_1.m_attributesetinstanceto_id
33. 0.194 0.801 ↓ 2.0 2 1

Nested Loop (cost=0.28..84.68 rows=1 width=33) (actual time=0.783..0.801 rows=2 loops=1)

34. 0.351 0.351 ↓ 21.3 64 1

Seq Scan on m_movement p_2 (cost=0.00..59.75 rows=3 width=6) (actual time=0.018..0.351 rows=64 loops=1)

  • Filter: ((processed = 'N'::bpchar) AND (docstatus = 'IP'::bpchar))
  • Rows Removed by Filter: 1,227
35. 0.256 0.256 ↓ 0.0 0 64

Index Scan using m_movementline_movement on m_movementline ml_1 (cost=0.28..8.30 rows=1 width=39) (actual time=0.004..0.004 rows=0 loops=64)

  • Index Cond: (m_movement_id = p_2.m_movement_id)
  • Filter: (m_product_id = '1020919'::numeric)
  • Rows Removed by Filter: 1
Planning time : 2.664 ms
Execution time : 3.082 ms