explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aPmw

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,580.372 ↑ 1.0 1 1

Limit (cost=951,628.81..952,210.20 rows=1 width=4) (actual time=1,455.387..1,580.372 rows=1 loops=1)

2.          

CTE ids

3. 0.675 1,579.462 ↑ 23.6 201 1

Nested Loop Left Join (cost=19,393.68..951,628.81 rows=4,746 width=45) (actual time=1,120.657..1,579.462 rows=201 loops=1)

4. 0.683 1,577.380 ↑ 23.6 201 1

Nested Loop Left Join (cost=19,393.68..948,388.90 rows=4,746 width=36) (actual time=1,120.578..1,577.380 rows=201 loops=1)

5. 0.411 1,575.692 ↑ 23.6 201 1

Nested Loop Left Join (cost=19,393.68..944,964.27 rows=4,746 width=20) (actual time=1,120.555..1,575.692 rows=201 loops=1)

6. 0.442 1,574.678 ↑ 23.6 201 1

Nested Loop Left Join (cost=19,393.68..764,108.26 rows=4,746 width=16) (actual time=1,120.547..1,574.678 rows=201 loops=1)

7. 0.634 1,573.231 ↑ 23.6 201 1

Nested Loop Left Join (cost=19,393.68..583,252.24 rows=4,746 width=12) (actual time=1,120.534..1,573.231 rows=201 loops=1)

8. 331.557 1,430.289 ↑ 23.6 201 1

Bitmap Heap Scan on ads (cost=19,393.68..402,396.22 rows=4,746 width=8) (actual time=1,119.593..1,430.289 rows=201 loops=1)

  • Recheck Cond: (category = 4,089)
  • Filter: (((status)::text = 'active'::text) AND (modified_at < (now() - '2 mons'::interval)))
  • Rows Removed by Filter: 65,735
9. 1,098.732 1,098.732 ↑ 2.3 66,357 1

Bitmap Index Scan on index_ads_category (cost=0.00..19,392.50 rows=150,079 width=0) (actual time=1,098.732..1,098.732 rows=66,357 loops=1)

  • Index Cond: (category = 4,089)
10. 142.308 142.308 ↓ 0.0 0 201

Index Scan using ad_params_pkey on ad_params ext (cost=0.00..38.10 rows=1 width=8) (actual time=0.708..0.708 rows=0 loops=201)

  • Index Cond: ((ads.ad_id = ad_id) AND ((name)::text = 'external_ad_id'::text))
11. 1.005 1.005 ↓ 0.0 0 201

Index Scan using ad_params_pkey on ad_params next_bump (cost=0.00..38.10 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=201)

  • Index Cond: ((ads.ad_id = ad_id) AND ((name)::text = 'next_bump'::text))
12. 0.603 0.603 ↓ 0.0 0 201

Index Scan using ad_params_pkey on ad_params pp_end (cost=0.00..38.10 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=201)

  • Index Cond: ((ads.ad_id = ad_id) AND ((name)::text = 'polepos_end'::text))
13. 1.005 1.005 ↑ 1.0 1 201

Index Scan using stores_pkey on stores store (cost=0.00..0.71 rows=1 width=20) (actual time=0.004..0.005 rows=1 loops=201)

  • Index Cond: (ads.store_id = store_id)
14. 1.407 1.407 ↑ 1.0 1 201

Index Scan using store_params_pkey on store_params plan (cost=0.00..0.67 rows=1 width=17) (actual time=0.006..0.007 rows=1 loops=201)

  • Index Cond: ((store.store_id = store_id) AND ((name)::text = 'plan'::text))
15. 1,580.368 1,580.368 ↑ 1.0 1 1

CTE Scan on ids (cost=0.00..581.38 rows=1 width=4) (actual time=1,455.385..1,580.368 rows=1 loops=1)

  • Filter: ((external_ad_id IS NULL) AND (next_bump IS NULL) AND ((polepos_end IS NULL) OR (to_timestamp(((polepos_end)::integer)::double precision) < now())) AND (((store_end IS NOT NULL) AND (store_plan <> ALL ('{boat,caravan,cardealer,machinery,moto,plan12l,plan12l_inv,plan12ld10,plan12ld10_inv,plan12ld15,plan12ld15_inv,plan12ld20,plan12ld20_inv,plan12ld25,plan12ld25_inv,plan12ld5,plan12ld5_inv,plan6l,plan6l_inv,plan6ld10,plan6ld10_inv,plan6ld15,plan6ld15_inv,plan6ld20,plan6ld20_inv,plan6ld25,plan6ld25_inv,plan6ld5,plan6ld5_inv,realestate,sparepart,vepsalainen}'::text[]))) OR ((store_end IS NOT NULL) AND ((store_end + '7 days'::interval) < now()) AND (store_plan = ANY ('{boat,caravan,cardealer,machinery,moto,plan12l,plan12l_inv,plan12ld10,plan12ld10_inv,plan12ld15,plan12ld15_inv,plan12ld20,plan12ld20_inv,plan12ld25,plan12ld25_inv,plan12ld5,plan12ld5_inv,plan6l,plan6l_inv,plan6ld10,plan6ld10_inv,plan6ld15,plan6ld15_inv,plan6ld20,plan6ld20_inv,plan6ld25,plan6ld25_inv,plan6ld5,plan6ld5_inv,realestate,sparepart,vepsalainen}'::text[]))) OR (store_end IS NULL)))
  • Rows Removed by Filter: 200
Total runtime : 1,580.504 ms