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.

• Suggested index for Oracle:

  CREATE INDEX PROPERTYENTRY_ENTITY_ID_NAME ON PROPERTYENTRY (ENTITY_ID, ENTITY_NAME) TABLESPACE JIRA;
  

• Suggested index for PostgreSQL:

  CREATE INDEX propertyentry_idx_entity_id_entity_name
  ON public.propertyentry USING btree (entity_id, entity_name COLLATE pg_catalog."default");
  

• Suggested index for MSSQL:

  CREATE NONCLUSTERED INDEX PROPERTYENTRY_IDX_ENTITY_ID_PROPERTY ON PROPERTYENTRY(ENTITY_ID,PROPERTY_KEY) INCLUDE(ENTITY_NAME)
  

  • Extra reading:
    • http://stackoverflow.com/questions/179085/multiple-indexes-vs-multi-column-indexes
    • https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns