explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.480 550.576 ↓ 36.9 480 1

Nested Loop (cost=0.55..6,408.13 rows=13 width=523) (actual time=178.982..550.576 rows=480 loops=1)

2. 0.732 450.896 ↓ 36.0 540 1

Nested Loop (cost=0.00..6,273.01 rows=15 width=273) (actual time=177.306..450.896 rows=540 loops=1)

  • Join Filter: ((plist.m_pricelist_id)::text = (prversion.m_pricelist_id)::text)
  • Rows Removed by Join Filter: 3,780
3. 0.020 0.020 ↑ 1.0 27 1

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

4. 0.401 450.144 ↓ 32.0 160 27

Materialize (cost=0.00..6,268.73 rows=5 width=273) (actual time=2.440..16.672 rows=160 loops=27)

5. 0.064 449.743 ↓ 32.0 160 1

Nested Loop (cost=0.00..6,268.70 rows=5 width=273) (actual time=65.868..449.743 rows=160 loops=1)

6. 0.015 0.015 ↓ 8.0 8 1

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

  • Filter: (isactive = 'Y'::bpchar)
7. 449.664 449.664 ↓ 4.0 20 8

Seq Scan on m_product prod (cost=0.00..6,267.55 rows=5 width=191) (actual time=48.554..56.208 rows=20 loops=8)

  • Filter: (((value)::text ~~* '%10008%'::text) AND (isgeneric = 'N'::bpchar) AND (isactive = 'Y'::bpchar))
  • Rows Removed by Filter: 55,840
8. 97.200 97.200 ↑ 1.0 1 540

Index Scan using m_productprice_pricelist_ve_un on m_productprice price (cost=0.55..8.57 rows=1 width=109) (actual time=0.180..0.180 rows=1 loops=540)

  • Index Cond: (((m_pricelist_version_id)::text = (prversion.m_pricelist_version_id)::text) AND ((m_product_id)::text = (prod.m_product_id)::text))
Planning time : 1.089 ms
Execution time : 550.727 ms