explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d8J2

Settings
# exclusive inclusive rows x rows loops node
1. 5,981.520 643,934.074 ↓ 18.8 2,711,520 1

Merge Left Join (cost=226,907,826.85..230,403,152.78 rows=144,443 width=244) (actual time=636,923.888..643,934.074 rows=2,711,520 loops=1)

  • Merge Cond: ((a.date_day = p.date_day) AND ((a.instance_type)::text = (p.instance_type)::text) AND ((a.os)::text = (p.os)::text))
  • Join Filter: ((a.region)::text = (a.region)::text)
2.          

CTE advisor

3. 6,319.508 6,319.508 ↓ 1.1 156,960 1

Seq Scan on spot_advisor (cost=0.00..62,149.54 rows=144,443 width=44) (actual time=0.036..6,319.508 rows=156,960 loops=1)

4.          

CTE prices

5. 1,950.501 626,466.874 ↑ 1,233.9 222,376 1

Finalize GroupAggregate (cost=35,842,343.80..77,840,063.92 rows=274,383,098 width=170) (actual time=540,014.181..626,466.874 rows=222,376 loops=1)

  • Group Key: (date_trunc('day'::text, p_1.check_time)), p_1.load_type, p_1.instance_type, r.region_2, p_1.os
6. 0.000 624,516.373 ↑ 342.7 667,128 1

Gather Merge (cost=35,842,343.80..66,521,761.12 rows=228,652,582 width=170) (actual time=540,009.145..624,516.373 rows=667,128 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 84,074.019 1,859,668.794 ↑ 514.1 222,376 3

Partial GroupAggregate (cost=35,841,343.78..40,128,579.69 rows=114,326,291 width=170) (actual time=539,225.378..619,889.598 rows=222,376 loops=3)

  • Group Key: (date_trunc('day'::text, p_1.check_time)), p_1.load_type, p_1.instance_type, r.region_2, p_1.os
8. 1,527,268.824 1,775,594.775 ↑ 5.5 20,781,567 3

Sort (cost=35,841,343.78..36,127,159.50 rows=114,326,291 width=79) (actual time=539,220.162..591,864.925 rows=20,781,567 loops=3)

  • Sort Key: (date_trunc('day'::text, p_1.check_time)), p_1.load_type, p_1.instance_type, r.region_2, p_1.os
  • Sort Method: external merge Disk: 1406880kB
  • Worker 0: Sort Method: external merge Disk: 1403808kB
  • Worker 1: Sort Method: external merge Disk: 1405032kB
9. 121,874.856 248,325.951 ↑ 5.5 20,781,567 3

Hash Left Join (cost=29.80..5,299,796.32 rows=114,326,291 width=79) (actual time=1.120..82,775.317 rows=20,781,567 loops=3)

  • Hash Cond: ((p_1.region)::text = (r.region)::text)
10. 126,450.984 126,450.984 ↑ 1.3 20,781,567 3

Parallel Seq Scan on spot_prices p_1 (cost=0.00..947,572.48 rows=25,983,248 width=57) (actual time=1.050..42,150.328 rows=20,781,567 loops=3)

11. 0.045 0.111 ↑ 46.3 19 3

Hash (cost=18.80..18.80 rows=880 width=64) (actual time=0.033..0.037 rows=19 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.066 0.066 ↑ 46.3 19 3

Seq Scan on ref_region r (cost=0.00..18.80 rows=880 width=64) (actual time=0.017..0.022 rows=19 loops=3)

13. 656.577 7,128.315 ↓ 1.1 156,960 1

Sort (cost=23,660.73..24,021.83 rows=144,443 width=112) (actual time=6,827.115..7,128.315 rows=156,960 loops=1)

  • Sort Key: a.date_day, a.instance_type, a.os
  • Sort Method: external merge Disk: 8832kB
14. 6,471.738 6,471.738 ↓ 1.1 156,960 1

CTE Scan on advisor a (cost=0.00..2,888.86 rows=144,443 width=112) (actual time=0.040..6,471.738 rows=156,960 loops=1)

15. 623.719 630,824.239 ↑ 101.5 2,703,331 1

Materialize (cost=148,981,952.67..150,353,868.16 rows=274,383,098 width=200) (actual time=630,090.608..630,824.239 rows=2,703,331 loops=1)

16. 1,707.511 630,200.520 ↑ 1,233.9 222,376 1

Sort (cost=148,981,952.67..149,667,910.42 rows=274,383,098 width=200) (actual time=630,090.600..630,200.520 rows=222,376 loops=1)

  • Sort Key: p.date_day, p.instance_type, p.os
  • Sort Method: external merge Disk: 15064kB
17. 628,493.009 628,493.009 ↑ 1,233.9 222,376 1

CTE Scan on prices p (cost=0.00..5,487,661.96 rows=274,383,098 width=200) (actual time=540,017.132..628,493.009 rows=222,376 loops=1)

Planning time : 0.293 ms
Execution time : 644,512.621 ms