explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

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

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

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

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

3. 0.053 0.124 ↓ 9.0 27 1

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

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

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

5. 0.007 0.034 ↓ 8.0 8 1

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

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

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

  • Filter: (isactive = 'Y'::bpchar)
7. 353.025 449.361 ↑ 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=4.031..16.643 rows=47,525 loops=27)

  • Recheck Cond: ((m_pricelist_version_id)::text = (prversion.m_pricelist_version_id)::text)
  • Heap Blocks: exact=85,746
8. 96.336 96.336 ↑ 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.568..3.568 rows=47,525 loops=27)

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

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

  • Buckets: 32,768 Batches: 4 Memory Usage: 3,169kB
10. 50.196 50.196 ↑ 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.051..50.196 rows=52,959 loops=1)

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