explain.depesz.com

PostgreSQL's explain analyze made readable

Result: snUU : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.348 316.462 ↓ 26.0 26 1

Sort (cost=4,088.60..4,088.60 rows=1 width=220) (actual time=316.459..316.462 rows=26 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 38kB
2. 0.452 316.114 ↓ 26.0 26 1

Nested Loop Left Join (cost=3,338.14..4,088.59 rows=1 width=220) (actual time=256.916..316.114 rows=26 loops=1)

3. 0.038 315.324 ↓ 26.0 26 1

Nested Loop Left Join (cost=3,337.86..4,088.04 rows=1 width=140) (actual time=256.432..315.324 rows=26 loops=1)

4. 0.118 181.932 ↓ 26.0 26 1

Nested Loop (cost=153.25..160.71 rows=1 width=116) (actual time=162.326..181.932 rows=26 loops=1)

  • Join Filter: (b.imb_id = widgets.imb_id)
  • Rows Removed by Join Filter: 1118
5. 11.947 179.708 ↓ 26.0 26 1

GroupAggregate (cost=2.67..2.69 rows=1 width=20) (actual time=161.568..179.708 rows=26 loops=1)

  • Group Key: widgets.imb_id
6. 22.997 167.761 ↓ 100,401.0 100,401 1

Sort (cost=2.67..2.67 rows=1 width=12) (actual time=160.864..167.761 rows=100,401 loops=1)

  • Sort Key: widgets.imb_id
  • Sort Method: quicksort Memory: 7779kB
7. 144.764 144.764 ↓ 100,401.0 100,401 1

Index Scan using widgets_date_idx on widgets (cost=0.44..2.66 rows=1 width=12) (actual time=2.286..144.764 rows=100,401 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Filter: (source_id = 30)
  • Rows Removed by Filter: 11319
8. 1.454 2.106 ↑ 2.2 44 26

HashAggregate (cost=150.58..155.78 rows=99 width=100) (actual time=0.031..0.081 rows=44 loops=26)

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

Index Scan using boosts_pkey on campaigns b (cost=0.29..145.07 rows=105 width=36) (actual time=0.161..0.652 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: 214
10. 0.800 133.354 ↑ 1.0 1 26

Hash Join (cost=3,184.61..3,927.32 rows=1 width=28) (actual time=5.129..5.129 rows=1 loops=26)

  • Hash Cond: ((d1.imb_id = campaigns.imb_id) AND (d1.date = (max(campaigns.date))))
11. 62.634 62.634 ↑ 44.0 2 26

Index Scan using boosts_pkey on campaigns d1 (cost=0.29..722.09 rows=88 width=32) (actual time=2.396..2.409 rows=2 loops=26)

  • Index Cond: (b.imb_id = imb_id)
  • Filter: (bid IS NOT NULL)
  • Rows Removed by Filter: 4
12. 0.110 69.920 ↓ 1.1 978 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
13. 17.705 69.810 ↓ 1.1 978 1

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

  • Group Key: campaigns.imb_id
14. 52.105 52.105 ↑ 1.0 94,318 1

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

15. 0.338 0.338 ↓ 0.0 0 26

Index Scan using campaign_names_pk on campaign_names_groups names (cost=0.28..0.52 rows=1 width=43) (actual time=0.013..0.013 rows=0 loops=26)

  • Index Cond: (imb_id = b.imb_id)
Planning time : 7.716 ms
Execution time : 317.841 ms