Speed up ListIndexableRepos query for uncloned
Created by: eseliger
Since we introduced an invariant that gitserver_repos MUST have a record for each repo, we no longer need to do a left join here, and hence can optimize. Still not fast, but faster.
Before:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=1385996.09..1428720.77 rows=366186 width=45) (actual time=7499.200..7589.828 rows=4914 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=1384996.07..1385453.80 rows=183093 width=45) (actual time=7484.861..7485.005 rows=1638 loops=3)
Sort Key: repo.stars DESC NULLS LAST
Sort Method: quicksort Memory: 248kB
Worker 0: Sort Method: quicksort Memory: 257kB
Worker 1: Sort Method: quicksort Memory: 256kB
-> Parallel Hash Left Join (cost=480914.62..1368991.71 rows=183093 width=45) (actual time=2002.405..7483.617 rows=1638 loops=3)
Hash Cond: (repo.id = gr.repo_id)
Filter: ((gr.clone_status IS NULL) OR (gr.clone_status = 'not_cloned'::text))
Rows Removed by Filter: 994128
-> Parallel Seq Scan on repo (cost=104508.90..988268.68 rows=1644687 width=45) (actual time=312.475..4995.084 rows=995766 loops=3)
Filter: ((deleted_at IS NULL) AND (blocked IS NULL) AND (((stars >= 5) AND (NOT COALESCE(fork, false)) AND (NOT archived)) OR (lower((name)::text) ~ '^(src\.fedoraproject\.org|maven|npm|jdk)'::text) OR (hashed SubPlan 1)))
Rows Removed by Filter: 1258974
SubPlan 1
-> Append (cost=6113.13..103243.67 rows=506091 width=4) (actual time=45.900..249.734 rows=476519 loops=3)
-> Bitmap Heap Scan on external_service_repos (cost=6113.13..95632.31 rows=504001 width=4) (actual time=45.899..190.160 rows=474429 loops=3)
Recheck Cond: ((user_id IS NOT NULL) OR (org_id IS NOT NULL))
Heap Blocks: exact=39490
-> BitmapOr (cost=6113.13..6113.13 rows=504118 width=0) (actual time=38.602..38.603 rows=0 loops=3)
-> Bitmap Index Scan on external_service_user_repos_idx (cost=0.00..5836.75 rows=502372 width=0) (actual time=38.509..38.509 rows=472829 loops=3)
Index Cond: (user_id IS NOT NULL)
-> Bitmap Index Scan on external_service_repos_org_id_idx (cost=0.00..24.38 rows=1746 width=0) (actual time=0.092..0.092 rows=1600 loops=3)
Index Cond: (org_id IS NOT NULL)
-> Seq Scan on user_public_repos (cost=0.00..40.90 rows=2090 width=4) (actual time=0.041..0.412 rows=2090 loops=3)
-> Parallel Hash (cost=341028.10..341028.10 rows=2830210 width=11) (actual time=1477.973..1477.974 rows=2254740 loops=3)
Buckets: 8388608 Batches: 1 Memory Usage: 382976kB
-> Parallel Seq Scan on gitserver_repos gr (cost=0.00..341028.10 rows=2830210 width=11) (actual time=0.014..661.638 rows=2254740 loops=3)
Planning Time: 1.929 ms
Execution Time: 7592.740 ms
(31 rows)After
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=1164998.24..1207722.92 rows=366186 width=45) (actual time=5885.152..5900.653 rows=4913 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=1163998.21..1164455.94 rows=183093 width=45) (actual time=5879.589..5879.741 rows=1638 loops=3)
Sort Key: repo.stars DESC NULLS LAST
Sort Method: quicksort Memory: 260kB
Worker 0: Sort Method: quicksort Memory: 257kB
Worker 1: Sort Method: quicksort Memory: 244kB
-> Parallel Hash Join (cost=259916.77..1147993.85 rows=183093 width=45) (actual time=1061.089..5878.503 rows=1638 loops=3)
Hash Cond: (repo.id = gr.repo_id)
-> Parallel Seq Scan on repo (cost=104508.90..988268.68 rows=1644687 width=45) (actual time=286.767..4787.528 rows=995766 loops=3)
Filter: ((deleted_at IS NULL) AND (blocked IS NULL) AND (((stars >= 5) AND (NOT COALESCE(fork, false)) AND (NOT archived)) OR (lower((name)::text) ~ '^(src\.fedoraproject\.org|maven|npm|jdk)'::text) OR (hashed SubPlan 1)))
Rows Removed by Filter: 1258975
SubPlan 1
-> Append (cost=6113.13..103243.67 rows=506091 width=4) (actual time=35.820..253.044 rows=476519 loops=3)
-> Bitmap Heap Scan on external_service_repos (cost=6113.13..95632.31 rows=504001 width=4) (actual time=35.818..193.572 rows=474429 loops=3)
Recheck Cond: ((user_id IS NOT NULL) OR (org_id IS NOT NULL))
Heap Blocks: exact=39490
-> BitmapOr (cost=6113.13..6113.13 rows=504118 width=0) (actual time=28.704..28.705 rows=0 loops=3)
-> Bitmap Index Scan on external_service_user_repos_idx (cost=0.00..5836.75 rows=502372 width=0) (actual time=28.623..28.624 rows=472829 loops=3)
Index Cond: (user_id IS NOT NULL)
-> Bitmap Index Scan on external_service_repos_org_id_idx (cost=0.00..24.38 rows=1746 width=0) (actual time=0.078..0.079 rows=1600 loops=3)
Index Cond: (org_id IS NOT NULL)
-> Seq Scan on user_public_repos (cost=0.00..40.90 rows=2090 width=4) (actual time=0.032..0.428 rows=2090 loops=3)
-> Parallel Hash (cost=151488.03..151488.03 rows=313587 width=4) (actual time=601.020..601.025 rows=247423 loops=3)
Buckets: 1048576 Batches: 1 Memory Usage: 37248kB
-> Parallel Index Only Scan using gitserver_repos_not_cloned_status_idx on gitserver_repos gr (cost=0.42..151488.03 rows=313587 width=4) (actual time=0.163..507.651 rows=247423 loops=3)
Heap Fetches: 1081295
Planning Time: 1.356 ms
Execution Time: 5902.782 ms
(30 rows)Test plan
Validated with query plans this change is good.