explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X2Wd : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #jjT8; plan #R0k; plan #RqOF; plan #yjc; plan #fWeZ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.029 37,305.650 ↑ 122.1 15 1

Sort (cost=213,997.83..214,002.41 rows=1,831 width=220) (actual time=37,305.648..37,305.650 rows=15 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 32kB
2. 0.101 37,305.621 ↑ 122.1 15 1

Hash Join (cost=213,774.45..213,898.61 rows=1,831 width=220) (actual time=37,305.558..37,305.621 rows=15 loops=1)

  • Hash Cond: (widgets.imb_id = b.imb_id)
3. 66.022 37,219.782 ↑ 11.6 71 1

HashAggregate (cost=207,476.58..207,484.79 rows=821 width=20) (actual time=37,219.763..37,219.782 rows=71 loops=1)

  • Group Key: widgets.imb_id
4. 37,153.760 37,153.760 ↑ 7.5 166,325 1

Index Scan using widgets_date_idx on widgets (cost=0.44..198,071.04 rows=1,254,072 width=12) (actual time=1.152..37,153.760 rows=166,325 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 1457223
5. 0.037 85.738 ↑ 10.1 44 1

Hash (cost=6,292.29..6,292.29 rows=446 width=163) (actual time=85.738..85.738 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
6. 0.039 85.701 ↑ 10.1 44 1

Hash Left Join (cost=4,396.51..6,292.29 rows=446 width=163) (actual time=80.579..85.701 rows=44 loops=1)

  • Hash Cond: (b.imb_id = names.imb_id)
7. 0.131 84.976 ↑ 10.1 44 1

Hash Right Join (cost=4,309.59..6,199.24 rows=446 width=124) (actual time=79.881..84.976 rows=44 loops=1)

  • Hash Cond: (d1.imb_id = b.imb_id)
8. 0.034 73.784 ↓ 4.4 580 1

Nested Loop (cost=3,567.46..5,448.76 rows=132 width=28) (actual time=66.870..73.784 rows=580 loops=1)

9. 26.314 66.904 ↑ 1.0 978 1

HashAggregate (cost=3,567.05..3,576.83 rows=978 width=8) (actual time=66.540..66.904 rows=978 loops=1)

  • Group Key: campaigns.imb_id
10. 40.590 40.590 ↑ 1.0 97,803 1

Seq Scan on campaigns (cost=0.00..3,078.03 rows=97,803 width=8) (actual time=0.014..40.590 rows=97,803 loops=1)

11. 6.846 6.846 ↑ 1.0 1 978

Index Scan using boosts_pkey on campaigns d1 (cost=0.42..1.89 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=978)

  • Index Cond: ((date = (max(campaigns.date))) AND (imb_id = campaigns.imb_id))
  • Filter: (bid IS NOT NULL)
  • Rows Removed by Filter: 0
12. 0.024 11.061 ↑ 10.1 44 1

Hash (cost=736.55..736.55 rows=446 width=100) (actual time=11.061..11.061 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.325 11.037 ↑ 10.1 44 1

HashAggregate (cost=708.68..732.09 rows=446 width=100) (actual time=10.956..11.037 rows=44 loops=1)

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
14. 10.712 10.712 ↑ 2.4 243 1

Index Scan using boosts_pkey on campaigns b (cost=0.42..677.49 rows=594 width=36) (actual time=0.348..10.712 rows=243 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: 1310
15. 0.321 0.686 ↑ 1.0 1,552 1

Hash (cost=67.52..67.52 rows=1,552 width=43) (actual time=0.686..0.686 rows=1,552 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 118kB
16. 0.365 0.365 ↑ 1.0 1,552 1

Seq Scan on campaign_names_groups names (cost=0.00..67.52 rows=1,552 width=43) (actual time=0.007..0.365 rows=1,552 loops=1)

Planning time : 1.979 ms
Execution time : 37,305.846 ms