explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.053 10,300.284 ↑ 61.0 26 1

Sort (cost=69,477.96..69,481.92 rows=1,585 width=220) (actual time=10,300.280..10,300.284 rows=26 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 38kB
2. 0.161 10,300.231 ↑ 61.0 26 1

Hash Join (cost=67,461.12..69,393.71 rows=1,585 width=220) (actual time=10,292.662..10,300.231 rows=26 loops=1)

  • Hash Cond: (b.imb_id = imb.imb_id)
3. 0.056 54.239 ↑ 10.8 44 1

Hash Left Join (cost=4,454.64..6,294.92 rows=473 width=163) (actual time=46.600..54.239 rows=44 loops=1)

  • Hash Cond: (b.imb_id = names.imb_id)
4. 0.161 53.401 ↑ 10.8 44 1

Hash Right Join (cost=4,367.69..6,201.47 rows=473 width=124) (actual time=45.804..53.401 rows=44 loops=1)

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

Nested Loop (cost=3,567.46..5,392.50 rows=132 width=28) (actual time=43.330..50.845 rows=580 loops=1)

6. 26.237 43.691 ↓ 1.0 978 1

HashAggregate (cost=3,567.05..3,576.45 rows=940 width=8) (actual time=43.307..43.691 rows=978 loops=1)

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

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

8. 6.846 6.846 ↑ 1.0 1 978

Index Scan using boosts_pkey on campaigns d1 (cost=0.42..1.91 rows=1 width=32) (actual time=0.007..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
9. 0.017 2.395 ↑ 10.8 44 1

Hash (cost=794.32..794.32 rows=473 width=100) (actual time=2.395..2.395 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.236 2.378 ↑ 10.8 44 1

HashAggregate (cost=764.76..789.59 rows=473 width=100) (actual time=2.321..2.378 rows=44 loops=1)

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
11. 2.142 2.142 ↑ 2.7 246 1

Index Scan using boosts_pkey on campaigns b (cost=0.42..730.37 rows=655 width=36) (actual time=0.325..2.142 rows=246 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: 1307
12. 0.268 0.782 ↑ 1.0 1,553 1

Hash (cost=67.53..67.53 rows=1,553 width=43) (actual time=0.782..0.782 rows=1,553 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 118kB
13. 0.514 0.514 ↑ 1.0 1,553 1

Seq Scan on campaign_names_groups names (cost=0.00..67.53 rows=1,553 width=43) (actual time=0.015..0.514 rows=1,553 loops=1)

14. 0.010 10,245.831 ↑ 25.8 26 1

Hash (cost=62,998.11..62,998.11 rows=670 width=20) (actual time=10,245.831..10,245.831 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.009 10,245.821 ↑ 25.8 26 1

Subquery Scan on imb (cost=62,984.71..62,998.11 rows=670 width=20) (actual time=10,245.808..10,245.821 rows=26 loops=1)

16. 532.062 10,245.812 ↑ 25.8 26 1

HashAggregate (cost=62,984.71..62,991.41 rows=670 width=20) (actual time=10,245.807..10,245.812 rows=26 loops=1)

  • Group Key: widgets.imb_id
17. 8,218.466 9,713.750 ↓ 18.9 1,477,103 1

Bitmap Heap Scan on widgets (cost=40,452.12..62,399.01 rows=78,094 width=12) (actual time=1,516.141..9,713.750 rows=1,477,103 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: 24203524
  • Heap Blocks: exact=55400 lossy=446896
18. 77.626 1,495.284 ↓ 0.0 0 1

BitmapAnd (cost=40,452.12..40,452.12 rows=19,981 width=0) (actual time=1,495.284..1,495.284 rows=0 loops=1)

19. 756.299 756.299 ↓ 4.0 3,686,960 1

Bitmap Index Scan on widgets_source_id_idx (cost=0.00..9,707.48 rows=922,325 width=0) (actual time=756.299..756.299 rows=3,686,960 loops=1)

  • Index Cond: (source_id = 30)
20. 661.359 661.359 ↑ 1.1 1,647,809 1

Bitmap Index Scan on widgets_pkey (cost=0.00..30,705.35 rows=1,747,068 width=0) (actual time=661.359..661.359 rows=1,647,809 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
Planning time : 1.003 ms
Execution time : 10,300.482 ms