explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.101 16,276.492 ↑ 70.4 26 1

Sort (cost=62,587.70..62,592.28 rows=1,831 width=220) (actual time=16,276.476..16,276.492 rows=26 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 38kB
2. 0.353 16,276.391 ↑ 70.4 26 1

Hash Join (cost=60,486.35..62,488.47 rows=1,831 width=220) (actual time=16,206.321..16,276.391 rows=26 loops=1)

  • Hash Cond: (b.imb_id = imb.imb_id)
3. 0.070 265.970 ↑ 10.1 44 1

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

  • Hash Cond: (b.imb_id = names.imb_id)
4. 0.205 262.771 ↑ 10.1 44 1

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

  • Hash Cond: (d1.imb_id = b.imb_id)
5. 0.266 176.517 ↓ 4.4 580 1

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

6. 42.376 101.923 ↑ 1.0 978 1

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

  • Group Key: campaigns.imb_id
7. 59.547 59.547 ↑ 1.0 97,803 1

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

8. 74.328 74.328 ↑ 1.0 1 978

Index Scan using boosts_pkey on campaigns d1 (cost=0.42..1.89 rows=1 width=32) (actual time=0.074..0.076 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
9. 0.036 86.049 ↑ 10.1 44 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.579 86.013 ↑ 10.1 44 1

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

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

Index Scan using boosts_pkey on campaigns b (cost=0.42..677.49 rows=594 width=36) (actual time=19.399..85.434 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
12. 0.577 3.129 ↑ 1.0 1,552 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 118kB
13. 2.552 2.552 ↑ 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.335..2.552 rows=1,552 loops=1)

14. 0.015 16,010.068 ↑ 31.6 26 1

Hash (cost=56,079.58..56,079.58 rows=821 width=20) (actual time=16,010.068..16,010.068 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.001 16,010.053 ↑ 31.6 26 1

Subquery Scan on imb (cost=56,063.16..56,079.58 rows=821 width=20) (actual time=16,010.042..16,010.053 rows=26 loops=1)

16. 585.935 16,010.052 ↑ 31.6 26 1

HashAggregate (cost=56,063.16..56,071.37 rows=821 width=20) (actual time=16,010.042..16,010.052 rows=26 loops=1)

  • Group Key: widgets.imb_id
17. 13,099.087 15,424.117 ↓ 21.8 1,455,810 1

Bitmap Heap Scan on widgets (cost=36,733.68..55,561.98 rows=66,824 width=12) (actual time=2,341.603..15,424.117 rows=1,455,810 loops=1)

  • Recheck Cond: ((source_id = 30) AND (date > '2019-01-01'::date) AND (date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Rows Removed by Index Recheck: 24003564
  • Heap Blocks: exact=50983 lossy=446463
18. 64.368 2,325.030 ↓ 0.0 0 1

BitmapAnd (cost=36,733.68..36,733.68 rows=17,112 width=0) (actual time=2,325.030..2,325.030 rows=0 loops=1)

19. 993.489 993.489 ↓ 4.0 3,540,294 1

Bitmap Index Scan on widgets_source_id_idx (cost=0.00..9,314.95 rows=885,682 width=0) (actual time=993.489..993.489 rows=3,540,294 loops=1)

  • Index Cond: (source_id = 30)
20. 1,267.173 1,267.173 ↓ 1.1 1,653,164 1

Bitmap Index Scan on widgets_pkey (cost=0.00..27,385.07 rows=1,558,150 width=0) (actual time=1,267.173..1,267.173 rows=1,653,164 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))