Re: pg_trgm upgrade to 1.6 led to load average increase - Mailing list pgsql-general

On Thursday, January 22nd, 2026 at 13:11, Nicolas Seinlet <nicolas@seinlet.com> wrote:

>
>
> On Wednesday, January 21st, 2026 at 17:28, Tom Lane tgl@sss.pgh.pa.us wrote:
>
> > Nicolas Seinlet nicolas@seinlet.com 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
analysetrigger parameters are quite aggressive. But, to ensure they are decently correct, I'll include a vacuum analyse
inmy 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.028rows=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.983rows=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.606rows=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.119rows=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.

I've also tried to put the random_page_cost to a huge value, and it does not change anything in index selection,
despitethe second cost increase. 
QUERY PLAN
----------
 Index Scan using ir_model_data_name_idx2 on ir_model_data  (cost=0.42..400.44 rows=1 width=17) (actual
time=141.930..256.600rows=1 loops=1) 
   Index Cond: ((name)::text = 'public_user'::text)
   Rows Removed by Index Recheck: 10
   Filter: ((module)::text = 'base'::text)
 Planning Time: 1.588 ms
 Execution Time: 256.640 ms
(6 rows)