Something went wrong on our end. Please try again.
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 msAfter:
# 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 msFixes https://github.com/sourcegraph/sourcegraph/issues/1381