explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.027 154,254.908 ↑ 104.9 15 1

Sort (cost=1,372,953.67..1,372,957.60 rows=1,573 width=220) (actual time=154,254.907..154,254.908 rows=15 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 32kB
2. 0.461 154,254.881 ↑ 104.9 15 1

Hash Join (cost=1,372,764.75..1,372,870.15 rows=1,573 width=220) (actual time=154,254.814..154,254.881 rows=15 loops=1)

  • Hash Cond: (widgets.imb_id = b.imb_id)
3. 119.332 154,007.234 ↑ 9.4 71 1

HashAggregate (cost=1,363,085.64..1,363,092.32 rows=668 width=20) (actual time=154,007.220..154,007.234 rows=71 loops=1)

  • Group Key: widgets.imb_id
4. 153,387.155 153,887.902 ↑ 8.4 165,207 1

Bitmap Heap Scan on widgets (cost=44,633.32..1,352,646.69 rows=1,391,861 width=12) (actual time=523.152..153,887.902 rows=165,207 loops=1)

  • Recheck Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Rows Removed by Index Recheck: 5672604
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 1449302
  • Heap Blocks: exact=46266 lossy=425072
5. 500.747 500.747 ↑ 1.1 1,614,509 1

Bitmap Index Scan on widgets_pkey (cost=0.00..44,285.36 rows=1,740,479 width=0) (actual time=500.747..500.747 rows=1,614,509 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
6. 0.103 247.186 ↑ 10.7 44 1

Hash (cost=9,673.22..9,673.22 rows=471 width=163) (actual time=247.186..247.186 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
7. 0.090 247.083 ↑ 10.7 44 1

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

  • Hash Cond: (b.imb_id = names.imb_id)
8. 0.206 245.484 ↑ 10.7 44 1

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

  • Hash Cond: (d1.imb_id = b.imb_id)
9. 1.016 150.481 ↓ 4.4 580 1

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

10. 30.548 80.027 ↓ 1.0 978 1

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

  • Group Key: campaigns.imb_id
11. 49.479 49.479 ↑ 1.0 97,803 1

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

12. 69.438 69.438 ↑ 1.0 1 978

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
14. 0.497 94.775 ↑ 10.7 44 1

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

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
15. 92.449 94.278 ↑ 2.7 243 1

Bitmap Heap Scan on campaigns b (cost=37.63..1,910.12 rows=653 width=36) (actual time=16.663..94.278 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
16. 1.829 1.829 ↑ 1.1 1,553 1

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

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
17. 0.381 1.509 ↑ 1.0 1,552 1

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

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

Planning time : 3.764 ms
Execution time : 154,255.641 ms