public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Nicolas Seinlet <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
Date: Thu, 22 Jan 2026 14:12:27 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <RF0kFq4YRN25ujnOGY1aDWwq2MseT-QQAhpF8XphV5ZAPmGbjicJ_oWcUN0N9lBYOe6lry7qPMdCEMTZ6b-D0GUk5bkrdQFbdbSktjUfVVk=@seinlet.com>
References: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>
	<[email protected]>
	<RF0kFq4YRN25ujnOGY1aDWwq2MseT-QQAhpF8XphV5ZAPmGbjicJ_oWcUN0N9lBYOe6lry7qPMdCEMTZ6b-D0GUk5bkrdQFbdbSktjUfVVk=@seinlet.com>

Nicolas Seinlet <[email protected]> writes:
> With pg_trgm=1.5:
> QUERY PLAN
> -----------
>  Index Scan using ir_model_data_module_name_uniq_index on ir_model_data  (cost=0.56..2.58 rows=1 width=17) (actual time=0.027..0.028 rows=1 loops=1)
>    Index Cond: (((module)::text = 'base'::text) AND ((name)::text = 'public_user'::text))
>  Planning Time: 0.093 ms
>  Execution Time: 0.050 ms
> (4 rows)

> With pg_trgm=1.6:
> QUERY PLAN
> ----------
>  Index Scan using ir_model_data_name_idx2 on ir_model_data  (cost=0.42..2.44 rows=1 width=17) (actual time=8403.936..9847.983 rows=1 loops=1)
>    Index Cond: ((name)::text = 'public_user'::text)
>    Rows Removed by Index Recheck: 10
>    Filter: ((module)::text = 'base'::text)
>  Planning Time: 1.564 ms
>  Execution Time: 9848.027 ms
> (6 rows)

Hmm, so the cost estimates are nearly the same for both index types.
I can reproduce that locally if the gist-indexed column is estimated
to have only one match; then the additional index condition isn't
believed to improve the selectivity any, so the planner has no
motivation to prefer using the two-column index.

What I don't reproduce here is the huge differential in actual cost.
The gist index is a bit slower for me, but not five orders of
magnitude slower.  However, my test table isn't huge --- about
80K rows --- and I suppose from these numbers that yours must be
far larger.

So I'm thinking that the default signature length for gist_trgm_ops
isn't large enough for your data set and what you want to do is
make it bigger:

CREATE INDEX ... USING GIST (name gist_trgm_ops(siglen = X));

The default value is 12 bytes, so maybe try 24 or 32.  That will
result in an index that's physically larger, but with luck
less of it will need to be scanned for any one query.

			regards, tom lane

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], [email protected]
  Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
  In-Reply-To: <[email protected]>

* 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