Atlassian Community, let's collaborate and provide stats to vendors about our SQL index usage

[#1]

Hi! 

Gonchik is in touch.

Today I would like to get a little bit of help from you related SQL indexes on your Jira installation (Data Center, Server edition does not matter). 

I would like to share a story about PostgreSQL and Jira 8.13.3 (JSD 4.13.3)  release. 

In that post I will be happy if you answer and comment about your instance indexes, as those info can help to improve the existing situation of heavy indexes. 

Typically, it starts from improvement of configuration (e.g. using https://postgresqltuner.pl/), 

then query analyzing, adding indexes, after removing as heavy one (shortly https://wiki.postgresql.org/wiki/Index_Maintenance

) :) 

I use like this query in PostgreSQL (don't forget to connect your jiradb via \c ):

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_size_pretty(pg_relation_size(s.indexrelid::regclass)) AS index_size,
       idx_tup_read, 
       idx_tup_fetch, 
       idx_scan
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE 
  s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique  -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 30;

PostgreSQL stats query 

My result is 

schemaname  |           tablename            |           indexname            | index_size
------------+--------------------------------+--------------------------------+------------
 public     | AO_54307E_SLAAUDITLOG          | index_ao_54307e_sla1843930481  |  951 MB
 public     | AO_733371_EVENT_PARAMETER      | index_ao_733371_eve1423945899  |  625 MB
 public     | cwd_user_attributes            | idx_user_attr_dir_name_lval    |  212 MB
 public     | AO_544E33_AUDIT_LOG_ENTRY      | index_ao_544e33_aud1602451017  |  188 MB
 public     | clusteredjob                   | clusteredjob_nextrun_idx       |  170 MB
 public     | AO_319474_QUEUE                | index_ao_319474_queue_topic    |  121 MB
 public     | AO_319474_MESSAGE              | index_ao_319474_mes1697012995  |   74 MB
 public     | AO_8542F1_IFJ_OBJ              | index_ao_8542f1_ifj1846712946  |   60 MB
 public     | AO_544E33_AUDIT_LOG_ENTRY      | index_ao_544e33_aud845075831   |   58 MB
 public     | AO_544E33_AUDIT_LOG_ENTRY      | index_ao_544e33_aud449906846   |   50 MB
 public     | AO_544E33_AUDIT_LOG_ENTRY      | index_ao_544e33_aud365482369   |   49 MB
 public     | AO_319474_MESSAGE              | index_ao_319474_mes525710975   |   44 MB
 public     | AO_319474_MESSAGE              | index_ao_319474_mes1815442463  |   43 MB
 public     | AO_544E33_AUDIT_LOG_ENTRY      | index_ao_544e33_aud1940082561  |   41 MB
 public     | AO_544E33_AUDIT_LOG_ENTRY      | index_ao_544e33_aud740477310   |   40 MB
 public     | AO_544E33_AUDIT_LOG_ENTRY      | index_ao_544e33_aud365906193   |   37 MB
 public     | issue_version                  | iv_parent_id                   |   32 MB
 public     | AO_733371_EVENT                | index_ao_733371_event_user_key |   30 MB
 public     | jiraissue                      | issue_resolutiondate           |   26 MB
 public     | jiraissue                      | issue_watches                  |   24 MB
 public     | jiraissue                      | issue_duedate                  |   24 MB
 public     | AO_733371_EVENT                | index_ao_733371_eve902883849   |   21 MB
 public     | AO_733371_EVENT                | index_ao_733371_event_action   |   21 MB
 public     | AO_C77861_AUDIT_ENTITY         | index_ao_c77861_aud237541374   |   13 MB
 public     | AO_4E8AE6_NOTIF_BATCH_QUEUE    | index_ao_4e8ae6_not1193702477  |   12 MB
 public     | AO_C77861_AUDIT_ENTITY         | index_ao_c77861_aud265617021   |   11 MB
 public     | AO_C77861_AUDIT_ENTITY         | index_ao_c77861_aud470300084   |   11 MB
 public     | AO_4E8AE6_NOTIF_BATCH_QUEUE    | index_ao_4e8ae6_not850480572   |    5 MB
 public     | AO_D530BB_CANNEDRESPONSEUSAGE  | index_ao_d530bb_can955130060   |    2 MB
 public     | cwd_user                       | idx_last_name                  | 1212 KB
 public     | cwd_user                       | idx_first_name                 | 1171 KB
 public     | label                          | label_fieldissuelabel          |  974 KB
 public     | AO_AEFED0_USER_INDEX           | index_ao_aefed0_use916119569   |  688 KB 
 public     | label                          | label_fieldissue               |  638 KB
 public     | label                          | label_label                    |  565 KB

(35 rows)

Conclusion: 

   Based on that info, you can request that info to the app vendor to improve the situation. You can use that article to easy identify the vendor ( https://confluence.atlassian.com/jirakb/list-of-jira-server-ao-table-names-and-vendors-973498988.html , https://github.com/gonchik/cleanup-scripts/blob/master/sql/jira/jira_plugins_mapping_with_AO_tables.sql )

References:

• https://wiki.postgresql.org/wiki/Performance_Optimization
• https://pastebin.com/WMQh2rj6
• https://postgresqltuner.pl/
• https://wiki.postgresql.org/wiki/Index_Maintenance
• https://github.com/gonchik/cleanup-scripts/blob/master/sql/jira/jira_plugins_mapping_with_AO_tables.sql
• https://confluence.atlassian.com/jirakb/list-of-jira-server-ao-table-names-and-vendors-973498988.html
• https://github.com/gonchik/cleanup-scripts/blob/master/sql/jira/stats/jira_postgresql_get_index_stats.sql

Hope it helps. 

Next time I will share story of improving and non-improving frequent queries created by a famous app.

Cheers,

Gonchik Tsymzhitov

[M Amine]

Hi @Gonchik Tsymzhitov 

excellent idea. Will definitely get back you with some stats.

[Gonchik Tsymzhitov]

Hi @M Amine , 

Thank you! Yeah, it will be good to compare and check the usage. 

[M Amine]

schemanametablenameindexnameindex_sizeidx_tup_readidx_tup_fetchidx_scan

public	AO_733371_EVENT_PARAMETER	index_ao_733371_eve1423945899	17 MB	0	0	0
public	cwd_user_attributes	idx_user_attr_dir_name_lval	7376 kB	0	0	0
public	AO_54307E_SLAAUDITLOG	index_ao_54307e_sla1843930481	1848 kB	0	0	0
public	AO_8542F1_IFJ_PRG_OT_RES	index_ao_8542f1_ifj561489672	1408 kB	0	0	0
public	jiraissue	issue_resolutiondate	952 kB	0	0	0
public	jiraissue	issue_votes	920 kB	0	0	0
public	jiraissue	issue_watches	856 kB	0	0	0
public	AO_733371_EVENT	index_ao_733371_event_user_key	784 kB	0	0	0
public	AO_733371_EVENT	index_ao_733371_event_action	736 kB	0	0	0
public	AO_8542F1_IFJ_IMPORT_MISS	index_ao_8542f1_ifj539071317	712 kB	0	0	0
public	AO_733371_EVENT	index_ao_733371_eve902883849	560 kB	0	0	0
public	audit_log	idx_audit_log_created	528 kB	0	0	0
public	AO_4E8AE6_NOTIF_BATCH_QUEUE	index_ao_4e8ae6_not850480572	480 kB	0	0	0
public	AO_4E8AE6_NOTIF_BATCH_QUEUE	index_ao_4e8ae6_not1193702477	448 kB	0	0	0
public	AO_4E8AE6_NOTIF_BATCH_QUEUE	index_ao_4e8ae6_not1081986701	384 kB	0	0	0
public	AO_8542F1_IFJ_OBJ	index_ao_8542f1_ifj1752004591	232 kB	0	0	0
public	AO_8542F1_IFJ_OBJ	index_ao_8542f1_ifj1846712946	224 kB	0	0	0
public	cwd_user	idx_last_name	168 kB	0	0	0
public	issuelink	issuelink_type	136 kB	0	0	0
public	AO_319474_QUEUE	index_ao_319474_queue_topic	136 kB	0	0	0
public	cwd_user	idx_first_name	120 kB	0	0	0
public	fieldscreenlayoutitem	fieldscreen_field	112 kB	0	0	0
public	AO_319474_MESSAGE	index_ao_319474_message_msg_id	72 kB	0	0	0
public	AO_D530BB_CANNEDRESPONSEUSAGE	index_ao_d530bb_can955130060	56 kB	0	0	0
public	tempattachmentsmonitor	idx_tam_by_form_token	56 kB	0	0	0
public	configurationcontext	confcontext	48 kB	0	0	0
public	clusteredjob	clusteredjob_jrk_idx	40 kB	0	0	0
public	avatar	avatar_filename_index	40 kB	0	0	0
public	fieldconfiguration	fc_fieldid	40 kB	0	0	0
public	AO_319474_MESSAGE	index_ao_319474_mes1697012995	40 kB	0	0	0