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