Optimize SQL query in com.atlassian.confluence.internal.persistence.hibernate.AbstractContentEntityObjectHibernateDao#queryContributionStatusByTouch

Query has poor performance on large CONTENT table. Please optimize it:

final String sqlString = "SELECT rt.currentId as contentId, rt.draftprevver as latestVersionid, rt.RELATIONID as relationId, " +
"CASE WHEN lt.LASTMODDATE < rt.touchdate THEN rt.CONTENT_STATUS ELSE lt.CONTENT_STATUS END as contentStatus, rt.touchdate as lastModifiedDate " +
"FROM CONTENT lt " +
"JOIN ( " +
"SELECT ucr.LASTMODDATE as touchdate, ucr.RELATIONID, c.LASTMODDATE, c.CONTENTID, " +
"c.TITLE, c.CONTENT_STATUS, coalesce(c.PREVVER, c.CONTENTID) as currentId, c.PREVVER as draftprevver " +
"FROM CONTENT c " +
"LEFT JOIN USERCONTENT_RELATION ucr " +
"ON c.CONTENTID = ucr.TARGETCONTENTID " +
"WHERE ucr.RELATIONNAME = 'touched' " +
"AND ucr.LASTMODIFIER = :userKey " +
" and c.CONTENTTYPE in (:contentTypes) " +
" and c.CONTENT_STATUS in (:contentStatuses) " +
") rt " +
"on lt.CONTENTID = rt.currentId " +
"WHERE lt.CONTENTID in (:contentIds)";

Using "coalesce(c.PREVVER, c.CONTENTID) as currentId" in subquery is bad idea. 

EXPAIN on our database

• • • • • • • • • • • • • • • • • • • • • • • • • • • 1. row ***************************
                                                      id: 1
                                                      select_type: SIMPLE
                                                      table: lt
                                                      partitions: NULL
                                                      type: const
                                                      possible_keys: PRIMARY
                                                      key: PRIMARY
                                                      key_len: 8
                                                      ref: const
                                                      rows: 1
                                                      filtered: 100.00
                                                      Extra: NULL
                                                    • 2. row ***************************
                                                      id: 1
                                                      select_type: SIMPLE
                                                      table: c
                                                      partitions: NULL
                                                      type: index
                                                      possible_keys: NULL
                                                      key: c_si_ct_pv_cs_cd_idx
                                                      key_len: 1559
                                                      ref: NULL
                                                      rows: 5356462
                                                      filtered: 4.00
                                                      Extra: Using where; Using index
                                                    • 3. row ***************************
                                                      id: 1
                                                      select_type: SIMPLE
                                                      table: ucr
                                                      partitions: NULL
                                                      type: ref
                                                      possible_keys: u2c_relation_unique,relation_u2c_targetcontent_idx
                                                      key: relation_u2c_targetcontent_idx
                                                      key_len: 8
                                                      ref: confluencedb.c.CONTENTID
                                                      rows: 1
                                                      filtered: 4.92
                                                      Extra: Using where
                                                      3 rows in set, 1 warning (0.00 sec)

Subquery is fetched 5356462 rows and after filtered it to 1 row.

WORKAROUND

Add extra condition in sub-query:

and (c.PREVVER in (:contentIds) or c.CONTENTID in (:contentIds))