pg_trgm upgrade to 1.6 led to load average increase

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Nicolas Seinlet <[email protected]>
To: [email protected] <[email protected]>
Subject: pg_trgm upgrade to 1.6 led to load average increase
Date: Tue, 20 Jan 2026 08:50:33 +0000
Message-ID: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com> (raw)

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

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]
  Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
  In-Reply-To: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>

* 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