explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Wpk

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 24.380 36,675.917 ↓ 3.1 4,192 1

Sort (cost=233,713.19..233,716.57 rows=1,350 width=89) (actual time=36,673.430..36,675.917 rows=4,192 loops=1)

  • Sort Key: l.priorityno DESC, asi.guaranteedate, s.m_attributesetinstance_id, s.qtyonhand DESC
  • Sort Method: quicksort Memory: 782kB
  • Buffers: shared hit=3277252
2. 27.529 36,651.537 ↓ 3.1 4,192 1

Nested Loop Left Join (cost=0.70..233,643.00 rows=1,350 width=89) (actual time=12.599..36,651.537 rows=4,192 loops=1)

  • Filter: ((asi.guaranteedate IS NULL) OR (asi.guaranteedate > '2019-03-11 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=3277246
3. 43.415 36,586.280 ↓ 3.1 4,192 1

Nested Loop (cost=0.41..232,703.84 rows=1,350 width=81) (actual time=12.574..36,586.280 rows=4,192 loops=1)

  • Join Filter: ((SubPlan 1) > '0'::numeric)
  • Buffers: shared hit=3264658
4. 9.585 9.585 ↓ 1.0 4,192 1

Index Scan using m_storage_pkey on m_storage s (cost=0.41..1,703.27 rows=4,051 width=76) (actual time=0.049..9.585 rows=4,192 loops=1)

  • Index Cond: ((m_product_id = '1002273'::numeric) AND (m_locator_id = '1000585'::numeric))
  • Buffers: shared hit=3281
5. 4.167 4.192 ↑ 1.0 1 4,192

Materialize (cost=0.00..3.18 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=4,192)

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

Seq Scan on m_locator l (cost=0.00..3.17 rows=1 width=11) (actual time=0.024..0.025 rows=1 loops=1)

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

SubPlan (forNested Loop)

8. 16,956.640 36,529.088 ↑ 1.0 1 4,192

Aggregate (cost=57.00..57.01 rows=1 width=32) (actual time=8.713..8.714 rows=1 loops=4,192)

  • Buffers: shared hit=3261376
9. 19,572.448 19,572.448 ↓ 167.7 4,192 4,192

Index Scan using m_storage_m_product_id_m_locator_id_idx on m_storage fa (cost=0.29..56.93 rows=25 width=3) (actual time=0.017..4.669 rows=4,192 loops=4,192)

  • Index Cond: ((m_product_id = s.m_product_id) AND (m_locator_id = l.m_locator_id))
  • Buffers: shared hit=3261376
10. 37.728 37.728 ↑ 1.0 1 4,192

Index Scan using m_attributesetinstance_pkey on m_attributesetinstance asi (cost=0.29..0.68 rows=1 width=14) (actual time=0.009..0.009 rows=1 loops=4,192)

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