explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qh9Z : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #LaHU; plan #IaEQ; plan #ewLA; plan #ZqOR; plan #SdSy; plan #7fcf

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.030 34,651.178 ↑ 105.7 15 1

Sort (cost=232,149.14..232,153.10 rows=1,585 width=220) (actual time=34,651.175..34,651.178 rows=15 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 32kB
2. 0.099 34,651.148 ↑ 105.7 15 1

Hash Join (cost=231,958.95..232,064.90 rows=1,585 width=220) (actual time=34,651.086..34,651.148 rows=15 loops=1)

  • Hash Cond: (widgets.imb_id = b.imb_id)
3. 73.138 34,603.088 ↑ 9.3 72 1

HashAggregate (cost=225,658.12..225,664.82 rows=670 width=20) (actual time=34,603.070..34,603.088 rows=72 loops=1)

  • Group Key: widgets.imb_id
4. 34,529.950 34,529.950 ↑ 8.2 170,547 1

Index Scan using widgets_date_idx on widgets (cost=0.44..215,142.24 rows=1,402,117 width=12) (actual time=11.161..34,529.950 rows=170,547 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
6. 0.047 47.920 ↑ 10.8 44 1

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

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

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

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

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

9. 25.340 39.086 ↓ 1.0 978 1

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

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

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

11. 4.890 4.890 ↑ 1.0 1 978

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.220 1.440 ↑ 10.8 44 1

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

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

Index Scan using boosts_pkey on campaigns b (cost=0.42..730.37 rows=655 width=36) (actual time=0.154..1.220 rows=246 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: 1307
15. 0.342 2.302 ↑ 1.0 1,553 1

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

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

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

Planning time : 0.971 ms
Execution time : 34,651.449 ms