On 2026-01-20 09:50 +0100, Nicolas Seinlet wrote: > 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?
It's been suggested to move the GiST index to another tablespace with increased random_page_cost. [1] > Is ivt viable to stick with the extension in 1.6, but with the > operation 11 removed from gist_trgm_ops? This would be the same as sticking with 1.5 since the new equality operator is the only change in 1.6. [1] https://www.postgresql.org/message-id/CAApHDvp3W7G8Oo4=wjt0ceTbic35SHJ=qfod_cnpxsnzvzc...@mail.gmail.com -- Erik Wienhold
