pg_trgm upgrade to 1.6 led to load average increase

From: Nicolas Seinlet <nicolas(at)seinlet(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: pg_trgm upgrade to 1.6 led to load average increase
Date: 2026-01-20 08:50:33
Message-ID: F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message ManiR 2026-01-20 09:17:36 Request for cryptographic mechanisms used in PostgreSQL
Previous Message Richard Guo 2026-01-19 02:39:37 Re: Fwd: pg18 bug? SELECT query doesn't work