Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

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


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, 

then query analyzing, adding indexes, after removing as heavy one (shortly

) :) 


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,
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
  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

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)



   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 ( , )




Hope it helps. 

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



Gonchik Tsymzhitov


M Amine
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Apr 12, 2021

Hi @Gonchik Tsymzhitov 

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

Like Gonchik Tsymzhitov likes this
Gonchik Tsymzhitov
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Apr 12, 2021

Hi @M Amine , 

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

M Amine
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Apr 17, 2021


publicAO_733371_EVENT_PARAMETERindex_ao_733371_eve142394589917 MB000
publiccwd_user_attributesidx_user_attr_dir_name_lval7376 kB000
publicAO_54307E_SLAAUDITLOGindex_ao_54307e_sla18439304811848 kB000
publicAO_8542F1_IFJ_PRG_OT_RESindex_ao_8542f1_ifj5614896721408 kB000
publicjiraissueissue_resolutiondate952 kB000
publicjiraissueissue_votes920 kB000
publicjiraissueissue_watches856 kB000
publicAO_733371_EVENTindex_ao_733371_event_user_key784 kB000
publicAO_733371_EVENTindex_ao_733371_event_action736 kB000
publicAO_8542F1_IFJ_IMPORT_MISSindex_ao_8542f1_ifj539071317712 kB000
publicAO_733371_EVENTindex_ao_733371_eve902883849560 kB000
publicaudit_logidx_audit_log_created528 kB000
publicAO_4E8AE6_NOTIF_BATCH_QUEUEindex_ao_4e8ae6_not850480572480 kB000
publicAO_4E8AE6_NOTIF_BATCH_QUEUEindex_ao_4e8ae6_not1193702477448 kB000
publicAO_4E8AE6_NOTIF_BATCH_QUEUEindex_ao_4e8ae6_not1081986701384 kB000
publicAO_8542F1_IFJ_OBJindex_ao_8542f1_ifj1752004591232 kB000
publicAO_8542F1_IFJ_OBJindex_ao_8542f1_ifj1846712946224 kB000
publiccwd_useridx_last_name168 kB000
publicissuelinkissuelink_type136 kB000
publicAO_319474_QUEUEindex_ao_319474_queue_topic136 kB000
publiccwd_useridx_first_name120 kB000
publicfieldscreenlayoutitemfieldscreen_field112 kB000
publicAO_319474_MESSAGEindex_ao_319474_message_msg_id72 kB000
publicAO_D530BB_CANNEDRESPONSEUSAGEindex_ao_d530bb_can95513006056 kB000
publictempattachmentsmonitoridx_tam_by_form_token56 kB000
publicconfigurationcontextconfcontext48 kB000
publicclusteredjobclusteredjob_jrk_idx40 kB000
publicavataravatar_filename_index40 kB000
publicfieldconfigurationfc_fieldid40 kB000
publicAO_319474_MESSAGEindex_ao_319474_mes169701299540 kB000


Log in or Sign up to comment
AUG Leaders

Atlassian Community Events