explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ETWN

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 40,267.146 ↑ 1.0 101 1

Limit (cost=92,475,724.91..92,481,264.77 rows=101 width=1,121) (actual time=40,120.027..40,267.146 rows=101 loops=1)

2. 148.018 40,267.112 ↑ 1,937.3 101 1

Result (cost=92,475,724.91..103,207,864.91 rows=195,663 width=1,121) (actual time=40,120.026..40,267.112 rows=101 loops=1)

3. 8.331 40,118.084 ↑ 1,937.3 101 1

Sort (cost=92,475,724.91..92,476,214.06 rows=195,663 width=811) (actual time=40,118.065..40,118.084 rows=101 loops=1)

  • Sort Key: locator2_.value
  • Sort Method: top-N heapsort Memory: 81kB
4. 10.928 40,109.753 ↑ 18.2 10,772 1

Nested Loop Left Join (cost=550,369.07..92,468,232.76 rows=195,663 width=811) (actual time=5,088.890..40,109.753 rows=10,772 loops=1)

5. 18.228 40,055.737 ↑ 18.2 10,772 1

Nested Loop Left Join (cost=550,368.66..92,383,331.90 rows=195,663 width=941) (actual time=5,088.877..40,055.737 rows=10,772 loops=1)

  • Join Filter: ((materialmg0_.c_uom_id)::text = (uom5_.c_uom_id)::text)
  • Rows Removed by Join Filter: 118492
6. 6.150 40,037.509 ↑ 18.2 10,772 1

Nested Loop Left Join (cost=550,368.66..92,292,169.12 rows=195,663 width=693) (actual time=5,088.860..40,037.509 rows=10,772 loops=1)

  • Join Filter: ((locator2_.m_warehouse_id)::text = (warehouse3_.m_warehouse_id)::text)
7. 3,880.632 40,031.359 ↑ 18.2 10,772 1

Nested Loop Left Join (cost=550,368.66..92,289,233.11 rows=195,663 width=408) (actual time=5,088.848..40,031.359 rows=10,772 loops=1)

  • Filter: ((upper((product1_.value)::text) ~~ '%ARE%'::text) OR (upper((product1_.upc)::text) ~~ '%ARE%'::text) OR (upper((product1_.name)::text) ~~ '%ARE%'::text) OR (upper((locator2_.value)::text) ~~ '%ARE%'::text) OR (...)
  • Rows Removed by Filter: 1687367
8. 734.692 32,754.449 ↓ 1.6 1,698,139 1

Nested Loop (cost=550,368.38..91,938,999.28 rows=1,059,772 width=395) (actual time=4,720.307..32,754.449 rows=1,698,139 loops=1)

9. 12,072.600 25,641.773 ↓ 3.0 3,188,992 1

Hash Left Join (cost=550,368.10..91,619,347.78 rows=1,063,743 width=239) (actual time=4,720.294..25,641.773 rows=3,188,992 loops=1)

  • Hash Cond: ((materialmg0_.m_attributesetinstance_id)::text = (attributes4_.m_attributesetinstance_id)::text)
10. 2,470.482 8,852.942 ↓ 3.0 3,188,992 1

Seq Scan on m_storage_detail materialmg0_ (cost=0.00..90,906,808.35 rows=1,063,743 width=174) (actual time=0.078..8,852.942 rows=3,188,992 loops=1)

  • Filter: ((qtyonhand > '0'::numeric) AND ((qtyonhand - (SubPlan 5)) > '0'::numeric))
  • Rows Removed by Filter: 2238
11.          

SubPlan (for Seq Scan)

12. 0.000 6,382.460 ↑ 1.0 1 3,191,230

Aggregate (cost=28.42..28.43 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=3,191,230)

13. 6,382.460 6,382.460 ↓ 0.0 0 3,191,230

Index Scan using obawo_task_storagedetail_ix on obawo_task obawo_task12_ (cost=0.28..28.40 rows=6 width=32) (actual time=0.002..0.002 rows=0 loops=3,191,230)

  • Index Cond: (((m_storage_detail_id)::text = (materialmg0_.m_storage_detail_id)::text) AND ((status)::text = 'AV'::text))
14. 2,986.724 4,716.231 ↑ 1.0 6,875,960 1

Hash (cost=356,981.60..356,981.60 rows=6,875,960 width=98) (actual time=4,716.231..4,716.231 rows=6,875,960 loops=1)

  • Buckets: 32768 Batches: 256 Memory Usage: 3716kB
15. 1,729.507 1,729.507 ↑ 1.0 6,875,960 1

Seq Scan on m_attributesetinstance attributes4_ (cost=0.00..356,981.60 rows=6,875,960 width=98) (actual time=0.004..1,729.507 rows=6,875,960 loops=1)

16. 6,377.984 6,377.984 ↑ 1.0 1 3,188,992

Index Scan using m_locator_key on m_locator locator2_ (cost=0.28..0.30 rows=1 width=189) (actual time=0.002..0.002 rows=1 loops=3,188,992)

  • Index Cond: ((m_locator_id)::text = (materialmg0_.m_locator_id)::text)
  • Filter: ((m_warehouse_id)::text = 'FCA81F56E9E643E096E86F5F379459FB'::text)
  • Rows Removed by Filter: 0
17. 3,396.278 3,396.278 ↑ 1.0 1 1,698,139

Index Scan using m_product_key on m_product product1_ (cost=0.28..0.30 rows=1 width=157) (actual time=0.002..0.002 rows=1 loops=1,698,139)

  • Index Cond: ((materialmg0_.m_product_id)::text = (m_product_id)::text)
18. 0.000 0.000 ↑ 1.0 1 10,772

Materialize (cost=0.00..1.07 rows=1 width=318) (actual time=0.000..0.000 rows=1 loops=10,772)

19. 0.005 0.005 ↑ 1.0 1 1

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

  • Filter: ((m_warehouse_id)::text = 'FCA81F56E9E643E096E86F5F379459FB'::text)
  • Rows Removed by Filter: 4
20. 0.000 0.000 ↑ 2.7 12 10,772

Materialize (cost=0.00..1.48 rows=32 width=252) (actual time=0.000..0.000 rows=12 loops=10,772)

21. 0.005 0.005 ↑ 2.7 12 1

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

22. 43.088 43.088 ↑ 1.0 1 10,772

Index Scan using m_refinventory_key on m_refinventory materialmg6_ (cost=0.41..0.43 rows=1 width=42) (actual time=0.004..0.004 rows=1 loops=10,772)

  • Index Cond: ((materialmg0_.m_refinventory_id)::text = (m_refinventory_id)::text)
23.          

SubPlan (for Result)

24. 0.101 0.101 ↓ 0.0 0 101

Index Scan using m_product_aum_product_id 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)
25. 0.300 0.404 ↓ 0.0 0 101

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

  • Hash Cond: ((uom9_.c_uom_id)::text = (productaum8_.c_uom_id)::text)
26. 0.003 0.003 ↑ 32.0 1 1

Seq Scan on c_uom uom9_ (cost=0.00..1.32 rows=32 width=220) (actual time=0.003..0.003 rows=1 loops=1)

27. 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
28. 0.101 0.101 ↓ 0.0 0 101

Index Scan using m_product_aum_unq on m_product_aum productaum8_ (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)
29. 0.000 0.000 ↓ 0.0 0 101

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

  • Index Cond: ((m_product_id)::text = (product1_.m_product_id)::text)
  • Filter: ((logistics)::text = 'P'::text)
30. 0.101 0.505 ↑ 1.0 1 101

Aggregate (cost=28.42..28.43 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=101)

31. 0.404 0.404 ↓ 0.0 0 101

Index Scan using obawo_task_storagedetail_ix on obawo_task obawo_task11_ (cost=0.28..28.40 rows=6 width=32) (actual time=0.004..0.004 rows=0 loops=101)

  • Index Cond: (((m_storage_detail_id)::text = (materialmg0_.m_storage_detail_id)::text) AND ((status)::text = 'AV'::text))
Planning time : 2.915 ms
Execution time : 40,267.333 ms