C_STATUS_IDX Index causing slow indexing in Confluence

Symptoms

Enterprise Clients have been reporting problems with much slower content index rebuilding operations post-upgrade to Confluence 5.9.x. This has previously been reported as CONF-41010 Table index "c_status_idx" on CONTENT.content_status cause slow search index rebuilding on Oracle which was thought to effect only Oracle, but this has now been found in PostgreSQL and MS-SQL Server instances as well.

Diagnosis

Check to verify if the c_status_idx index exists for the table CONTENT on the Column CONTENT_STATUS

Explanation

The basic problem here is that the index C_STATUS_IDX on Column CONTENT_STATUS in Table CONTENT is of very low cardinality as the two values do not represent a lot of unique possibilities to search through.

Indexes with low cardinality are known to cause a negative impact on performance as a page set of data must be scanned for all values of the value being indexed. As the CONTENT table of the Confluence application holds the majority of rows, each record lookup takes a significant amount of time to isolate the record in question.

Since the values of CONTENT_STATUS is essentially a Boolean value (current or previous version) the index does not speed up the scan by isolating a unique value. Therefore, indexing requires nearly full table scans for every record in the CONTENT table.

Workaround

The only known workaround at present is to DROP the C_STATUS_IDX index which then restores the normal operation of the indexing operations within Confluence to the previous levels of indexing performance in terms of time.