explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gIbH : Optimization for: plan #Q3vg

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 14.596 122.675 ↓ 1.0 4,188 1

Sort (cost=2,780.55..2,790.69 rows=4,055 width=89) (actual time=119.400..122.675 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=1818
2.          

Initplan (forSort)

3. 4.865 14.768 ↑ 1.0 1 1

Aggregate (cost=919.82..919.83 rows=1 width=32) (actual time=14.766..14.768 rows=1 loops=1)

  • Buffers: shared hit=747
4. 8.674 9.903 ↓ 1.0 4,188 1

Bitmap Heap Scan on m_storage fa (cost=105.85..909.68 rows=4,055 width=3) (actual time=1.334..9.903 rows=4,188 loops=1)

  • Recheck Cond: ((m_product_id = '1002273'::numeric) AND (m_locator_id = '1000585'::numeric))
  • Heap Blocks: exact=729
  • Buffers: shared hit=747
5. 1.229 1.229 ↓ 1.0 4,188 1

Bitmap Index Scan on m_storage_m_product_id_m_locator_id_idx (cost=0.00..104.84 rows=4,055 width=0) (actual time=1.228..1.229 rows=4,188 loops=1)

  • Index Cond: ((m_product_id = '1002273'::numeric) AND (m_locator_id = '1000585'::numeric))
  • Buffers: shared hit=18
6. 21.326 93.311 ↓ 1.0 4,188 1

Result (cost=960.37..1,617.72 rows=4,055 width=89) (actual time=33.232..93.311 rows=4,188 loops=1)

  • One-Time Filter: ($0 > '0'::numeric)
  • Buffers: shared hit=1812
7. 6.569 71.985 ↓ 1.0 4,188 1

Nested Loop (cost=960.37..1,617.72 rows=4,055 width=89) (actual time=18.448..71.985 rows=4,188 loops=1)

  • Buffers: shared hit=1065
8. 0.054 0.054 ↑ 1.0 1 1

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

  • Filter: (m_locator_id = '1000585'::numeric)
  • Rows Removed by Filter: 98
  • Buffers: shared hit=2
9. 29.122 65.362 ↓ 1.0 4,188 1

Hash Right Join (cost=960.37..1,573.99 rows=4,055 width=84) (actual time=18.408..65.362 rows=4,188 loops=1)

  • Hash Cond: (asi.m_attributesetinstance_id = s.m_attributesetinstance_id)
  • Filter: ((asi.guaranteedate IS NULL) OR (asi.guaranteedate > '2019-03-11 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=1063
10. 17.907 17.907 ↑ 1.0 23,061 1

Seq Scan on m_attributesetinstance asi (cost=0.00..551.73 rows=23,573 width=14) (actual time=0.012..17.907 rows=23,061 loops=1)

  • Buffers: shared hit=316
11. 7.959 18.333 ↓ 1.0 4,188 1

Hash (cost=909.68..909.68 rows=4,055 width=76) (actual time=18.333..18.333 rows=4,188 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 555kB
  • Buffers: shared hit=747
12. 8.735 10.374 ↓ 1.0 4,188 1

Bitmap Heap Scan on m_storage s (cost=105.85..909.68 rows=4,055 width=76) (actual time=1.768..10.374 rows=4,188 loops=1)

  • Recheck Cond: ((m_product_id = '1002273'::numeric) AND (m_locator_id = '1000585'::numeric))
  • Heap Blocks: exact=729
  • Buffers: shared hit=747
13. 1.639 1.639 ↓ 1.0 4,188 1

Bitmap Index Scan on m_storage_m_product_id_m_locator_id_idx (cost=0.00..104.84 rows=4,055 width=0) (actual time=1.639..1.639 rows=4,188 loops=1)

  • Index Cond: ((m_product_id = '1002273'::numeric) AND (m_locator_id = '1000585'::numeric))
  • Buffers: shared hit=18