Indexes for `created_at` can probably be removed
Various tables index the column created_at
so one can sort by this column. Because in pretty much all cases there's already some sort of project ID filter this created_at
index is unlikely to be used. For example:
gitlabhq_production=# explain analyze select * from issues where project_id = 13083 order by created_at desc;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=61029.77..61070.78 rows=16406 width=1123) (actual time=158.034..168.053 rows=14321 loops=1)
Sort Key: created_at
Sort Method: quicksort Memory: 14314kB
-> Bitmap Heap Scan on issues (cost=471.99..51803.19 rows=16406 width=1123) (actual time=6.278..91.828 rows=14321 loops=1)
Recheck Cond: (project_id = 13083)
-> Bitmap Index Scan on index_issues_on_project_id_and_iid (cost=0.00..467.89 rows=16406 width=0) (actual time=3.921..3.921 rows=14321 loops=1)
Index Cond: (project_id = 13083)
Total runtime: 176.729 ms
This query does not benefit from the created_at
index as it doesn't need to use it.
There may be some cases where one also filters by created_at
, but even there the index may not be beneficial if other indexes are already being used.