Re: pg_trgm upgrade to 1.6 led to load average increase

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_CnPXSnZVzCkhQ@mail.gmail.com

--
Erik Wienhold