On Thursday, January 22nd, 2026 at 13:11, Nicolas Seinlet <[email protected]> wrote:
> > > 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. 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)
