After we drop the base system index (indicator, breakdown, element, start_at) and create a new one that contains the second breakdown and second element (indicator, breakdown, element, breakdown_level2, element_level2, and start_at), during the next upgrade of the instance to a release before Istanbul, the base system index is recreated because the index is still in the table definition. This can increase the upgrade time significantly depending on the number of records that the pa_scores table contains.
Steps to Reproduce
- Provision an instance on one of the versions listed in the Seen In section below.
- Drop the index.
Index is usually named index1 and has the following fields: indicator, breakdown, element, and start_at.
- Create the index:
- Index: indicator
- Table: pa_scores
- Fields (in order - extremely important that this order is followed):
- Upgrade to Helsinki Patch 5.
During the upgrade, the index dropped in step 2 (indicator, breakdown, element, start_at) is recreated because it is still in the table definition. The index is added using an online alter. Depending on the number of records the instance contains, this process can take from a few minutes to several hours.
If the upgrade is in progress, there is no workaround for this issue. If you have not started the upgrade, re-add the index before upgrading. This can be done through the user interface without concern as the online alter will run as a scheduled job and not cause any performance issues.
Related Problem: PRB717668