Re: gist_trgm_ops '=' operator: planner picks it over btree, ~300x slower

Thanks Alexander, Andrew,

Sounds like there's rough agreement that (2) has enough open
questions: the cost of the sampling itself, and the fact that it only
helps column = constant, not joins — that it's not something to
commit as-is, and that the real long-term fix is closer to what
Andrew described: an AM hook letting an opclass gather its own
per-page statistics at ANALYZE time. I agree that's the right
direction, but as noted it's a large project (new AM API, catalog
storage, ANALYZE integration), so I don't think it should block a
fix for the regression that's on the table today.

Given that, I'd like to move forward with patch 3
(gist_trgm_ops_noeq) as a committable fix now rather than just as
something people hand-roll for themselves:

- It changes nothing about gist_trgm_ops's existing behavior.
- It requires no planner/cost-model changes and no new stats
machinery — the new opclass simply never registers strategy 11, so
= can never reach it, under any settings.
- It's easy to verify correctness by inspection of the catalog
entries alone.
- It gives anyone hitting this today (via REINDEX onto the new
opclass) an immediate, permanent fix without waiting on the bigger
project or introducing any interim cost-model heuristics that might
need to be revisited later.

Andrew, you mentioned "we could adopt patch 3 if we decide not to go
for a more thorough solution" — given the scope of the thorough
solution, I'd like to propose we do exactly that: commit patch 3 now
as the practical fix, and track the ANALYZE/AM-hook idea as a
separate, independent piece of future work rather than a prerequisite.

Does that match your thinking, or is there a reason to hold off on
committing (3) until the larger design is settled? Happy to rebase/
update the patch based on any review feedback.

Regards,
Vaibhav

On Thu, Sep 3, 2026 at 11:56 AM Alexander Korotkov <aekorotkov(at)gmail(dot)com>
wrote:

> On Wed, Sep 2, 2026 at 4:30 PM Andrew Dunstan <andrew(at)dunslane(dot)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(dot)dalvi(at)enterprisedb(dot)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
>