explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NC9D

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 3,600.830 ↓ 33.7 101 1

Limit (cost=569,749.23..569,804.01 rows=3 width=1,204) (actual time=3,530.531..3,600.830 rows=101 loops=1)

2. 70.928 3,600.795 ↓ 33.7 101 1

Result (cost=569,749.23..569,804.01 rows=3 width=1,204) (actual time=3,530.529..3,600.795 rows=101 loops=1)

3. 0.165 3,529.362 ↓ 33.7 101 1

Sort (cost=569,749.23..569,749.23 rows=3 width=1,059) (actual time=3,529.344..3,529.362 rows=101 loops=1)

  • Sort Key: locator2_.value
  • Sort Method: quicksort Memory: 112kB
4. 9.764 3,529.197 ↓ 56.7 170 1

Nested Loop Left Join (cost=2,368.99..569,749.20 rows=3 width=1,059) (actual time=1,191.543..3,529.197 rows=170 loops=1)

  • Join Filter: ((materialmg0_.m_refinventory_id)::text = (materialmg6_.m_refinventory_id)::text)
  • Rows Removed by Join Filter: 131920
5. 0.139 3,514.163 ↓ 56.7 170 1

Nested Loop Left Join (cost=2,368.99..569,565.05 rows=3 width=1,189) (actual time=1,191.174..3,514.163 rows=170 loops=1)

  • Join Filter: ((materialmg0_.c_uom_id)::text = (uom5_.c_uom_id)::text)
  • Rows Removed by Join Filter: 1870
6. 614.301 3,513.854 ↓ 56.7 170 1

Nested Loop Left Join (cost=2,368.99..569,562.21 rows=3 width=941) (actual time=1,191.157..3,513.854 rows=170 loops=1)

  • Filter: ((upper((product1_.value)::text) ~~ '%5503-BVV%'::text) OR (upper((product1_.upc)::text) ~~ '%5503-BVV%'::text) OR (upper((product1_.name)::text) ~~ '%5503-BVV%'::text) OR (upper((locator2_.value)::text) ~~ '%5503-BVV%'::text) OR (upper((attributes4_.description)::text) ~~ '%5503-BVV%'::text))
  • Rows Removed by Filter: 287155
7. 209.301 2,324.903 ↓ 42.5 287,325 1

Nested Loop Left Join (cost=2,368.71..567,276.43 rows=6,754 width=915) (actual time=0.194..2,324.903 rows=287,325 loops=1)

8. 51.879 966.302 ↓ 42.5 287,325 1

Nested Loop (cost=2,368.28..556,856.13 rows=6,754 width=863) (actual time=0.149..966.302 rows=287,325 loops=1)

9. 0.023 0.065 ↓ 6.0 6 1

Nested Loop Left Join (cost=0.00..2.28 rows=1 width=722) (actual time=0.030..0.065 rows=6 loops=1)

  • Join Filter: ((locator2_.m_warehouse_id)::text = (warehouse3_.m_warehouse_id)::text)
10. 0.024 0.024 ↓ 6.0 6 1

Seq Scan on m_locator locator2_ (cost=0.00..1.20 rows=1 width=486) (actual time=0.017..0.024 rows=6 loops=1)

  • Filter: ((m_warehouse_id)::text = 'FCA81F56E9E643E096E86F5F379459FB'::text)
  • Rows Removed by Filter: 10
11. 0.018 0.018 ↑ 1.0 1 6

Seq Scan on m_warehouse warehouse3_ (cost=0.00..1.06 rows=1 width=318) (actual time=0.003..0.003 rows=1 loops=6)

  • Filter: ((m_warehouse_id)::text = 'FCA81F56E9E643E096E86F5F379459FB'::text)
  • Rows Removed by Filter: 1
12. 313.068 914.358 ↓ 2.2 47,888 6

Bitmap Heap Scan on m_storage_detail materialmg0_ (cost=2,368.28..556,637.72 rows=21,614 width=174) (actual time=4.600..152.393 rows=47,888 loops=6)

  • Recheck Cond: ((m_locator_id)::text = (locator2_.m_locator_id)::text)
  • Filter: ((qtyonhand > '0'::numeric) AND ((qtyonhand - (SubPlan 3)) > '0'::numeric))
  • Rows Removed by Filter: 141
  • Heap Blocks: exact=13624
13. 24.990 24.990 ↑ 1.4 48,030 6

Bitmap Index Scan on m_storage_detail_locator (cost=0.00..2,362.88 rows=64,861 width=0) (actual time=4.165..4.165 rows=48,030 loops=6)

  • Index Cond: ((m_locator_id)::text = (locator2_.m_locator_id)::text)
14.          

SubPlan (forBitmap Heap Scan)

15. 0.000 576.300 ↑ 1.0 1 288,150

Aggregate (cost=8.30..8.31 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=288,150)

16. 576.300 576.300 ↓ 0.0 0 288,150

Index Scan using obawo_task_storagedetail_ix on obawo_task obawo_task10_ (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=288,150)

  • Index Cond: (((m_storage_detail_id)::text = (materialmg0_.m_storage_detail_id)::text) AND ((status)::text = 'AV'::text))
17. 1,149.300 1,149.300 ↑ 1.0 1 287,325

Index Scan using m_attributesetinstance_key on m_attributesetinstance attributes4_ (cost=0.42..1.54 rows=1 width=85) (actual time=0.004..0.004 rows=1 loops=287,325)

  • Index Cond: ((materialmg0_.m_attributesetinstance_id)::text = (m_attributesetinstance_id)::text)
18. 574.650 574.650 ↑ 1.0 1 287,325

Index Scan using m_product_key on m_product product1_ (cost=0.28..0.30 rows=1 width=161) (actual time=0.002..0.002 rows=1 loops=287,325)

  • Index Cond: ((materialmg0_.m_product_id)::text = (m_product_id)::text)
19. 0.160 0.170 ↑ 2.7 12 170

Materialize (cost=0.00..1.48 rows=32 width=252) (actual time=0.000..0.001 rows=12 loops=170)

20. 0.010 0.010 ↑ 2.7 12 1

Seq Scan on c_uom uom5_ (cost=0.00..1.32 rows=32 width=252) (actual time=0.008..0.010 rows=12 loops=1)

21. 5.077 5.270 ↑ 2.6 777 170

Materialize (cost=0.00..98.30 rows=2,020 width=42) (actual time=0.000..0.031 rows=777 loops=170)

22. 0.193 0.193 ↑ 2.6 777 1

Seq Scan on m_refinventory materialmg6_ (cost=0.00..88.20 rows=2,020 width=42) (actual time=0.007..0.193 rows=777 loops=1)

23.          

SubPlan (forResult)

24. 0.299 0.404 ↓ 0.0 0 101

Hash Join (cost=8.17..9.59 rows=1 width=82) (actual time=0.004..0.004 rows=0 loops=101)

  • Hash Cond: ((uom8_.c_uom_id)::text = (productaum7_.c_uom_id)::text)
25. 0.004 0.004 ↑ 32.0 1 1

Seq Scan on c_uom uom8_ (cost=0.00..1.32 rows=32 width=82) (actual time=0.004..0.004 rows=1 loops=1)

26. 0.000 0.101 ↓ 0.0 0 101

Hash (cost=8.16..8.16 rows=1 width=82) (actual time=0.001..0.001 rows=0 loops=101)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
27. 0.101 0.101 ↓ 0.0 0 101

Index Scan using m_product_aum_unq on m_product_aum productaum7_ (cost=0.14..8.16 rows=1 width=82) (actual time=0.001..0.001 rows=0 loops=101)

  • Index Cond: ((m_product_id)::text = (product1_.m_product_id)::text)
  • Filter: ((logistics)::text = 'P'::text)
28. 0.101 0.101 ↓ 0.0 0 101

Index Scan using m_product_aum_product_id on m_product_aum productaum9_ (cost=0.14..8.16 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=101)

  • Index Cond: ((m_product_id)::text = (product1_.m_product_id)::text)
  • Filter: ((logistics)::text = 'P'::text)
Planning time : 7.095 ms
Execution time : 3,601.469 ms