Date: Thu, 22 Jan 2026 12:10:33 +0000
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
From: Nicolas Seinlet <nicolas@seinlet.com>
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
Message-ID: <RF0kFq4YRN25ujnOGY1aDWwq2MseT-QQAhpF8XphV5ZAPmGbjicJ_oWcUN0N9lBYOe6lry7qPMdCEMTZ6b-D0GUk5bkrdQFbdbSktjUfVVk=@seinlet.com>
In-Reply-To: <2215067.1769012876@sss.pgh.pa.us>
References: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com> <2215067.1769012876@sss.pgh.pa.us>
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/RF0kFq4YRN25ujnOGY1aDWwq2MseT-QQAhpF8XphV5ZAPmGbjicJ_oWcUN0N9lBYOe6lry7qPMdCEMTZ6b-D0GUk5bkrdQFbdbSktjUfVVk%3D%40seinlet.com>
Precedence: bulk

On Wednesday, January 21st, 2026 at 17:28, Tom Lane <tgl@sss.pgh.pa.us> wro=
te:

>=20
>=20
> Nicolas Seinlet nicolas@seinlet.com writes:
>=20
> > We issue queries like :
> > SELECT model, res_id FROM ir_model_data WHERE module=3D'base' AND name=
=3D'public_user';
>=20
> > With 1.0 extension, the query is planned with a matching btree index:
> > "ir_model_data_module_name_uniq_index" UNIQUE, btree (module, name)
>=20
> > With 1.6 extension, the query is planned with a gist index:
> > "ir_model_data_name_idx2" gist (name gist_trgm_ops)
>=20
> > 1.0 extension executes the query in 0.1ms, while 1.6 in 100ms
>=20
>=20
> It seems quite odd that the planner would prefer an index
> using only one of the two clauses. It must be concluding that
> the gist indexscan will be cheaper, but it's hard to see why
> that would be.
>=20
> Could we see EXPLAIN ANALYZE output for those two cases?
> Are the statistics for the table up-to-date?
>=20
> regards, tom lane

That table is not that much updated, so we could expect the statistics are =
quite up-to-date, especially because our analyse trigger parameters are qui=
te aggressive. But, to ensure they are decently correct, I'll include a vac=
uum analyse in my test.

With pg_trgm=3D1.5:
QUERY PLAN
-----------
 Index Scan using ir_model_data_module_name_uniq_index on ir_model_data  (c=
ost=3D0.56..2.58 rows=3D1 width=3D17) (actual time=3D0.027..0.028 rows=3D1 =
loops=3D1)
   Index Cond: (((module)::text =3D 'base'::text) AND ((name)::text =3D 'pu=
blic_user'::text))
 Planning Time: 0.093 ms
 Execution Time: 0.050 ms
(4 rows)

With pg_trgm=3D1.6:
QUERY PLAN
----------
 Index Scan using ir_model_data_name_idx2 on ir_model_data  (cost=3D0.42..2=
.44 rows=3D1 width=3D17) (actual time=3D8403.936..9847.983 rows=3D1 loops=
=3D1)
   Index Cond: ((name)::text =3D 'public_user'::text)
   Rows Removed by Index Recheck: 10
   Filter: ((module)::text =3D 'base'::text)
 Planning Time: 1.564 ms
 Execution Time: 9848.027 ms
(6 rows)

For the tests, the random_page_cost=3D1.

Let's try with random_page_cost=3D2
QUERY PLAN
----------
 Index Scan using ir_model_data_name_idx2 on ir_model_data  (cost=3D0.42..4=
.44 rows=3D1 width=3D17) (actual time=3D106.136..191.606 rows=3D1 loops=3D1=
)
   Index Cond: ((name)::text =3D 'public_user'::text)
   Rows Removed by Index Recheck: 10
   Filter: ((module)::text =3D 'base'::text)
 Planning Time: 0.096 ms
 Execution Time: 191.623 ms
(6 rows)

And finally, after a vacuum analyse and set random_page_cost to 1:
QUERY PLAN
-----------
 Index Scan using ir_model_data_name_idx2 on ir_model_data  (cost=3D0.42..4=
.44 rows=3D1 width=3D17) (actual time=3D104.866..189.119 rows=3D1 loops=3D1=
)
   Index Cond: ((name)::text =3D 'public_user'::text)
   Rows Removed by Index Recheck: 10
   Filter: ((module)::text =3D 'base'::text)
 Planning Time: 0.352 ms
 Execution Time: 189.134 ms
(6 rows)

Thanks for all,

Nicolas.