explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B1KJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 371,786.673 ↓ 50.5 101 1

Limit (cost=419,589.16..419,625.68 rows=2 width=1,204) (actual time=371,508.778..371,786.673 rows=101 loops=1)

2. 316.533 371,786.606 ↓ 50.5 101 1

Result (cost=419,589.16..419,625.68 rows=2 width=1,204) (actual time=371,508.775..371,786.606 rows=101 loops=1)

3. 0.272 371,469.366 ↓ 50.5 101 1

Sort (cost=419,589.16..419,589.16 rows=2 width=1,059) (actual time=371,469.331..371,469.366 rows=101 loops=1)

  • Sort Key: locator2_.value
  • Sort Method: quicksort Memory: 112kB
4. 11.308 371,469.094 ↓ 85.0 170 1

Nested Loop Left Join (cost=8,478.86..419,589.15 rows=2 width=1,059) (actual time=233,905.747..371,469.094 rows=170 loops=1)

  • Join Filter: ((materialmg0_.m_refinventory_id)::text = (materialmg6_.m_refinventory_id)::text)
  • Rows Removed by Join Filter: 131920
5. 0.290 371,448.946 ↓ 85.0 170 1

Nested Loop Left Join (cost=8,478.86..419,109.90 rows=2 width=1,189) (actual time=233,901.774..371,448.946 rows=170 loops=1)

  • Join Filter: ((materialmg0_.c_uom_id)::text = (uom5_.c_uom_id)::text)
  • Rows Removed by Join Filter: 1870
6. 2,966.187 371,448.486 ↓ 85.0 170 1

Hash Left Join (cost=8,478.86..419,107.54 rows=2 width=941) (actual time=233,901.750..371,448.486 rows=170 loops=1)

  • Hash Cond: ((materialmg0_.m_product_id)::text = (product1_.m_product_id)::text)
  • Filter: ((upper((product1_.value)::text) ~~ '%5503-BVV%'::text) OR (upper((product1_.upc)::text) ~~ '%5503-BVV%'::text) OR (upper((product1_.name)::text
  • Rows Removed by Filter: 717310
7. 796.406 368,481.543 ↓ 153.9 717,480 1

Nested Loop Left Join (cost=8,336.07..418,952.40 rows=4,663 width=923) (actual time=55.249..368,481.543 rows=717,480 loops=1)

8. 284.978 361,227.817 ↓ 153.9 717,480 1

Nested Loop (cost=8,335.51..393,051.28 rows=4,663 width=863) (actual time=55.228..361,227.817 rows=717,480 loops=1)

9. 0.055 0.099 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.00..2.31 rows=1 width=722) (actual time=0.016..0.099 rows=10 loops=1)

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

Seq Scan on m_locator locator2_ (cost=0.00..1.24 rows=1 width=486) (actual time=0.011..0.024 rows=10 loops=1)

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

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

  • Filter: ((m_warehouse_id)::text = 'FCA81F56E9E643E096E86F5F379459FB'::text)
  • Rows Removed by Filter: 4
12. 1,919.630 360,942.740 ↓ 4.0 71,748 10

Bitmap Heap Scan on m_storage_detail materialmg0_ (cost=8,335.51..392,871.79 rows=17,718 width=174) (actual time=6.131..36,094.274 rows=71,748 loops=10)

  • 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: 47
  • Heap Blocks: exact=44618
13. 49.110 49.110 ↓ 1.4 76,128 10

Bitmap Index Scan on m_storage_detail_locator (cost=0.00..8,331.09 rows=53,155 width=0) (actual time=4.911..4.911 rows=76,128 loops=10)

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

SubPlan (forBitmap Heap Scan)

15. 717.948 358,974.000 ↑ 1.0 1 717,948

Aggregate (cost=6.32..6.33 rows=1 width=32) (actual time=0.500..0.500 rows=1 loops=717,948)

16. 358,256.052 358,256.052 ↓ 0.0 0 717,948

Index Scan using obawo_task_status_ix on obawo_task obawo_task10_ (cost=0.55..6.32 rows=1 width=4) (actual time=0.499..0.499 rows=0 loops=717,948)

  • Index Cond: ((status)::text = 'AV'::text)
  • Filter: ((m_storage_detail_id)::text = (materialmg0_.m_storage_detail_id)::text)
  • Rows Removed by Filter: 1226
17. 6,457.320 6,457.320 ↑ 1.0 1 717,480

Index Scan using m_attributesetinstance_key on m_attributesetinstance attributes4_ (cost=0.55..5.55 rows=1 width=93) (actual time=0.009..0.009 rows=1 loops=717,480)

  • Index Cond: ((materialmg0_.m_attributesetinstance_id)::text = (m_attributesetinstance_id)::text)
18. 0.303 0.756 ↑ 1.0 1,724 1

Hash (cost=121.24..121.24 rows=1,724 width=157) (actual time=0.756..0.756 rows=1,724 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 206kB
19. 0.453 0.453 ↑ 1.0 1,724 1

Seq Scan on m_product product1_ (cost=0.00..121.24 rows=1,724 width=157) (actual time=0.012..0.453 rows=1,724 loops=1)

20. 0.156 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)

21. 0.014 0.014 ↑ 2.7 12 1

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

22. 5.061 8.840 ↑ 7.9 777 170

Materialize (cost=0.00..311.50 rows=6,100 width=42) (actual time=0.004..0.052 rows=777 loops=170)

23. 3.779 3.779 ↑ 7.9 777 1

Seq Scan on m_refinventory materialmg6_ (cost=0.00..281.00 rows=6,100 width=42) (actual time=0.643..3.779 rows=777 loops=1)

24.          

SubPlan (forResult)

25. 0.296 0.606 ↓ 0.0 0 101

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

  • Hash Cond: ((uom8_.c_uom_id)::text = (productaum7_.c_uom_id)::text)
26. 0.007 0.007 ↑ 32.0 1 1

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

27. 0.101 0.303 ↓ 0.0 0 101

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
28. 0.202 0.202 ↓ 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.002..0.002 rows=0 loops=101)

  • Index Cond: ((m_product_id)::text = (product1_.m_product_id)::text)
  • Filter: ((logistics)::text = 'P'::text)
29. 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)