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
