explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MjK : Optimization for: plan #3Wpk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 8.572 10,120.576 ↓ 3.1 4,186 1

Sort (cost=309,974.32..309,977.71 rows=1,357 width=89) (actual time=10,120.376..10,120.576 rows=4,186 loops=1)

  • Sort Key: l.priorityno DESC, asi.guaranteedate, s.m_attributesetinstance_id, s.qtyonhand DESC
  • Sort Method: quicksort Memory: 781kB
  • Buffers: shared hit=3109271
2. 7.633 10,112.004 ↓ 3.1 4,186 1

Nested Loop Left Join (cost=0.70..309,903.71 rows=1,357 width=89) (actual time=7.596..10,112.004 rows=4,186 loops=1)

  • Filter: ((asi.guaranteedate IS NULL) OR (asi.guaranteedate > '2019-03-11 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=3109265
3. 12.844 10,087.627 ↓ 3.1 4,186 1

Nested Loop (cost=0.41..308,974.20 rows=1,357 width=81) (actual time=7.567..10,087.627 rows=4,186 loops=1)

  • Join Filter: ((SubPlan 1) > '0'::numeric)
  • Buffers: shared hit=3096695
4. 3.267 3.267 ↓ 1.0 4,186 1

Index Scan using m_storage_pkey on m_storage s (cost=0.41..1,651.57 rows=4,071 width=76) (actual time=0.094..3.267 rows=4,186 loops=1)

  • Index Cond: ((m_product_id = '1002273'::numeric) AND (m_locator_id = '1000585'::numeric))
  • Buffers: shared hit=3240
5. 0.000 0.000 ↑ 1.0 1 4,186

Materialize (cost=0.00..3.24 rows=1 width=11) (actual time=0.000..0.000 rows=1 loops=4,186)

  • Buffers: shared hit=1
6. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on m_locator l (cost=0.00..3.24 rows=1 width=11) (actual time=0.027..0.027 rows=1 loops=1)

  • Filter: (m_locator_id = '1000585'::numeric)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=1
7.          

SubPlan (forNested Loop)

8. 4,198.558 10,071.516 ↑ 1.0 1 4,186

Aggregate (cost=75.46..75.47 rows=1 width=32) (actual time=2.406..2.406 rows=1 loops=4,186)

  • Buffers: shared hit=3093454
9. 5,872.958 5,872.958 ↓ 123.1 4,186 4,186

Index Scan using m_storage_m_product_id_m_locator_id_idx on m_storage fa (cost=0.29..75.38 rows=34 width=3) (actual time=0.023..1.403 rows=4,186 loops=4,186)

  • Index Cond: ((m_product_id = s.m_product_id) AND (m_locator_id = l.m_locator_id))
  • Buffers: shared hit=3093454
10. 16.744 16.744 ↑ 1.0 1 4,186

Index Scan using m_attributesetinstance_pkey on m_attributesetinstance asi (cost=0.29..0.67 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=4,186)

  • Index Cond: (s.m_attributesetinstance_id = m_attributesetinstance_id)
  • Buffers: shared hit=12570