Skip to content

graphqlbackend: Order repos by name, not uri

Administrator requested to merge repo-listing-slow into master

Created by: keegancsmith

URI is an unused field in our repos table (which mirrors name). It is the old column name for what became our name column. As such ordering by uri is slow and requires a sequential scan. However, ordering by name is fast since we have an index on it. This manifested itself on Sourcegraph as repo listing taking 10s.

Before:

  # EXPLAIN ANALYZE SELECT id FROM repo WHERE enabled ORDER BY uri LIMIT 11;
   Limit  (cost=241091.77..241091.80 rows=11 width=39) (actual time=9155.239..9155.240 rows=11 loops=1)
     ->  Sort  (cost=241091.77..247704.95 rows=2645272 width=39) (actual time=9155.237..9155.238 rows=11 loops=1)
           Sort Key: uri
           Sort Method: top-N heapsort  Memory: 25kB
           ->  Seq Scan on repo  (cost=0.00..182109.72 rows=2645272 width=39) (actual time=0.011..4658.238 rows=2797130 loops=1)
                 Filter: enabled
                 Rows Removed by Filter: 31
   Planning time: 0.125 ms
   Execution time: 9155.274 ms

After:

  # EXPLAIN ANALYZE SELECT id FROM repo WHERE enabled ORDER BY name LIMIT 11;
   Limit  (cost=0.56..3.84 rows=11 width=39) (actual time=0.058..0.195 rows=11 loops=1)
     ->  Index Scan using repo_name_unique on repo  (cost=0.56..790025.73 rows=2645272 width=39) (actual time=0.058..0.194 rows=11 loops=1)
           Filter: enabled
   Planning time: 7.800 ms
   Execution time: 0.217 ms

Fixes https://github.com/sourcegraph/sourcegraph/issues/1381

Merge request reports