Research issues index usage

This is a list of the indexes we have on the issues table with a link to the Grafana dashboard to get the usage over the past 6 months + a comment about the usage.

Proposed new tables

Issues table

Table: public.issues

Column	Type	Collation	Nullable	Default
id	bigint		not null	nextval('issues_id_seq'::regclass)
title	character varying
author_id	bigint
project_id	bigint
created_at	timestamp without time zone
updated_at	timestamp without time zone
milestone_id	bigint
iid	integer
weight	integer
confidential	boolean		not null	false
due_date	date
title_html	text
time_estimate	integer			0
relative_position	integer
service_desk_reply_to	character varying
discussion_locked	boolean
closed_at	timestamp with time zone
state_id	smallint		not null	1
health_status	smallint
external_key	character varying(255)
blocking_issues_count	integer		not null	0
upvotes_count	integer		not null	0
work_item_type_id	bigint
namespace_id	bigint
start_date	date
imported_from	smallint		not null	0
author_id_convert_to_bigint	bigint
id_convert_to_bigint	bigint		not null	0
milestone_id_convert_to_bigint	bigint
project_id_convert_to_bigint	bigint

Work Item Descriptions Table

Table: public.work_item_descriptions

Column	Type	Collation	Nullable	Default
work_item_id	bigint		not null
description	text
description_html	text
cached_markdown_version	integer
last_edited_at	timestamp without time zone
last_edited_by_id	bigint
lock_version	integer			0
namespace_id	bigint		not null
root_namespace_id	bigint		not null
search_vector	tsvector		not null

Work Item Metadta Table

Table: public.work_item_transitions

Column	Type	Collation	Nullable	Default
work_item_id	bigint		not null
moved_to_id	bigint
promoted_to_epic_id	bigint
duplicated_to_id	bigint

Index overview

Note there is https://docs.google.com/spreadsheets/d/1WZlnM4jxjflPeIiFuTNSv7TsqFQE8X9a9clCsSnG3ZI/edit?gid=0#gid=0 which this table is copied from and a bit easier to sort

#	Index	Note	Usage	Status	MR
1	index_issues_on_sprint_id	Only there because of the FK constraint	Low usage	⛓️‍💥 Potential decomposition
2	index_issues_on_updated_by_id	Only there because of the FK constraint	Low usage	⛓️‍💥 Potential decomposition
3	index_issues_on_author_id	Only there because of the FK constraint	Low usage	⛓️‍💥 Potential decomposition
4	index_issues_on_milestone_id_and_id	Needs to be kept for FK constraints, but usage got lower recently	Lower usage	⛓️‍💥 Potential decomposition
5	index_issues_on_closed_by_id	Only there because of the FK constraint	Low usage	⛓️‍💥 Potential decomposition
6	index_issues_on_work_item_type_id_project_id_created_at_state		Normal usage	✅ Keep
7	index_open_issues_on_namespace_id_confidential_author_id_id	Due to switch to `namespace_id`	Usage increased	✅ Keep
8	index_issues_on_namespace_id_iid_unique	IID index, can't be removed		✅ Keep
9	issues_pkey	Primary key index, can't be removed		✅ Keep
10	idx_issues_on_project_id_and_created_at_and_id_and_state_id	Should be only replaced with `namespace_id` at some point	High usage	✅ Keep
11	idx_issues_on_project_id_and_rel_position_and_id_and_state_id	Could be replaced by `namespace_id`	High usage	✅ Keep
12	idx_issues_on_project_id_and_updated_at_and_id_and_state_id	Could be replaced by `namespace_id`	High usage	✅ Keep
13	idx_open_issues_on_project_and_confidential_and_author_and_id	Could be replaced by `namespace_id`	High usage	✅ Keep
14	index_closed_incidents_on_namespace_id_closed_at	Got introduced recently	Still used	✅ Keep
15	index_issues_on_namespace_id_created_at_id_state_id	Will be used more once we migrate to `namespace_id`	Low usage	✅ Keep
16	index_issues_on_namespace_id_relative_position_id_state_id	Will be used more once we migrate to `namespace_id`	Low usage	✅ Keep
17	index_issues_on_namespace_id_updated_at_id_state_id	Will be used more once we migrate to `namespace_id`	Low usage	✅ Keep
18	index_issues_on_project_id_and_iid		Normal usage	✅ Keep
19	index_issues_on_project_id_and_upvotes_count		Normal usage	✅ Keep
20	index_issues_on_title_trigram_non_latin	Due to usage of Elasticsearch on .com	Low usage	✅ Keep
21	index_issues_on_work_item_type_id_namespace_id_created_at_state	Due to switch to `namespace_id`	Usage increased	✅ Keep
22	idx_issues_on_project_id_and_due_date_and_id_and_state_id	We store `due_date` in `work_item_date_sources` now, can we use that?	Low usage	🤔 Potential removal
23	idx_issues_on_project_work_item_type_closed_at_where_closed	Likely duplicated because we have index_issues_on_work_item_type_id_project_id_created_at_state	Lower usage	🤔 Potential removal
24	index_issue_on_project_id_state_id_and_blocking_issues_count	Usage on the lower end	Lower usage	🤔 Potential removal
25	index_issues_on_id_and_weight	Usage went down recently, maybe because we changed our queries for weights ?	High usage	🤔 Potential removal
26	index_issues_on_project_id_closed_at_desc_state_id_and_id		Low usage	🤔 Potential removal
27	index_issues_on_updated_at	Not sure which query uses this	Low usage	🤔 Potential removal
28	idx_issues_on_health_status_not_null	Can be removed	Low usage	🚧 To be removed
29	index_issues_on_duplicated_to_id	Only there because of the FK constraint, can be moved to work_item_transitions	Low usage	🚧 To be removed
30	index_issues_on_moved_to_id	Only there because of the FK constraint, can be moved to work_item_transitions	Low usage	🚧 To be removed
31	index_issues_on_promoted_to_epic_id	Only there because of the FK constraint, can be moved to work_item_transitions	Low usage	🚧 To be removed
32	index_issues_on_description_trigram_non_latin	Low usage likely because of .com using Elasticsearch, will be moved to work_item_descriptions	Also low usage	🚧 To be removed
33	index_issues_on_last_edited_by_id	Only there because of the FK constraint, will be moved to work_item_descriptions	Low usage	🚧 To be removed
34	index_issues_on_project_health_status_asc_work_item_type	No longer used by the optimizer	Low usage	🚧 To be removed
35	index_issues_on_project_id_and_external_key	Column never got used, can be dropped	No usage	🚧 To be removed