Hello, we've upgraded the pg_trgm extension from 1.0 to 1.6 on our production database, while sticking our postgresql cluster version to 16. This led to an increase in the load average of the server (twice the load average on our use case). After investigation, we found our issue was linked to : https://github.com/postgres/postgres/commit/935f6666502250abde8615bc7805a6e5aa05a066
We issue queries like : SELECT model, res_id FROM ir_model_data WHERE module='base' AND name='public_user'; With 1.0 extension, the query is planned with a matching btree index: "ir_model_data_module_name_uniq_index" UNIQUE, btree (module, name) With 1.6 extension, the query is planned with a gist index: "ir_model_data_name_idx2" gist (name gist_trgm_ops) 1.0 extension executes the query in 0.1ms, while 1.6 in 100ms Our solution was to revert to pg_trgm 1.5, so remove operation 11 from gist_trgm_ops. After the removal, the load average was back to normal. Is there another way of preventing PostgreSQL to use the gist index when a btree exactly match the condition? Is it viable to stick with the extension in 1.6, but with the operation 11 removed from gist_trgm_ops? PostgreSQL 16 contains https://github.com/postgres/postgres/commit/cd9479af2af25d7fa9bfd24dd4dcf976b360f077 , but is this applicable to gist? Thanks in advance, Nicolas
