Date: Wed, 21 Jan 2026 10:14:02 +0100
From: Erik Wienhold <ewie@ewie.name>
To: Nicolas Seinlet <nicolas@seinlet.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
Message-ID: <261d81f9-3291-40e3-9af1-48724c649887@ewie.name>
References: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>
Archived-At: <https://www.postgresql.org/message-id/261d81f9-3291-40e3-9af1-48724c649887%40ewie.name>
Precedence: bulk

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