After mod-inventory-storage was upgraded to mod-inventory-storage-18.2.1, it was noticed the compound search performance issue came back. See
MODINVSTOR-424. Looks like when module is upgraded, there was no index update/recreation. For example: "instance_title_idx" btree ("left"(lower(f_unaccent(jsonb ->> 'title'::text)), 600)). Need to look into this.
PostgreSQL doesn't upgrade an index when the index name is the same but the index expression changes (CREATE INDEX IF NOT EXISTS ...).
Therefore we decided to drop all indexes before the CREATE INDEX statements so that all indexes get recreated.
This may take long on big data sets when doing the module upgrade. But working with queries that take very long is worse.
RMB-498 caused an expression change for many indexes, it was released in RMB 29.1.3 and RMB 29.2.0 without recreating the indexes on module upgrade. So we need to recreated many indexes anyhow.
RMB-552 how we want to implement a more smart index upgrade in future.