Thank you, Andrew for the response and input. I will proceed as you suggested.
Thanks, Vaibhav On Sat, Oct 3, 2026 at 7:37 PM Andrew Dunstan <[email protected]> wrote: > > On 2026-09-02 We 9:29 AM, Andrew Dunstan wrote: > > > > On 2026-09-02 We 9:00 AM, Alexander Korotkov wrote: > >> Hi, Vaibhav! > >> > >> On Wed, Sep 2, 2026 at 2:12 PM Vaibhav Dalvi > >> <[email protected]> wrote: > >>> I would like to raise an old issue again. David Rowley reported > >>> this same problem back on 2024-09-17, in this[1] thread, but it did not > >>> get any replies. > >>> > >>> I am hitting the same problem, so I am posting it again with three > >>> different patches, since it can badly hurt anyone who has both a > >>> btree and a gist_trgm_ops index on the same column. > >>> > >>> In short: once a table has both a btree index and a gist_trgm_ops > >>> index on the same text column, the planner sometimes picks the GiST > >>> index for a plain equality (=) query, and that GiST plan can be > >>> hundreds of times slower than the btree plan for the exact same > >>> query. Below is a small, self-contained test case: > >>> > >>> create extension if not exists pg_trgm; > >>> create table t1 (a varchar(250), b varchar(250), c varchar(250)); > >>> create index t1_a_btree on t1 (a); > >>> create index t1_a_gist on t1 using gist (a gist_trgm_ops); > >>> insert into t1 select md5(a::text),md5(a::text),md5(a::text) from > >>> generate_series(1,100000)a; > >>> vacuum freeze analyze t1; > >>> > >>> explain (analyze, buffers) select * from t1 where a = '1234'; > >>> QUERY PLAN > >>> > -------------------------------------------------------------------------------------------------------------------- > > >>> > >>> Index Scan using t1_a_gist on t1 (cost=0.28..8.30 rows=1 > >>> width=99) (actual time=15.186..15.187 rows=0.00 loops=1) > >>> Index Cond: ((a)::text = '1234'::text) > >>> Buffers: shared hit=1583 > >>> Execution Time: 15.242 ms > >>> (4 rows) > >>> > >>> -- disabling the GiST index makes the planner fall back to btree, > >>> -- and the same query becomes ~355x faster: > >>> update pg_index set indisvalid = false where > >>> indexrelid='t1_a_gist'::regclass; > >>> explain (analyze, buffers) select * from t1 where a = '1234'; > >>> QUERY PLAN > >>> > ------------------------------------------------------------------------------------------------------------------- > > >>> > >>> Index Scan using t1_a_btree on t1 (cost=0.42..8.44 rows=1 > >>> width=99) (actual time=0.022..0.022 rows=0.00 loops=1) > >>> Index Cond: ((a)::text = '1234'::text) > >>> Buffers: shared hit=3 > >>> Execution Time: 0.043 ms > >>> (4 rows) > >>> > >>> The estimated cost of both plans is almost the same (8.30 vs 8.44), > >>> but the real cost is not even close. The root cause: GiST's cost > >>> estimate for '=' on gist_trgm_ops does not reflect the real cost of > >>> the scan, so the planner can pick GiST over a much cheaper btree > >>> index, even for people who kept both indexes only for other reasons > >>> (LIKE queries, for example). > >>> > >>> I looked at three different ways to fix this: > >>> > >>> 1) v1-0001-Drop-equality-operator-from-gist_trgm_ops.patch > >>> Stops gist_trgm_ops from offering '=' at all (gin_trgm_ops is > >>> untouched, its cost estimator was already fixed for the same issue > >>> in commit cd9479af2af). Small, but it changes the existing > >>> opclass's behavior for anyone already relying on '=' through it. > >>> > >>> 2) v1-0001-gist-trgm-real-signature-stats.patch > >>> Keeps '=' on gist_trgm_ops, and adds a new optional GiST support > >>> function so the opclass can correct the planner's estimate using a > >>> real measurement sampled from the index's own pages, instead of a > >>> guess. It works, but it only helps "column = constant" conditions > >>> (not joins), a small index still falls back to a pessimistic > >>> guess, and it adds uncached I/O to every planning call. More > >>> machinery than I'm comfortable with for this. > >>> > >>> 3) v1-0001-Add-gist_trgm_ops_noeq.patch > >>> Adds a second, independently-named opclass, gist_trgm_ops_noeq, > >>> identical to gist_trgm_ops except that it does not register '=' > >>> at all. gist_trgm_ops itself is completely untouched; anyone who > >>> wants the safety creates new indexes with the new opclass, or > >>> rebuilds an existing index onto it, and '=' simply cannot reach it > >>> afterward, under any planner settings. No core or planner changes > >>> at all, just a SQL/DDL addition. > >>> > >>> Of the three, (3) is the one I would prefer to take forward. It is > >>> the smallest, safest change: nothing existing changes behavior, there > >>> is no heuristic or cost-model logic to get wrong, and it is easy to > >>> verify its correctness just by looking at the catalog entries. (1) > >>> fixes the > >>> regression but forces the choice on everyone using gist_trgm_ops for > >>> '=' today, and (2) is real but has enough rough edges (documented in > >>> that patch) that I would not want to see it committed as-is. > >>> > >>> Would appreciate the community's view on this, or any other approach > >>> I may have missed. > >>> > >>> Thank you, @Andrew Dunstan for the offline inputs. > >> I think 2 looks like the right direction. But I wonder about the cost > >> of the cost estimate. Scanning 30 random index pages could be easily > >> way more costly than the query execution. What about letting GiST > >> opclass store custom statistics which would reflect index quality > >> (like key overlapping degree per page level or something), then use it > >> for fast scan cost estimation? > >> > > > > Yes, and I think v2 has serious limitations in any case. The best way > > forward might be for us to have a new AM hook that lets an opclass > > walk its own pages during ANALYZE (bounded, since ANALYZE is already > > expected to do I/O and is rate-limited by autovacuum, unlike planning) > > and persist something like your per-level overlap measure through that > > same path. > > > > But that's a large project. > > > > Meanwhile users encountering the problem can create the new > > opfamily/opclass for themselves as in patch 3, and the recreate their > > indexes using the new opclass. Or we could adopt patch 3 of we decide > > not to go for a more thorough solution. > > > > > > > > In the absence of further discussion, I suggest Vaibhav creates a CF > entry for item 3. It will cost almost nothing and provide users a > reasonable way forward. > > > cheers > > > andrew > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com > >
