explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DsHq : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #jjT8; plan #R0k; plan #RqOF; plan #yjc; plan #fWeZ; plan #X2Wd

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.046 45,068.972 ↑ 105.7 15 1

Sort (cost=232,051.97..232,055.93 rows=1,585 width=220) (actual time=45,068.971..45,068.972 rows=15 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 32kB
2. 0.092 45,068.926 ↑ 105.7 15 1

Hash Join (cost=231,861.77..231,967.72 rows=1,585 width=220) (actual time=45,068.863..45,068.926 rows=15 loops=1)

  • Hash Cond: (widgets.imb_id = b.imb_id)
3. 79.137 44,857.801 ↑ 9.4 71 1

HashAggregate (cost=225,560.94..225,567.64 rows=670 width=20) (actual time=44,857.781..44,857.801 rows=71 loops=1)

  • Group Key: widgets.imb_id
4. 44,778.664 44,778.664 ↑ 8.3 168,531 1

Index Scan using widgets_date_idx on widgets (cost=0.44..215,050.58 rows=1,401,382 width=12) (actual time=0.919..44,778.664 rows=168,531 loops=1)

  • Index Cond: ((date >= '2019-06-06'::date) AND (date <= '2019-06-11'::date))
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 1473269
5. 0.093 211.033 ↑ 10.8 44 1

Hash (cost=6,294.92..6,294.92 rows=473 width=163) (actual time=211.033..211.033 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
6. 0.098 210.940 ↑ 10.8 44 1

Hash Left Join (cost=4,454.64..6,294.92 rows=473 width=163) (actual time=122.427..210.940 rows=44 loops=1)

  • Hash Cond: (b.imb_id = names.imb_id)
7. 0.361 202.709 ↑ 10.8 44 1

Hash Right Join (cost=4,367.69..6,201.47 rows=473 width=124) (actual time=114.266..202.709 rows=44 loops=1)

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

Nested Loop (cost=3,567.46..5,392.50 rows=132 width=28) (actual time=102.162..199.278 rows=580 loops=1)

9. 67.148 102.577 ↓ 1.0 978 1

HashAggregate (cost=3,567.05..3,576.45 rows=940 width=8) (actual time=101.812..102.577 rows=978 loops=1)

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

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

11. 95.844 95.844 ↑ 1.0 1 978

Index Scan using boosts_pkey on campaigns d1 (cost=0.42..1.91 rows=1 width=32) (actual time=0.098..0.098 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.030 3.070 ↑ 10.8 44 1

Hash (cost=794.32..794.32 rows=473 width=100) (actual time=3.070..3.070 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.264 3.040 ↑ 10.8 44 1

HashAggregate (cost=764.76..789.59 rows=473 width=100) (actual time=2.967..3.040 rows=44 loops=1)

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
14. 2.776 2.776 ↑ 2.7 243 1

Index Scan using boosts_pkey on campaigns b (cost=0.42..730.37 rows=655 width=36) (actual time=0.444..2.776 rows=243 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: 1310
15. 0.777 8.133 ↑ 1.0 1,553 1

Hash (cost=67.53..67.53 rows=1,553 width=43) (actual time=8.133..8.133 rows=1,553 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 118kB
16. 7.356 7.356 ↑ 1.0 1,553 1

Seq Scan on campaign_names_groups names (cost=0.00..67.53 rows=1,553 width=43) (actual time=1.108..7.356 rows=1,553 loops=1)

Planning time : 5.787 ms
Execution time : 45,069.295 ms