explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oZWp : Optimization for: Optimization for: plan #X7OV; plan #4qTb

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.051 9,090.139 ↑ 21.6 16 1

Sort (cost=1,318,700.30..1,318,701.17 rows=346 width=220) (actual time=9,090.137..9,090.139 rows=16 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 33kB
2. 0.108 9,090.088 ↑ 21.6 16 1

Hash Join (cost=1,317,897.46..1,318,685.71 rows=346 width=220) (actual time=9,088.316..9,090.088 rows=16 loops=1)

  • Hash Cond: (b.imb_id = imb.imb_id)
3. 0.051 18.185 ↑ 7.0 45 1

Hash Left Join (cost=2,523.19..3,290.69 rows=317 width=163) (actual time=16.472..18.185 rows=45 loops=1)

  • Hash Cond: (b.imb_id = names.imb_id)
4. 0.083 17.195 ↑ 7.0 45 1

Hash Right Join (cost=2,461.18..3,224.33 rows=317 width=124) (actual time=15.514..17.195 rows=45 loops=1)

  • Hash Cond: (d1.imb_id = b.imb_id)
5. 0.000 13.899 ↓ 2.4 303 1

Nested Loop (cost=810.20..1,566.93 rows=126 width=28) (actual time=12.275..13.899 rows=303 loops=1)

6. 6.489 12.372 ↑ 1.0 382 1

HashAggregate (cost=809.91..813.73 rows=382 width=8) (actual time=12.256..12.372 rows=382 loops=1)

  • Group Key: campaigns.imb_id
7. 5.883 5.883 ↑ 1.0 25,521 1

Index Only Scan using boosts_pkey on campaigns (cost=0.29..682.30 rows=25,521 width=8) (actual time=0.477..5.883 rows=25,521 loops=1)

  • Heap Fetches: 3171
8. 1.528 1.528 ↑ 1.0 1 382

Index Scan using boosts_pkey on campaigns d1 (cost=0.29..1.95 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=382)

  • Index Cond: ((date = (max(campaigns.date))) AND (imb_id = campaigns.imb_id))
  • Filter: (bid IS NOT NULL)
  • Rows Removed by Filter: 0
9. 0.024 3.213 ↑ 7.0 45 1

Hash (cost=1,647.03..1,647.03 rows=317 width=100) (actual time=3.213..3.213 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.486 3.189 ↑ 7.0 45 1

HashAggregate (cost=1,627.21..1,643.86 rows=317 width=100) (actual time=3.143..3.189 rows=45 loops=1)

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
11. 2.703 2.703 ↑ 1.5 459 1

Index Scan using boosts_pkey on campaigns b (cost=0.29..1,592.14 rows=668 width=36) (actual time=0.530..2.703 rows=459 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Filter: ((ts_id IS NOT NULL) AND (cost > '0'::double precision))
  • Rows Removed by Filter: 2602
12. 0.410 0.939 ↑ 1.0 1,556 1

Hash (cost=42.56..42.56 rows=1,556 width=43) (actual time=0.939..0.939 rows=1,556 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
13. 0.529 0.529 ↑ 1.0 1,556 1

Seq Scan on campaign_names_groups names (cost=0.00..42.56 rows=1,556 width=43) (actual time=0.014..0.529 rows=1,556 loops=1)

14. 0.041 9,071.795 ↑ 2.1 105 1

Hash (cost=1,315,371.54..1,315,371.54 rows=218 width=20) (actual time=9,071.795..9,071.795 rows=105 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.020 9,071.754 ↑ 2.1 105 1

Subquery Scan on imb (cost=1,315,367.18..1,315,371.54 rows=218 width=20) (actual time=9,071.714..9,071.754 rows=105 loops=1)

16. 128.010 9,071.734 ↑ 2.1 105 1

HashAggregate (cost=1,315,367.18..1,315,369.36 rows=218 width=20) (actual time=9,071.713..9,071.734 rows=105 loops=1)

  • Group Key: widgets.imb_id
17. 7,945.341 8,943.724 ↑ 6.1 365,725 1

Bitmap Heap Scan on widgets (cost=35,790.66..1,298,612.56 rows=2,233,949 width=12) (actual time=1,058.713..8,943.724 rows=365,725 loops=1)

  • Recheck Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Rows Removed by Index Recheck: 5454412
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 2493530
  • Heap Blocks: exact=93166 lossy=407072
18. 998.383 998.383 ↓ 1.1 2,917,165 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..35,232.18 rows=2,702,794 width=0) (actual time=998.382..998.383 rows=2,917,165 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
Planning time : 1.093 ms
Execution time : 9,090.411 ms