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

Hi,

On 2022-10-19 15:32:17 -0400, Michael Guissine wrote:
> The column names in the plan and queries are obfuscated

I guessed so :)

> Indexes:
> "partitioned_table_pkey" PRIMARY KEY, btree (partition_key, id)
> "partitioned_table_partition_key_start_time_index" btree
> (partition_key, start_time)
> "partitioned_table_partition_key_start_time_text" btree
> (partition_key, start_time, text)
> "partitioned_table_partition_key_text_idx" gin (partition_key,
> text gin_trgm_ops)
> "partitioned_table_text_tsv_index" gin
> (to_tsvector('simple'::regconfig, text)) WHERE text IS NOT NULL
> Number of partitions: 128 (Use \d+ to list them.)

Ok, that explains why we see the massive rechecks. For the bitmap scans we end
up using a gin index instead of the btree index.

The reason you're seeing this in 14 is likely the following entry in the
release notes:

> Allow GiST/GIN pg_trgm indexes to do equality lookups (Julien Rouhaud)
> This is similar to LIKE except no wildcards are honored.

CCed author/committer of that change. I suspect this would need proper costing
to not be inadvertently be used.

I assume you need the pg_trgm index?

In theory you could likely work around the issue by reverting the relevant
catalog change:

+ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
+ OPERATOR 11 pg_catalog.= (text, text);

by issuing

ALTER OPERATOR FAMILY gin_trgm_ops USING gin DROP OPERATOR 11 (text, text);

but I suspect you won't be allowed to do that in RDS.

Greetings,

Andres Freund