explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4qTb : Optimization for: plan #X7OV

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.117 5,060.247 ↑ 21.6 16 1

Sort (cost=1,318,268.71..1,318,269.57 rows=346 width=220) (actual time=5,060.246..5,060.247 rows=16 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 33kB
2. 0.096 5,060.130 ↑ 21.6 16 1

Hash Join (cost=1,317,466.96..1,318,254.12 rows=346 width=220) (actual time=5,058.498..5,060.130 rows=16 loops=1)

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

Hash Left Join (cost=2,519.89..3,286.29 rows=317 width=163) (actual time=14.861..16.437 rows=45 loops=1)

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

Hash Right Join (cost=2,457.88..3,219.93 rows=317 width=124) (actual time=14.266..15.817 rows=45 loops=1)

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

Nested Loop (cost=808.00..1,563.63 rows=126 width=28) (actual time=11.076..12.564 rows=303 loops=1)

6. 5.849 11.144 ↑ 1.0 382 1

HashAggregate (cost=807.71..811.53 rows=382 width=8) (actual time=11.053..11.144 rows=382 loops=1)

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

Index Only Scan using boosts_pkey on campaigns (cost=0.29..680.10 rows=25,521 width=8) (actual time=0.043..5.295 rows=25,521 loops=1)

  • Heap Fetches: 0
8. 1.146 1.146 ↑ 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.003 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.026 3.162 ↑ 7.0 45 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.375 3.136 ↑ 7.0 45 1

HashAggregate (cost=1,626.11..1,642.76 rows=317 width=100) (actual time=3.093..3.136 rows=45 loops=1)

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

Index Scan using boosts_pkey on campaigns b (cost=0.29..1,591.04 rows=668 width=36) (actual time=0.101..2.761 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.302 0.576 ↑ 1.0 1,556 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
13. 0.274 0.274 ↑ 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.011..0.274 rows=1,556 loops=1)

14. 0.037 5,043.597 ↑ 2.1 105 1

Hash (cost=1,314,944.34..1,314,944.34 rows=218 width=20) (actual time=5,043.597..5,043.597 rows=105 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.022 5,043.560 ↑ 2.1 105 1

Subquery Scan on imb (cost=1,314,939.98..1,314,944.34 rows=218 width=20) (actual time=5,043.519..5,043.560 rows=105 loops=1)

16. 113.199 5,043.538 ↑ 2.1 105 1

HashAggregate (cost=1,314,939.98..1,314,942.16 rows=218 width=20) (actual time=5,043.518..5,043.538 rows=105 loops=1)

  • Group Key: widgets.imb_id
17. 4,461.873 4,930.339 ↑ 6.1 365,725 1

Bitmap Heap Scan on widgets (cost=35,642.08..1,298,258.24 rows=2,224,232 width=12) (actual time=510.546..4,930.339 rows=365,725 loops=1)

  • Recheck Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Rows Removed by Index Recheck: 5505203
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 2493530
  • Heap Blocks: exact=92283 lossy=407066
18. 468.466 468.466 ↓ 1.1 2,859,255 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..35,086.02 rows=2,691,038 width=0) (actual time=468.466..468.466 rows=2,859,255 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
Planning time : 4.067 ms
Execution time : 5,061.026 ms