public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erik Wienhold <[email protected]>
To: Nicolas Seinlet <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
Date: Wed, 21 Jan 2026 10:14:02 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>
References: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>

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/[email protected]...

-- 
Erik Wienhold

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox