explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RqOF : Optimization for: Optimization for: plan #jjT8; plan #R0k

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.109 3,922.626 ↑ 35.6 72 1

Sort (cost=233,306.60..233,313.02 rows=2,566 width=220) (actual time=3,922.616..3,922.626 rows=72 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 62kB
2. 0.342 3,922.517 ↑ 35.6 72 1

Hash Join (cost=232,995.81..233,161.30 rows=2,566 width=220) (actual time=3,922.157..3,922.517 rows=72 loops=1)

  • Hash Cond: (widgets.imb_id = b.imb_id)
3. 632.382 3,791.161 ↑ 4.7 176 1

HashAggregate (cost=226,327.23..226,335.44 rows=821 width=20) (actual time=3,791.104..3,791.161 rows=176 loops=1)

  • Group Key: widgets.imb_id
4. 3,158.779 3,158.779 ↓ 1.3 1,772,282 1

Index Scan using widgets_date_idx on widgets (cost=0.44..215,890.91 rows=1,391,509 width=12) (actual time=0.986..3,158.779 rows=1,772,282 loops=1)

  • Index Cond: ((date >= '2019-04-28'::date) AND (date <= '2019-05-10'::date))
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 9099
5. 0.110 131.014 ↑ 7.8 80 1

Hash (cost=6,660.76..6,660.76 rows=625 width=163) (actual time=131.014..131.014 rows=80 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
6. 0.100 130.904 ↑ 7.8 80 1

Hash Left Join (cost=4,759.47..6,660.76 rows=625 width=163) (actual time=61.796..130.904 rows=80 loops=1)

  • Hash Cond: (b.imb_id = names.imb_id)
7. 0.260 129.245 ↑ 7.8 80 1

Hash Right Join (cost=4,637.63..6,530.33 rows=625 width=124) (actual time=60.224..129.245 rows=80 loops=1)

  • Hash Cond: (d1.imb_id = b.imb_id)
8. 0.469 122.884 ↓ 4.4 580 1

Nested Loop (cost=3,567.46..5,448.76 rows=132 width=28) (actual time=51.682..122.884 rows=580 loops=1)

9. 32.821 51.999 ↑ 1.0 978 1

HashAggregate (cost=3,567.05..3,576.83 rows=978 width=8) (actual time=51.316..51.999 rows=978 loops=1)

  • Group Key: campaigns.imb_id
10. 19.178 19.178 ↑ 1.0 97,803 1

Seq Scan on campaigns (cost=0.00..3,078.03 rows=97,803 width=8) (actual time=0.011..19.178 rows=97,803 loops=1)

11. 70.416 70.416 ↑ 1.0 1 978

Index Scan using boosts_pkey on campaigns d1 (cost=0.42..1.89 rows=1 width=32) (actual time=0.071..0.072 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
12. 0.043 6.101 ↑ 7.8 80 1

Hash (cost=1,062.36..1,062.36 rows=625 width=100) (actual time=6.101..6.101 rows=80 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
13. 0.875 6.058 ↑ 7.8 80 1

HashAggregate (cost=1,023.29..1,056.11 rows=625 width=100) (actual time=5.886..6.058 rows=80 loops=1)

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
  • Rows Removed by Filter: 4
14. 5.183 5.183 ↑ 1.6 640 1

Index Scan using boosts_pkey on campaigns b (cost=0.42..971.21 rows=992 width=36) (actual time=0.398..5.183 rows=640 loops=1)

  • Index Cond: ((date >= '2019-04-28'::date) AND (date <= '2019-05-10'::date))
  • Filter: ((ts_id IS NOT NULL) AND (cost > '0'::double precision))
  • Rows Removed by Filter: 2135
15. 0.925 1.559 ↑ 2.0 1,552 1

Hash (cost=83.04..83.04 rows=3,104 width=43) (actual time=1.559..1.559 rows=1,552 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 134kB
16. 0.634 0.634 ↑ 2.0 1,552 1

Seq Scan on campaign_names_groups names (cost=0.00..83.04 rows=3,104 width=43) (actual time=0.012..0.634 rows=1,552 loops=1)

Planning time : 6.442 ms
Execution time : 3,922.871 ms