Performance regression on full reindex for Jira 9.5 onwards

Issue Summary

Suppose a full reindex takes 60 minutes to complete on Jira 9.4.latest.
When upgrading this same instance to 9.5 or any other recent version, such as 9.12 LTS, the full reindex takes around 120 minutes to complete.

This was identified on instances connected to PostgreSQL, sqlserver and Oracle DB.
We've validated the same scenario with other supported databases and couldn't recreate the problem.

Steps to Reproduce

1. Install a vanilla instance of Jira 9.4.latest with PostgreSQL as the DBMS.
   • This was tested with Jira 9.4.18 and PostgreSQL 14.11.
2. Have a reasonable number of data imported to make a full reindex taking more than 10 minutes.
3. Stop Jira.
4. Run the regular PostgreSQL maintenance tasks on the database.
   • VACUUM, ANALYZE, and REINDEX
5. Start Jira.
6. Run a Full Reindex a couple of times.
   • Take note of the time it takes for each run.
7. Upgrade Jira to 9.12.latest.
   • This was validated with Jira 9.12.5 and with 9.14.0.
   • Note the root cause of this issue was introduced on Jira 9.5.
8. Stop Jira after the upgrade completes.
9. Run the regular PostgreSQL maintenance tasks on the database.
10. Run a Full Reindex a couple of times.

Expected Results

It takes about the same time as it used to on Jira 9.4.

Actual Results

It takes roughly double the time comparing to 9.4.

Workaround

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