Issue Summary

Editing or copying a Confluence page with an attachment triggers the execution of the following SQL via content-reconciliation threads:

YYYY-MM-DD HH:mm:SS,mmm DEBUG [reconciliation-executor:thread-6] [org.hibernate.SQL] logStatement 
select 
	bodyconten0_.BODYCONTENTID as bodycont1_13_, 
	bodyconten0_.BODY as body2_13_, 
	bodyconten0_.CONTENTID as contenti3_13_, 
	bodyconten0_.BODYTYPEID as bodytype4_13_ 
from 
	BODYCONTENT bodyconten0_ 
where 
	cast(contentId as varchar(255)) in (?)
	-- comment: the "in" clause above will have one or more contentId values

In the SQL shown above, the "cast(contentId as varchar(255))" part in the where clause converts numeric Content IDs into varchar strings.
This in turn ignores the already existing body_content_idx database-index defined on the bodycontent table's contentid column, since that database-index operates on a column having numeric values.
Due to this absence of a suitable database-index for matching varchar casted numbers/contentIDs, a full scan of the bodycontent table becomes necessary, which can take time (multiple minutes) on large enough datasets.

For comparison, the following SQL statement will be magnitudes faster (milliseconds level of execution time) since the existing body_content_idx database-index aids in findings the relevant row(s) faster:

select 
	bodyconten0_.BODYCONTENTID as bodycont1_13_, 
	bodyconten0_.BODY as body2_13_, 
	bodyconten0_.CONTENTID as contenti3_13_, 
	bodyconten0_.BODYTYPEID as bodytype4_13_ 
from 
	BODYCONTENT bodyconten0_ 
where 
	contentId in (?)
	-- comment: the "in" clause above will have one or more contentId values

Steps to Reproduce

On a Confluence 9.2.x or 9.1.x instance:

1. (optional) to see the actual SQL causing suboptimal performance, either:
   • enable SQL logging on Confluence
     (recommended only for brief intervals, use only during troubleshooting/investigation and turn off after the relevant data has been gathered), OR
   • obtain the slow-query logs/data from the database-server side (location/format varies based on the database vendor, please engage a Database Admin for this)
2. create a new Confluence page, drag and drop an attachment on that page in the editor, save the page
   • this action should result in a reconciliation-executor thread (within Confluence) triggering the problematic SQL (example shown in the Issue Summary section)
3. (optional) the same reconciliation logic kicks in if this page is copied (with its attachment) to a different location in the page-hierarchy or another space on the instance

Issue is also reproducible like this:

1. create a new test space on your instance
2. create a new page under this space
3. create 4 child pages under the new page
4. upload an attachment to the parent page
5. add the same attachment to the child pages by referring them from the parent page
6. edit the parent page, remove the existing attachment, and upload it again with the same name
7. save and publish the page

Expected Results

Reconciliation threads should execute performant/optimized queries that use existing database-indexes for fast data retrieval.

Actual Results

Reconciliation threads end up casting numeric content IDs as varchar strings in the relevant SQL statements, which ignores the existing body_content_idx database-index defined on the bodycontent table's contentid column, resulting in significantly longer SQL execution times.

Workaround

Currently there is no known workaround for this behavior. A workaround will be added here when available.

Versions Tested

Please confirm all versions that have been tested for this issue, and indicate whether the tested version is affected or not affected, below: