explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xXsV : 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 #LaHU; plan #IaEQ; plan #ewLA; plan #ZqOR; plan #SdSy; plan #NX5; plan #AAo9; plan #IMEN; plan #VYE1; plan #l3H2; plan #VGlE; plan #i7GS; plan #cExc; plan #WKwM; plan #C92a

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.327 11,669.225 ↑ 34.2 15 1

Sort (cost=1,055,133.48..1,055,134.76 rows=513 width=220) (actual time=11,668.980..11,669.225 rows=15 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 32kB
2. 0.366 11,668.898 ↑ 34.2 15 1

Hash Join (cost=1,053,631.15..1,055,110.39 rows=513 width=220) (actual time=11,417.339..11,668.898 rows=15 loops=1)

  • Hash Cond: (b.imb_id = imb.imb_id)
3. 0.120 465.827 ↑ 10.7 44 1

Hash Left Join (cost=4,070.22..5,518.66 rows=471 width=163) (actual time=214.568..465.827 rows=44 loops=1)

  • Hash Cond: (b.imb_id = names.imb_id)
4. 0.384 460.417 ↑ 10.7 44 1

Hash Right Join (cost=4,021.30..5,463.26 rows=471 width=124) (actual time=209.252..460.417 rows=44 loops=1)

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

Nested Loop (cost=3,261.34..4,694.48 rows=133 width=28) (actual time=126.592..377.485 rows=580 loops=1)

6. 31.209 127.326 ↓ 1.0 978 1

HashAggregate (cost=3,261.05..3,270.39 rows=935 width=8) (actual time=126.566..127.326 rows=978 loops=1)

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

Seq Scan on campaigns (cost=0.00..2,772.03 rows=97,803 width=8) (actual time=0.010..96.117 rows=97,803 loops=1)

8. 248.412 248.412 ↑ 1.0 1 978

Index Scan using boosts_pkey on campaigns d1 (cost=0.29..1.50 rows=1 width=32) (actual time=0.254..0.254 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.020 82.548 ↑ 10.7 44 1

Hash (cost=754.08..754.08 rows=471 width=100) (actual time=82.548..82.548 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.608 82.528 ↑ 10.7 44 1

HashAggregate (cost=724.64..749.37 rows=471 width=100) (actual time=82.479..82.528 rows=44 loops=1)

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

Index Scan using boosts_pkey on campaigns b (cost=0.29..690.36 rows=653 width=36) (actual time=31.736..81.920 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.318 5.290 ↑ 1.0 1,552 1

Hash (cost=29.52..29.52 rows=1,552 width=43) (actual time=5.290..5.290 rows=1,552 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 118kB
13. 4.972 4.972 ↑ 1.0 1,552 1

Seq Scan on campaign_names_groups names (cost=0.00..29.52 rows=1,552 width=43) (actual time=0.611..4.972 rows=1,552 loops=1)

14. 0.042 11,202.705 ↑ 3.1 71 1

Hash (cost=1,049,558.20..1,049,558.20 rows=218 width=20) (actual time=11,202.705..11,202.705 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
15. 0.010 11,202.663 ↑ 3.1 71 1

Subquery Scan on imb (cost=1,049,553.84..1,049,558.20 rows=218 width=20) (actual time=11,202.637..11,202.663 rows=71 loops=1)

16. 80.796 11,202.653 ↑ 3.1 71 1

HashAggregate (cost=1,049,553.84..1,049,556.02 rows=218 width=20) (actual time=11,202.636..11,202.653 rows=71 loops=1)

  • Group Key: widgets.imb_id
17. 10,427.415 11,121.857 ↑ 8.2 165,207 1

Bitmap Heap Scan on widgets (cost=21,588.49..1,039,391.94 rows=1,354,921 width=12) (actual time=726.550..11,121.857 rows=165,207 loops=1)

  • Recheck Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Rows Removed by Index Recheck: 6415561
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 1449302
  • Heap Blocks: exact=64263 lossy=407075
18. 694.442 694.442 ↑ 1.0 1,614,509 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..21,249.76 rows=1,639,282 width=0) (actual time=694.442..694.442 rows=1,614,509 loops=1)

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