7
Github fix performance regression in all releases-views by syphar · Pull Request...
source link: https://github.com/rust-lang/docs.rs/pull/1237
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
All pages that show data from web::releases::get_releases are insanely slow (>1s).
This PR fixes this.
Local tests and explain looks good now. I could only test with faked data locally, but likely the behaviour will be the same on prod.
The behaviour is the same for the views by release-time, for the views by github-stars we don't show releases any more that are on non-github repos.
I also restructured the related tests a bit, I'm happy to revert if you don't like it. In my mind it fits better when tests only test a single thing with a visible input-data, execution, assertion structure.
old query plan, releases by github stars
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=20504.68..20504.69 rows=1 width=557) (actual time=4157.308..4157.910 rows=25 loops=1) │
│ -> Sort (cost=20504.68..20504.69 rows=1 width=557) (actual time=4157.288..4157.596 rows=25 loops=1) │
│ Sort Key: github_repos.stars DESC NULLS LAST │
│ Sort Method: top-N heapsort Memory: 27kB │
│ -> Nested Loop Left Join (cost=3099.40..20504.67 rows=1 width=557) (actual time=866.612..3749.925 rows=51776 loops=1) │
│ Join Filter: ((releases.github_repo)::text = (github_repos.id)::text) │
│ Rows Removed by Join Filter: 51761 │
│ -> Gather (cost=3099.40..20503.65 rows=1 width=582) (actual time=866.550..1314.885 rows=51776 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ -> Hash Join (cost=2099.40..19503.55 rows=1 width=582) (actual time=869.435..2655.396 rows=17259 loops=3) │
│ Hash Cond: ((releases.crate_id = crates.id) AND (releases.id = crates.latest_version_id)) │
│ -> Parallel Seq Scan on releases (cost=0.00..16716.26 rows=131026 width=579) (actual time=0.095..814.734 rows=104821 loops=3) │
│ -> Hash (cost=1322.76..1322.76 rows=51776 width=19) (actual time=868.599..868.616 rows=51776 loops=3) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3198kB │
│ -> Seq Scan on crates (cost=0.00..1322.76 rows=51776 width=19) (actual time=0.040..429.363 rows=51776 loops=3) │
│ -> Seq Scan on github_repos (cost=0.00..1.01 rows=1 width=33) (actual time=0.008..0.016 rows=1 loops=51776) │
│ Planning Time: 1.205 ms │
│ Execution Time: 4158.608 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(19 rows)
new query plan, release by github stars
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=1.14..137.06 rows=25 width=557) (actual time=0.039..0.381 rows=25 loops=1) │
│ -> Nested Loop (cost=1.14..283513.21 rows=52146 width=557) (actual time=0.038..0.377 rows=25 loops=1) │
│ -> Nested Loop (cost=0.84..178928.89 rows=316801 width=550) (actual time=0.032..0.283 rows=85 loops=1) │
│ -> Index Scan using github_repos_stars_idx on github_repos (cost=0.42..10360.44 rows=316801 width=22) (actual time=0.014..0.028 rows=85 loops=1) │
│ Index Cond: (stars IS NOT NULL) │
│ -> Index Scan using releases_github_repo_idx on releases (cost=0.42..0.52 rows=1 width=564) (actual time=0.002..0.002 rows=1 loops=85) │
│ Index Cond: ((github_repo)::text = (github_repos.id)::text) │
│ -> Index Scan using crates_latest_version_idx on crates (cost=0.29..0.32 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=85) │
│ Index Cond: (latest_version_id = releases.id) │
│ Planning Time: 0.481 ms │
│ Execution Time: 0.419 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(11 rows)
old query plan, release by release-time
;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=20504.68..20504.69 rows=1 width=557) (actual time=3248.766..3249.370 rows=25 loops=1) │
│ -> Sort (cost=20504.68..20504.69 rows=1 width=557) (actual time=3248.752..3249.080 rows=25 loops=1) │
│ Sort Key: releases.release_time DESC NULLS LAST │
│ Sort Method: top-N heapsort Memory: 26kB │
│ -> Nested Loop Left Join (cost=3099.40..20504.67 rows=1 width=557) (actual time=652.129..2926.578 rows=51776 loops=1) │
│ Join Filter: ((releases.github_repo)::text = (github_repos.id)::text) │
│ Rows Removed by Join Filter: 51761 │
│ -> Gather (cost=3099.40..20503.65 rows=1 width=582) (actual time=652.087..995.741 rows=51776 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ -> Hash Join (cost=2099.40..19503.55 rows=1 width=582) (actual time=649.110..2065.535 rows=17259 loops=3) │
│ Hash Cond: ((releases.crate_id = crates.id) AND (releases.id = crates.latest_version_id)) │
│ -> Parallel Seq Scan on releases (cost=0.00..16716.26 rows=131026 width=579) (actual time=0.122..655.740 rows=104821 loops=3) │
│ -> Hash (cost=1322.76..1322.76 rows=51776 width=19) (actual time=648.415..648.438 rows=51776 loops=3) │
│ Buckets: 65536 Batches: 1 Memory Usage: 3198kB │
│ -> Seq Scan on crates (cost=0.00..1322.76 rows=51776 width=19) (actual time=0.030..322.152 rows=51776 loops=3) │
│ -> Seq Scan on github_repos (cost=0.00..1.01 rows=1 width=33) (actual time=0.006..0.013 rows=1 loops=51776) │
│ Planning Time: 2.768 ms │
│ Execution Time: 3249.898 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
new query plan, release by release-time
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=1.01..69.63 rows=25 width=557) (actual time=0.265..1.983 rows=25 loops=1) │
│ -> Nested Loop Left Join (cost=1.01..143130.67 rows=52146 width=557) (actual time=0.264..1.975 rows=25 loops=1) │
│ -> Nested Loop (cost=0.59..116157.63 rows=52146 width=571) (actual time=0.252..1.214 rows=25 loops=1) │
│ -> Index Scan using releases_release_time_idx on releases (cost=0.30..11573.32 rows=316801 width=564) (actual time=0.030..0.074 rows=85 loops=1) │
│ Index Cond: (release_time IS NOT NULL) │
│ -> Index Scan using crates_latest_version_idx on crates (cost=0.29..0.32 rows=1 width=15) (actual time=0.011..0.013 rows=0 loops=85) │
│ Index Cond: (latest_version_id = releases.id) │
│ -> Index Scan using github_repos_pkey on github_repos (cost=0.42..0.52 rows=1 width=22) (actual time=0.029..0.029 rows=1 loops=25) │
│ Index Cond: ((id)::text = (releases.github_repo)::text) │
│ Planning Time: 1.614 ms │
│ Execution Time: 2.046 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(11 rows)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK