explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.099 190,777.085 ↑ 34.5 15 1

Sort (cost=1,361,683.08..1,361,684.37 rows=518 width=220) (actual time=190,777.077..190,777.085 rows=15 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 32kB
2. 0.369 190,776.986 ↑ 34.5 15 1

Hash Join (cost=1,357,245.52..1,361,659.72 rows=518 width=220) (actual time=190,523.104..190,776.986 rows=15 loops=1)

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

Hash Left Join (cost=5,289.98..9,673.22 rows=471 width=163) (actual time=206.009..459.568 rows=44 loops=1)

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

Hash Right Join (cost=5,241.06..9,617.82 rows=471 width=124) (actual time=204.153..457.605 rows=44 loops=1)

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

Nested Loop (cost=3,261.34..7,629.28 rows=133 width=28) (actual time=75.990..331.290 rows=580 loops=1)

6. 28.702 77.169 ↓ 1.0 978 1

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

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

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

8. 252.324 252.324 ↑ 1.0 1 978

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

Hash (cost=1,973.84..1,973.84 rows=471 width=100) (actual time=125.367..125.367 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.735 125.331 ↑ 10.7 44 1

HashAggregate (cost=1,944.40..1,969.13 rows=471 width=100) (actual time=125.283..125.331 rows=44 loops=1)

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

Bitmap Heap Scan on campaigns b (cost=37.63..1,910.12 rows=653 width=36) (actual time=17.926..124.596 rows=243 loops=1)

  • Recheck 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
  • Heap Blocks: exact=270
12. 1.474 1.474 ↑ 1.1 1,553 1

Bitmap Index Scan on boosts_pkey (cost=0.00..37.46 rows=1,717 width=0) (actual time=1.474..1.474 rows=1,553 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
13. 0.364 1.830 ↑ 1.0 1,552 1

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

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

15. 0.032 190,317.049 ↑ 3.1 71 1

Hash (cost=1,351,952.79..1,351,952.79 rows=220 width=20) (actual time=190,317.049..190,317.049 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
16. 0.012 190,317.017 ↑ 3.1 71 1

Subquery Scan on imb (cost=1,351,948.39..1,351,952.79 rows=220 width=20) (actual time=190,316.990..190,317.017 rows=71 loops=1)

17. 130.295 190,317.005 ↑ 3.1 71 1

HashAggregate (cost=1,351,948.39..1,351,950.59 rows=220 width=20) (actual time=190,316.989..190,317.005 rows=71 loops=1)

  • Group Key: widgets.imb_id
18. 189,447.061 190,186.710 ↑ 7.9 165,207 1

Bitmap Heap Scan on widgets (cost=33,714.18..1,342,126.29 rows=1,309,613 width=12) (actual time=754.201..190,186.710 rows=165,207 loops=1)

  • Recheck Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Rows Removed by Index Recheck: 5564034
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 1449302
  • Heap Blocks: exact=47520 lossy=423818
19. 739.649 739.649 ↓ 1.0 1,614,509 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..33,386.78 rows=1,592,234 width=0) (actual time=739.649..739.649 rows=1,614,509 loops=1)

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