Skip to content

db: Cleanup repo store

Administrator requested to merge es/repo-store-cleanup-perf into main

Created by: eseliger

Some little quality of life improvements I found while investigating some things around the repo table.

  • A tad nicer SQL queries for consumption in google query insights
  • Removed unused param
  • A missing index for getting a repo by clone URL

Before

                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=107892.05..107892.06 rows=1 width=54) (actual time=195.665..197.576 rows=1 loops=1)
   ->  Sort  (cost=107892.05..107892.06 rows=1 width=54) (actual time=195.663..197.574 rows=1 loops=1)
         Sort Key: r.updated_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Nested Loop  (cost=1000.43..107892.04 rows=1 width=54) (actual time=10.582..197.542 rows=49 loops=1)
               ->  Gather  (cost=1000.00..107889.39 rows=1 width=4) (actual time=10.561..197.306 rows=49 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Parallel Seq Scan on external_service_repos esr  (cost=0.00..106889.29 rows=1 width=4) (actual time=5.638..190.964 rows=16 loops=3)
                           Filter: (clone_url = 'https://github.com/sourcegraph/sourcegraph'::text)
                           Rows Removed by Filter: 1454816
               ->  Index Scan using repo_pkey on repo r  (cost=0.43..2.65 rows=1 width=58) (actual time=0.004..0.004 rows=1 loops=49)
                     Index Cond: (id = esr.repo_id)
 Planning Time: 0.370 ms
 Execution Time: 197.618 ms
(15 rows)

After

                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5.44..5.44 rows=1 width=54) (actual time=0.234..0.235 rows=1 loops=1)
   ->  Sort  (cost=5.44..5.44 rows=1 width=54) (actual time=0.234..0.234 rows=1 loops=1)
         Sort Key: r.updated_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Nested Loop  (cost=0.99..5.43 rows=1 width=54) (actual time=0.068..0.218 rows=49 loops=1)
               ->  Index Scan using erik_remove_test on external_service_repos esr  (cost=0.56..2.78 rows=1 width=4) (actual time=0.058..0.109 rows=49 loops=1)
                     Index Cond: (clone_url = 'https://github.com/sourcegraph/sourcegraph'::text)
               ->  Index Scan using repo_pkey on repo r  (cost=0.43..2.65 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=49)
                     Index Cond: (id = esr.repo_id)
 Planning Time: 0.552 ms
 Execution Time: 0.260 ms
(11 rows)

Merge request reports