Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-65168

Add Multi-Column Index to JIRA Tables

    XMLWordPrintable

Details

    • 106
    • 49
    • Hide
      Atlassian Update – 11 December 2018

      Hi everyone,

      Thank you for your votes and thoughts on this issue. We fully understand that many of you are dependent on this functionality.

      After careful consideration, we've decided to prioritize adding Multi-Column Index to Jira Tables on Jira Server roadmap and already started development.

      To learn more on how you suggestions are reviewed, see our updated workflow for server feature suggestions.

      Kind regards,

      Jira Server Product Management

      Show
      Atlassian Update – 11 December 2018 Hi everyone, Thank you for your votes and thoughts on this issue. We fully understand that many of you are dependent on this functionality. After careful consideration, we've decided to prioritize adding Multi-Column Index to Jira Tables on Jira Server roadmap and already started development. To learn more on how you suggestions are reviewed, see our updated workflow for server feature suggestions. Kind regards, Jira Server Product Management
    • We collect Jira feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

    Description

      Problem

      Some JIRA tables, like the propertyentry table contains many single column indexes, but many of the of SQL queries run by JIRA actually include checks for 2 or 3 of the columns.

      For example:
      propertyentry has indices on:

      jira728=# \d propertyentry ;
                 Table "public.propertyentry"
          Column    |          Type          | Modifiers
      --------------+------------------------+-----------
       id           | numeric(18,0)          | not null
       entity_name  | character varying(255) |
       entity_id    | numeric(18,0)          |
       property_key | character varying(255) |
       propertytype | numeric(9,0)           |
      Indexes:
          "pk_propertyentry" PRIMARY KEY, btree (id)
          "osproperty_all" btree (entity_id)
          "osproperty_entityname" btree (entity_name)
          "osproperty_propertykey" btree (property_key)
      

      A majority of the SQL queries run against this table include:

      • ENTITY_NAME, ENTITY_ID,PROPERTY_KEY
        SELECT ID, propertytype FROM propertyentry WHERE ENTITY_NAME= @P0  AND ENTITY_ID= @P1  AND PROPERTY_KEY= @P2
        
      • entity_name,entity_id
        select O_S_PROPERTY_ENTRY.property_key, O_S_PROPERTY_ENTRY.propertytype from propertyentry O_S_PROPERTY_ENTRY where O_S_PROPERTY_ENTRY.entity_name = :1 and O_S_PROPERTY_ENTRY.entity_id = :2 order by O_S_PROPERTY_ENTRY.id desc
        

      In these cases, it is left to the database to try and determine which index should be used, which is not always efficient. It may even cause extremely slow query times.

      Suggestion

      Add multi-column index to the table.

      Attachments

        Issue Links

          Activity

            People

              sutecht Seth Utecht (Inactive)
              dchan David Chan
              Votes:
              25 Vote for this issue
              Watchers:
              47 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: