explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w8W : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: 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; plan #RqED; plan #YYF; plan #rV1N; plan #jPAj; plan #WFzh

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.056 253.782 ↓ 26.0 26 1

Sort (cost=4,156.56..4,156.56 rows=1 width=220) (actual time=253.778..253.782 rows=26 loops=1)

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

Nested Loop Left Join (cost=3,406.27..4,156.55 rows=1 width=220) (actual time=166.703..253.726 rows=26 loops=1)

3. 0.044 253.444 ↓ 26.0 26 1

Nested Loop Left Join (cost=3,405.99..4,156.07 rows=1 width=140) (actual time=166.686..253.444 rows=26 loops=1)

4. 0.136 190.064 ↓ 26.0 26 1

Nested Loop (cost=219.57..230.40 rows=1 width=116) (actual time=142.190..190.064 rows=26 loops=1)

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

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

  • Group Key: widgets.imb_id
6. 64.288 158.153 ↓ 264,245.0 264,245 1

Sort (cost=2.67..2.67 rows=1 width=12) (actual time=139.514..158.153 rows=264,245 loops=1)

  • Sort Key: widgets.imb_id
  • Sort Method: quicksort Memory: 21125kB
7. 93.865 93.865 ↓ 264,245.0 264,245 1

Index Scan using widgets_date_idx on widgets (cost=0.44..2.66 rows=1 width=12) (actual time=0.014..93.865 rows=264,245 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.338 1.430 ↑ 3.3 44 26

HashAggregate (cost=216.90..224.46 rows=144 width=100) (actual time=0.009..0.055 rows=44 loops=26)

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

Index Scan using boosts_pkey on campaigns b (cost=0.29..208.71 rows=156 width=36) (actual time=0.057..0.092 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.112 63.336 ↑ 1.0 1 26

Hash Join (cost=3,186.42..3,925.66 rows=1 width=28) (actual time=2.436..2.436 rows=1 loops=26)

  • Hash Cond: ((d1.imb_id = campaigns.imb_id) AND (d1.date = (max(campaigns.date))))
11. 40.274 40.274 ↑ 29.3 3 26

Index Scan using boosts_pkey on campaigns d1 (cost=0.29..718.62 rows=88 width=32) (actual time=1.540..1.549 rows=3 loops=26)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
13. 15.559 22.847 ↓ 1.1 978 1

HashAggregate (cost=3,153.58..3,162.88 rows=930 width=8) (actual time=22.753..22.847 rows=978 loops=1)

  • Group Key: campaigns.imb_id
14. 7.288 7.288 ↑ 1.0 94,344 1

Seq Scan on campaigns (cost=0.00..2,681.72 rows=94,372 width=8) (actual time=0.017..7.288 rows=94,344 loops=1)

15. 0.078 0.078 ↓ 0.0 0 26

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

  • Index Cond: (imb_id = b.imb_id)
Planning time : 0.754 ms
Execution time : 256.543 ms