Re: gist_trgm_ops '=' operator: planner picks it over btree, ~300x slower - Mailing list pgsql-hackers

On Wed, Sep 2, 2026 at 4:30 PM Andrew Dunstan <andrew@dunslane.net> 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
> > <vaibhav.dalvi@enterprisedb.com> 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.

I think that the situation when GiST have similar cost to B-tree for
equality operator is not normal even for current quite generic
estimators.  GiST cost estimator doesn't know about opclass internal
details.  But even in the best possible case, GiST still have to call
consistent function for reach item on the page (unlike B-tree).  So,
GiST cost must be higher.

------
Regards,
Alexander Korotkov
Supabase