explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.025 482.401 ↑ 32.8 15 1

Sort (cost=45,303.41..45,304.64 rows=492 width=220) (actual time=482.399..482.401 rows=15 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 32kB
2. 0.072 482.376 ↑ 32.8 15 1

Hash Left Join (cost=43,832.65..45,281.41 rows=492 width=220) (actual time=480.008..482.376 rows=15 loops=1)

  • Hash Cond: (b.imb_id = names.imb_id)
3. 0.023 481.855 ↑ 32.8 15 1

Hash Join (cost=43,786.21..45,217.14 rows=492 width=140) (actual time=479.529..481.855 rows=15 loops=1)

  • Hash Cond: (b.imb_id = imb.imb_id)
4. 0.073 25.667 ↑ 3.3 44 1

Hash Right Join (cost=3,383.39..4,796.81 rows=144 width=124) (actual time=23.352..25.667 rows=44 loops=1)

  • Hash Cond: (d1.imb_id = b.imb_id)
5. 0.116 25.102 ↓ 4.5 580 1

Nested Loop (cost=3,155.70..4,568.43 rows=128 width=28) (actual time=22.810..25.102 rows=580 loops=1)

6. 15.364 23.030 ↓ 1.1 978 1

HashAggregate (cost=3,155.41..3,164.71 rows=930 width=8) (actual time=22.797..23.030 rows=978 loops=1)

  • Group Key: campaigns.imb_id
7. 7.666 7.666 ↓ 1.0 94,443 1

Seq Scan on campaigns (cost=0.00..2,683.27 rows=94,427 width=8) (actual time=0.028..7.666 rows=94,443 loops=1)

8. 1.956 1.956 ↑ 1.0 1 978

Index Scan using boosts_pkey on campaigns d1 (cost=0.29..1.49 rows=1 width=32) (actual time=0.002..0.002 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.037 0.492 ↑ 3.3 44 1

Hash (cost=225.90..225.90 rows=144 width=100) (actual time=0.492..0.492 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.219 0.455 ↑ 3.3 44 1

HashAggregate (cost=216.90..224.46 rows=144 width=100) (actual time=0.403..0.455 rows=44 loops=1)

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
11. 0.236 0.236 ↓ 1.0 157 1

Index Scan using boosts_pkey on campaigns b (cost=0.29..208.71 rows=156 width=36) (actual time=0.082..0.236 rows=157 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: 226
12. 0.044 456.165 ↑ 31.1 22 1

Hash (cost=40,394.27..40,394.27 rows=684 width=20) (actual time=456.165..456.165 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.003 456.121 ↑ 31.1 22 1

Subquery Scan on imb (cost=40,380.59..40,394.27 rows=684 width=20) (actual time=456.114..456.121 rows=22 loops=1)

14. 1.872 456.118 ↑ 31.1 22 1

HashAggregate (cost=40,380.59..40,387.43 rows=684 width=20) (actual time=456.113..456.118 rows=22 loops=1)

  • Group Key: widgets.imb_id
15. 454.246 454.246 ↑ 21.6 11,206 1

Index Scan using widgets_date_idx on widgets (cost=0.44..38,568.84 rows=241,566 width=12) (actual time=80.665..454.246 rows=11,206 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 1383344
16. 0.242 0.449 ↑ 1.0 1,486 1

Hash (cost=27.86..27.86 rows=1,486 width=43) (actual time=0.449..0.449 rows=1,486 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 114kB
17. 0.207 0.207 ↑ 1.0 1,486 1

Seq Scan on campaign_names_groups names (cost=0.00..27.86 rows=1,486 width=43) (actual time=0.020..0.207 rows=1,486 loops=1)

Planning time : 1.468 ms
Execution time : 482.642 ms