public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nicolas Seinlet <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
Date: Thu, 22 Jan 2026 12:10:33 +0000
Message-ID: <RF0kFq4YRN25ujnOGY1aDWwq2MseT-QQAhpF8XphV5ZAPmGbjicJ_oWcUN0N9lBYOe6lry7qPMdCEMTZ6b-D0GUk5bkrdQFbdbSktjUfVVk=@seinlet.com> (raw)
In-Reply-To: <[email protected]>
References: <F-DrgibQiu1I_ItlkIz765ee1SJJvVg187PNonwprn0eT7GC56_kJcU8CkP8Y10bs4-TT6n_JJ3fFPP4H3xPtJLc5W5bE9AATi99VWaKbYc=@seinlet.com>
<[email protected]>
On Wednesday, January 21st, 2026 at 17:28, Tom Lane <[email protected]> wrote:
>
>
> Nicolas Seinlet [email protected] writes:
>
> > 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
>
>
> 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
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 quite aggressive. But, to ensure they are decently correct, I'll include a vacuum analyse in my test.
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)
For the tests, the random_page_cost=1.
Let's try with random_page_cost=2
QUERY PLAN
----------
Index Scan using ir_model_data_name_idx2 on ir_model_data (cost=0.42..4.44 rows=1 width=17) (actual time=106.136..191.606 rows=1 loops=1)
Index Cond: ((name)::text = 'public_user'::text)
Rows Removed by Index Recheck: 10
Filter: ((module)::text = '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=0.42..4.44 rows=1 width=17) (actual time=104.866..189.119 rows=1 loops=1)
Index Cond: ((name)::text = 'public_user'::text)
Rows Removed by Index Recheck: 10
Filter: ((module)::text = 'base'::text)
Planning Time: 0.352 ms
Execution Time: 189.134 ms
(6 rows)
Thanks for all,
Nicolas.
view thread (3+ messages) latest in thread
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]
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
In-Reply-To: <RF0kFq4YRN25ujnOGY1aDWwq2MseT-QQAhpF8XphV5ZAPmGbjicJ_oWcUN0N9lBYOe6lry7qPMdCEMTZ6b-D0GUk5bkrdQFbdbSktjUfVVk=@seinlet.com>
* 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