public inbox for [email protected]help / color / mirror / Atom feed
Re: pg_trgm upgrade to 1.6 led to load average increase 3+ messages / 2 participants [nested] [flat]
* Re: pg_trgm upgrade to 1.6 led to load average increase @ 2026-01-21 16:27 Tom Lane <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2026-01-21 16:27 UTC (permalink / raw) To: Nicolas Seinlet <[email protected]>; +Cc: [email protected] <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_trgm upgrade to 1.6 led to load average increase @ 2026-01-22 12:10 Nicolas Seinlet <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Nicolas Seinlet @ 2026-01-22 12:10 UTC (permalink / raw) To: [email protected] <[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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_trgm upgrade to 1.6 led to load average increase @ 2026-01-22 13:22 Nicolas Seinlet <[email protected]> parent: Nicolas Seinlet <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Nicolas Seinlet @ 2026-01-22 13:22 UTC (permalink / raw) To: [email protected] <[email protected]> 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) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-01-22 13:22 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-01-21 16:27 Re: pg_trgm upgrade to 1.6 led to load average increase Tom Lane <[email protected]> 2026-01-22 12:10 ` Nicolas Seinlet <[email protected]> 2026-01-22 13:22 ` Nicolas Seinlet <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox