Date: Wed, 21 Jan 2026 08:38:56 +0000
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>, Adrian Klaver <adrian.klaver@aklaver.com>
From: Nicolas Seinlet <nicolas@seinlet.com>
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
Message-ID: <IxuUZAMktNuKxR3PPfUvMkcb6-Avjmq1kB9jBEmEOPFo-NIqMc3IVuXf1w3K6ob_s1lSyPLnvP4NA8X6n6v0H1Um_3FaLHvbLKNe24EmU-k=@seinlet.com>
In-Reply-To: <b6443638-9947-474b-ac5a-967e89142387@aklaver.com>
References: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com> <b6443638-9947-474b-ac5a-967e89142387@aklaver.com>
Feedback-ID: 32582315:user:proton
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/IxuUZAMktNuKxR3PPfUvMkcb6-Avjmq1kB9jBEmEOPFo-NIqMc3IVuXf1w3K6ob_s1lSyPLnvP4NA8X6n6v0H1Um_3FaLHvbLKNe24EmU-k%3D%40seinlet.com>
Precedence: bulk

On Tuesday, January 20th, 2026 at 17:34, Adrian Klaver <adrian.klaver@aklav=
er.com> wrote:

>=20
>=20
> On 1/20/26 00:50, Nicolas Seinlet wrote:
>=20
> > Hello,
> >=20
> > 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 averag=
e
> > on our use case). After investigation, we found our issue was linked to=
 :
> > https://github.com/postgres/postgres/
> > commit/935f6666502250abde8615bc7805a6e5aa05a066 <https://github.com/
> > postgres/postgres/commit/935f6666502250abde8615bc7805a6e5aa05a066>
> >=20
> > We issue queries like :
> > SELECT model, res_id FROM ir_model_data WHERE module=3D'base' AND
> > name=3D'public_user';
>=20
>=20
>=20
> You need to provide the table definition, including indexes.
>=20

Hello,

here is the table definition:

                                         Table "public.ir_model_data"
   Column    |            Type             | Collation | Nullable |        =
          Default
-------------+-----------------------------+-----------+----------+--------=
-----------------------------------
 id          | integer                     |           | not null | nextval=
('ir_model_data_id_seq'::regclass)
 create_uid  | integer                     |           |          |
 create_date | timestamp without time zone |           |          | timezon=
e('UTC'::text, now())
 write_date  | timestamp without time zone |           |          | timezon=
e('UTC'::text, now())
 write_uid   | integer                     |           |          |
 noupdate    | boolean                     |           |          | false
 name        | character varying           |           | not null |
 module      | character varying           |           | not null |
 model       | character varying           |           | not null |
 res_id      | integer                     |           |          |
Indexes:
    "ir_model_data_pkey" PRIMARY KEY, btree (id)
    "ir_model_data_create_uid_idx" btree (create_uid) WHERE create_uid IS N=
OT NULL AND (create_uid <> ALL (ARRAY[1, 208196]))
    "ir_model_data_model_name_multilang_index" btree (name) WHERE model::te=
xt =3D ANY (ARRAY['account.account'::character varying::text, 'account.grou=
p'::character varying::text, 'account.tax'::character varying::text, 'accou=
nt.fiscal.position'::character varying::text])
    "ir_model_data_model_res_id_index" btree (model, res_id)
    "ir_model_data_module_name_uniq_index" UNIQUE, btree (module, name)
    "ir_model_data_name_idx2" gist (name gist_trgm_ops)
    "ir_model_data_write_uid_idx" btree (write_uid) WHERE write_uid IS NOT =
NULL AND (write_uid <> ALL (ARRAY[1, 208196]))
Check constraints:
    "ir_model_data_name_nospaces" CHECK (name::text !~~ '% %'::text)

> > Thanks in advance,
> >=20
> > Nicolas
>=20
>=20
>=20
> --
> Adrian Klaver
> adrian.klaver@aklaver.com