Re: BUG #17652: Performance degradation after migrating from v13.4 to v14.4

On Wed, Oct 19, 2022 at 1:30 PM Michael Guissine <mguissine@gmail.com> wrote:

Hi Jeff,

Sorry I should've mentioned that. Yes we did ANALYZE all the tables after upgrade, we even went ahead and REINDEX all indexes and then also run VACUUM ANALYZE but still facing same performance issue

I'm guessing that paritioned_table_hash_p3_partition_key_text_idx is a pg_trgm index, and that you are victim of the change "Allow GiST/GIN pg_trgm indexes to do equality lookups".  It is not surprising that this change would bite someone.  If you can redo the upgrade but this time refrain from upgrading pg_trgm from 1.5 to 1.6, that should fix the issue (assuming RDS even gives you enough control to do that), or drop the extension and recreate it specifically as version 1.5 (which unfortunately would cascade to dropping all the indexes, and they would need to be recreated after--even though the index structures themselves would require no change).

I have a file which would let you downgrade the extension without needing to drop all the pg_trgm indexes, but there is no way for you to deploy that to RDS.

Cheers,

Jeff