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

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.dalvi@enterprisedb.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@dunslane.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.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.
>
>
>

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