cwd_user table missing a index

 This is a clone of a Confluence bug that has proven to affect JIRA.

Summary

Everywhere in Confluence DB tables, the creator is now set as the user hash. This username hash is stored in the table user_mapping which is made of three columns (user_key, username, lower_username). The last column has a unique index.

Now in cwd_user, there is no index on column lower_user_name. The only index there is on lower_user_name is composed with the other column directory_id.

So this means that for every action where Confluence looks up for creator information, it makes a full table scan on cwd_user. (Unless the caching in Confluence helps to avoid this ? )

Environment

• Confluence 5.9.4
• PostgreSQL 9.3.10

Steps to Reproduce

There are many actions in Confluence which could cause this to occur. One such query which has been found is the below one:

select internalgr2_.id as id,
internalgr2_.group_name as group_name,
internalgr2_.lower_group_name as lower_gr3_,
internalgr2_.active as active,
internalgr2_.local as local,
internalgr2_.created_date as created_6_,
internalgr2_.updated_date as updated_7_,
internalgr2_.description as descript8_,
internalgr2_.group_type as group_type,
internalgr2_.directory_id as directo10_
from cwd_membership hibernatem0_,
cwd_user internalus1_,
cwd_group internalgr2_
where hibernatem0_.parent_id=internalgr2_.id
and ((internalus1_.lower_user_name=:v2 and hibernatem0_.child_user_id=internalus1_.id)
and(internalgr2_.directory_id=:v1 and hibernatem0_.parent_id=internalgr2_.id))
order by internalgr2_.lower_group_name;

An explain plan on that shows a table scan is run on the lower_user_name field in cwd_user, if there is enough data in the tables being queried (in a fresh install there is no table scan, probably due to optimization from analyze statistics).

Expected Results

Database query returns quickly without performance impact, by using an index lookup.

Actual Results

Database query returns slowly, and causes a performance hit, as it's using a record lookup (table scan).

Workaround

Manually add an index on lower_user_name in the cwd_user table, for example:

create index idx_user_lower_user_name on cwd_user (lower_user_name);

NOTE: If any future fix adds a new index, you may need to manually remove this index before that.