travis-ci/travis-core — pull request #350 by henrikhodne (2014-02-10 08:44:52+00:00)
Title: db: remove unused and rarely used indices

These indices have never been scanned on the production database:

```
heroku pg:unused_indexes --app travis-production
table | index | index_size | index_scans
-----------------+-----------------------------------+------------+-------------
public.commits | index_commits_on_commit | 532 MB | 0
public.builds | index_builds_on_state | 161 MB | 0
public.commits | index_commits_on_branch | 149 MB | 0
public.users | index_users_on_github_oauth_token | 16 MB | 0
```

`users.github_oauth_token` is an encrypted column, so no query can be filtered on that column.

The `build(finished_at)`, `jobs(queue,state)`, `jobs(created_at)` and `jobs(repository_id)` indices have very rarely been used:

```
indexname | index_size | index_scans
-------------------------------+------------+-------------
index_builds_on_finished_at | 131 MB | 98
index_jobs_on_queue_and_state | 740 MB | 19
index_jobs_on_created_at | 681 MB | 38
index_jobs_on_repository_id | 580 MB | 31
```

These indices take up about 3 GB on disk.