Skip to content

Investigate and improve indices for issues table

Open and closed issues don't typically get queried together and the following indices on issues table do not seem to reflect this domain consideration:

  • "idx_issues_on_project_id_and_created_at_and_id_and_state_id" btree (project_id, created_at, id, state_id)
  • "idx_issues_on_project_id_and_due_date_and_id_and_state_id" btree (project_id, due_date, id, state_id) WHERE due_date IS NOT NULL
  • "idx_issues_on_project_id_and_updated_at_and_id_and_state_id" btree (project_id, updated_at, id, state_id)

To investigate and possibly improve the performance of issues queries, we can introduce new indices with rearranged column orders and collect the index usages for the existing and newly added indices then proceed to remove unnecessary indices based on the data collected.

Stats on index usages :

SELECT * FROM pg_stat_all_indexes WHERE indexrelname in ('idx_issues_on_project_id_and_created_at_and_id_and_state_id', 'idx_issues_on_project_id_and_due_date_and_id_and_state_id', 'idx_issues_on_project_id_and_updated_at_and_id_and_state_id', 'index_issue_on_project_id_state_id_and_blocking_issues_count');

On Aug 2, 2021:

 relid | indexrelid | schemaname | relname |                         indexrelname                         | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+---------+--------------------------------------------------------------+----------+--------------+---------------
 33462 | 1029493977 | public     | issues  | idx_issues_on_project_id_and_created_at_and_id_and_state_id  | 77054283 |   1736533612 |    1267860612
 33462 |  312160929 | public     | issues  | idx_issues_on_project_id_and_due_date_and_id_and_state_id    |   291143 |       517673 |        401184
 33462 | 1139805917 | public     | issues  | idx_issues_on_project_id_and_updated_at_and_id_and_state_id  |  1209574 |     27909324 |      12134920
 33462 | 1109367232 | public     | issues  | index_issue_on_project_id_state_id_and_blocking_issues_count | 57575357 |   3872795687 |    3222069343

On Aug 26, 2021: #337400 (comment 661535164)

Notice that index_issue_on_project_id_state_id_and_blocking_issues_count is a highly used index. Some queries (example: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5629/commands/19092) may be using this index to get issues with open or closed state first then apply filters on the result even when blocking_issues_count is not involved at all.

Edited by euko