Subject: Re: pg_trgm upgrade to 1.6 led to load average increase

From: Tom Lane <[email-masked]>
Date: Wed, 21 Jan 2026 11:27:56 -0500

Nicolas Seinlet <[email-masked]> 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

---

From: Nicolas Seinlet <[email-masked]>
Date: Thu, 22 Jan 2026 12:10:33 +0000

On Wednesday, January 21st, 2026 at 17:28, Tom Lane <[email-masked]> wrote:

> 
> 
> Nicolas Seinlet [email-masked] writes:
> 
> > We issue queries like :

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.

---

From: Nicolas Seinlet <[email-masked]>
Date: Thu, 22 Jan 2026 13:22:27 +0000

On Thursday, January 22nd, 2026 at 13:11, Nicolas Seinlet <[email-masked]> wrote:

> 
> 
> On Wednesday, January 21st, 2026 at 17:28, Tom Lane [email-masked] wrote:
> 

I've also tried to put the random_page_cost to a huge value, and it does not change anything in index selection, despite the 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.600 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.588 ms
 Execution Time: 256.640 ms
(6 rows)