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

Hi Andrew,

The same patch is applicable.
I am re-attaching it for review and for the CF bot to pick up.

Regards,
Vaibhav

On Tue, Oct 6, 2026 at 9:49 AM Vaibhav Dalvi <vaibhav(dot)dalvi(at)enterprisedb(dot)com>
wrote:

> 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 <andrew(at)dunslane(dot)net> 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
>> >> <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.
>> >
>> >
>> >
>>
>> 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
>>
>>