explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RqED : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #jjT8; plan #R0k; plan #RqOF; plan #yjc; plan #fWeZ; plan #X2Wd; plan #DsHq; plan #PaBF; plan #TNrF; plan #DhCf; plan #snUU

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.098 111.061 ↓ 15.0 15 1

Sort (cost=4,154.34..4,154.34 rows=1 width=220) (actual time=111.059..111.061 rows=15 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 32kB
2. 0.239 110.963 ↓ 15.0 15 1

Nested Loop Left Join (cost=3,404.46..4,154.33 rows=1 width=220) (actual time=79.815..110.963 rows=15 loops=1)

3. 0.029 110.289 ↓ 15.0 15 1

Nested Loop Left Join (cost=3,404.18..4,153.85 rows=1 width=140) (actual time=79.513..110.289 rows=15 loops=1)

4. 0.139 53.995 ↓ 15.0 15 1

Nested Loop (cost=219.57..230.40 rows=1 width=116) (actual time=49.988..53.995 rows=15 loops=1)

  • Join Filter: (b.imb_id = widgets.imb_id)
  • Rows Removed by Join Filter: 953
5. 2.548 52.052 ↓ 22.0 22 1

GroupAggregate (cost=2.67..2.69 rows=1 width=20) (actual time=48.148..52.052 rows=22 loops=1)

  • Group Key: widgets.imb_id
6. 3.238 49.504 ↓ 11,206.0 11,206 1

Sort (cost=2.67..2.67 rows=1 width=12) (actual time=48.143..49.504 rows=11,206 loops=1)

  • Sort Key: widgets.imb_id
  • Sort Method: quicksort Memory: 910kB
7. 46.266 46.266 ↓ 11,206.0 11,206 1

Index Scan using widgets_date_idx on widgets (cost=0.44..2.66 rows=1 width=12) (actual time=0.067..46.266 rows=11,206 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 100514
8. 1.656 1.804 ↑ 3.3 44 22

HashAggregate (cost=216.90..224.46 rows=144 width=100) (actual time=0.012..0.082 rows=44 loops=22)

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
9. 0.148 0.148 ↑ 3.5 44 1

Index Scan using boosts_pkey on campaigns b (cost=0.29..208.71 rows=156 width=36) (actual time=0.074..0.148 rows=44 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Filter: ((ts_id IS NOT NULL) AND (cost > '0'::double precision))
  • Rows Removed by Filter: 240
10. 0.237 56.265 ↑ 1.0 1 15

Hash Join (cost=3,184.61..3,923.44 rows=1 width=28) (actual time=3.749..3.751 rows=1 loops=15)

  • Hash Cond: ((d1.imb_id = campaigns.imb_id) AND (d1.date = (max(campaigns.date))))
11. 28.800 28.800 ↑ 2.4 37 15

Index Scan using boosts_pkey on campaigns d1 (cost=0.29..718.21 rows=88 width=32) (actual time=1.739..1.920 rows=37 loops=15)

  • Index Cond: (b.imb_id = imb_id)
  • Filter: (bid IS NOT NULL)
  • Rows Removed by Filter: 7
12. 0.106 27.228 ↓ 1.1 978 1

Hash (cost=3,170.37..3,170.37 rows=930 width=8) (actual time=27.228..27.228 rows=978 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
13. 18.417 27.122 ↓ 1.1 978 1

HashAggregate (cost=3,151.77..3,161.07 rows=930 width=8) (actual time=27.023..27.122 rows=978 loops=1)

  • Group Key: campaigns.imb_id
14. 8.705 8.705 ↓ 1.0 94,344 1

Seq Scan on campaigns (cost=0.00..2,680.18 rows=94,318 width=8) (actual time=0.021..8.705 rows=94,344 loops=1)

15. 0.435 0.435 ↑ 1.0 1 15

Index Scan using campaign_names_pk on campaign_names_groups names (cost=0.28..0.45 rows=1 width=43) (actual time=0.029..0.029 rows=1 loops=15)

  • Index Cond: (imb_id = b.imb_id)