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

paritioned_table_hash_p3_partition_key_text_idx is in fact pg_trgm index,
you are right!
interesting, why would it use that index at all on some partitions but not
on others? the query is a simple match on 3 columns and we have other
indexes to cover it

On Wed, Oct 19, 2022 at 3:01 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Oct 19, 2022 at 1:30 PM Michael Guissine <mguissine(at)gmail(dot)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
>
>>