explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q3vg

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 22.075 35,445.854 ↓ 3.1 4,188 1

Sort (cost=244,490.56..244,493.97 rows=1,364 width=89) (actual time=35,443.249..35,445.854 rows=4,188 loops=1)

  • Sort Key: l.priorityno DESC, asi.guaranteedate, s.m_attributesetinstance_id, s.qtyonhand DESC
  • Sort Method: quicksort Memory: 781kB
  • Buffers: shared hit=3269937
2. 25.543 35,423.779 ↓ 3.1 4,188 1

Nested Loop Left Join (cost=0.70..244,419.54 rows=1,364 width=89) (actual time=12.750..35,423.779 rows=4,188 loops=1)

  • Filter: ((asi.guaranteedate IS NULL) OR (asi.guaranteedate > '2019-03-11 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=3269931
3. 38.552 35,364.732 ↓ 3.1 4,188 1

Nested Loop (cost=0.41..243,478.60 rows=1,364 width=81) (actual time=12.710..35,364.732 rows=4,188 loops=1)

  • Join Filter: ((SubPlan 1) > '0'::numeric)
  • Buffers: shared hit=3257355
4. 8.776 8.776 ↓ 1.0 4,188 1

Index Scan using m_storage_pkey on m_storage s (cost=0.41..1,692.71 rows=4,093 width=76) (actual time=0.053..8.776 rows=4,188 loops=1)

  • Index Cond: ((m_product_id = '1002273'::numeric) AND (m_locator_id = '1000585'::numeric))
  • Buffers: shared hit=3278
5. 4.169 4.188 ↑ 1.0 1 4,188

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

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

Seq Scan on m_locator l (cost=0.00..3.17 rows=1 width=11) (actual time=0.018..0.019 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,370.892 35,313.216 ↑ 1.0 1 4,188

Aggregate (cost=59.05..59.06 rows=1 width=32) (actual time=8.431..8.432 rows=1 loops=4,188)

  • Buffers: shared hit=3254076
9. 18,942.324 18,942.324 ↓ 161.1 4,188 4,188

Index Scan using m_storage_m_product_id_m_locator_id_idx on m_storage fa (cost=0.29..58.98 rows=26 width=3) (actual time=0.015..4.523 rows=4,188 loops=4,188)

  • Index Cond: ((m_product_id = s.m_product_id) AND (m_locator_id = l.m_locator_id))
  • Buffers: shared hit=3254076
10. 33.504 33.504 ↑ 1.0 1 4,188

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

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