explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J86F : Optimization for: Optimization for: Optimization for: plan #EsfA; plan #wHXu; plan #n6ew

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,343.738 3,088.230 ↓ 9.0 1,217,845 1

Hash Join (cost=9,541.80..107,532.56 rows=135,284 width=524) (actual time=81.860..3,088.230 rows=1,217,845 loops=1)

  • Hash Cond: ((price.m_product_id)::text = (prod.m_product_id)::text)
2. 235.894 671.300 ↓ 9.0 1,283,175 1

Nested Loop (cost=1,284.23..23,098.49 rows=142,575 width=158) (actual time=8.570..671.300 rows=1,283,175 loops=1)

3. 0.060 0.139 ↓ 9.0 27 1

Hash Join (cost=1.11..3.46 rows=3 width=82) (actual time=0.076..0.139 rows=27 loops=1)

  • Hash Cond: ((prversion.m_pricelist_id)::text = (plist.m_pricelist_id)::text)
4. 0.044 0.044 ↑ 1.0 27 1

Seq Scan on m_pricelist_version prversion (cost=0.00..2.27 rows=27 width=164) (actual time=0.019..0.044 rows=27 loops=1)

5. 0.010 0.035 ↓ 8.0 8 1

Hash (cost=1.10..1.10 rows=1 width=82) (actual time=0.035..0.035 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
6. 0.025 0.025 ↓ 8.0 8 1

Seq Scan on m_pricelist plist (cost=0.00..1.10 rows=1 width=82) (actual time=0.019..0.025 rows=8 loops=1)

  • Filter: (isactive = 'Y'::bpchar)
7. 349.407 435.267 ↑ 1.0 47,525 27

Bitmap Heap Scan on m_productprice price (cost=1,283.12..7,223.09 rows=47,525 width=109) (actual time=3.585..16.121 rows=47,525 loops=27)

  • Recheck Cond: ((m_pricelist_version_id)::text = (prversion.m_pricelist_version_id)::text)
  • Heap Blocks: exact=85,746
8. 85.860 85.860 ↑ 1.0 47,525 27

Bitmap Index Scan on m_productprice_plv (cost=0.00..1,271.24 rows=47,525 width=0) (actual time=3.180..3.180 rows=47,525 loops=27)

  • Index Cond: ((m_pricelist_version_id)::text = (prversion.m_pricelist_version_id)::text)
9. 32.420 73.192 ↑ 1.0 52,959 1

Hash (cost=6,170.58..6,170.58 rows=53,679 width=192) (actual time=73.192..73.192 rows=52,959 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 3,169kB
10. 40.772 40.772 ↑ 1.0 52,959 1

Seq Scan on m_product prod (cost=0.00..6,170.58 rows=53,679 width=192) (actual time=0.010..40.772 rows=52,959 loops=1)

  • Filter: ((isgeneric = 'N'::bpchar) AND (isactive = 'Y'::bpchar))
  • Rows Removed by Filter: 2,901
Planning time : 2.678 ms
Execution time : 3,126.051 ms