explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Zv8

Settings
# exclusive inclusive rows x rows loops node
1. 13.896 10,597.192 ↑ 1.0 1 1

HashAggregate (cost=312,805.99..312,806.00 rows=1 width=40) (actual time=10,597.192..10,597.192 rows=1 loops=1)

  • Group Key: stats.latest_scan_at, stats.total_scans
2.          

CTE website_scores

3. 44.465 10,389.166 ↑ 1.1 24,789 1

Nested Loop (cost=1.14..308,314.59 rows=27,639 width=10) (actual time=4.330..10,389.166 rows=24,789 loops=1)

4. 1,153.161 1,153.161 ↑ 1.1 24,842 1

Index Scan using cloudscans_hostnames_vendorid_enabled_without_id on cloudscans_hostnames h (cost=0.56..69,246.58 rows=27,844 width=8) (actual time=2.997..1,153.161 rows=24,842 loops=1)

  • Index Cond: ((enabled = true) AND (datastore_vendor_id = '6525938145689600'::bigint))
  • Filter: enabled
5. 9,191.540 9,191.540 ↑ 1.0 1 24,842

Index Scan using unique_cloudscan_per_hostname on cloudscans_scans s (cost=0.57..8.58 rows=1 width=18) (actual time=0.369..0.370 rows=1 loops=24,842)

  • Index Cond: (cloudscan_id = h.latest_cloudscan_id)
  • Filter: (NOT no_result)
  • Rows Removed by Filter: 0
6.          

CTE stats

7. 51.357 10,470.220 ↑ 1.0 1 1

Aggregate (cost=898.27..898.28 rows=1 width=56) (actual time=10,470.220..10,470.220 rows=1 loops=1)

8. 10,418.863 10,418.863 ↑ 1.1 24,789 1

CTE Scan on website_scores (cost=0.00..552.78 rows=27,639 width=10) (actual time=4.333..10,418.863 rows=24,789 loops=1)

9.          

CTE raw_weights

10. 54.633 60.317 ↑ 1.1 24,789 1

Nested Loop (cost=0.00..1,451.07 rows=27,639 width=10) (actual time=0.058..60.317 rows=24,789 loops=1)

11. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on stats stats_1 (cost=0.00..0.02 rows=1 width=40) (actual time=0.001..0.003 rows=1 loops=1)

12. 5.681 5.681 ↑ 1.1 24,789 1

CTE Scan on website_scores s_1 (cost=0.00..552.78 rows=27,639 width=2) (actual time=0.001..5.681 rows=24,789 loops=1)

13.          

CTE final_weights

14. 27.567 97.981 ↑ 1.1 24,789 1

WindowAgg (cost=0.00..967.36 rows=27,639 width=10) (actual time=84.956..97.981 rows=24,789 loops=1)

15. 70.414 70.414 ↑ 1.1 24,789 1

CTE Scan on raw_weights (cost=0.00..552.78 rows=27,639 width=10) (actual time=0.060..70.414 rows=24,789 loops=1)

16. 4.597 10,583.296 ↑ 1.1 24,789 1

Nested Loop (cost=0.00..829.19 rows=27,639 width=26) (actual time=10,555.186..10,583.296 rows=24,789 loops=1)

17. 10,470.225 10,470.225 ↑ 1.0 1 1

CTE Scan on stats (cost=0.00..0.02 rows=1 width=16) (actual time=10,470.224..10,470.225 rows=1 loops=1)

18. 108.474 108.474 ↑ 1.1 24,789 1

CTE Scan on final_weights w (cost=0.00..552.78 rows=27,639 width=10) (actual time=84.959..108.474 rows=24,789 loops=1)

Planning time : 2.326 ms
Execution time : 10,598.842 ms