From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nicolas Seinlet <nicolas@seinlet.com>
cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
In-reply-to: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>
References: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>
Comments: In-reply-to Nicolas Seinlet <nicolas@seinlet.com>
	message dated "Tue, 20 Jan 2026 08:50:33 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2215066.1769012876.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 21 Jan 2026 11:27:56 -0500
Message-ID: <2215067.1769012876@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2215067.1769012876%40sss.pgh.pa.us>
Precedence: bulk

Nicolas Seinlet <nicolas@seinlet.com> writes:
> We issue queries like :
> SELECT model, res_id FROM ir_model_data WHERE module=3D'base' AND name=3D=
'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

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.

Could we see EXPLAIN ANALYZE output for those two cases?
Are the statistics for the table up-to-date?

			regards, tom lane