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

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?

------
Regards,
Alexander Korotkov
Supabase

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.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

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

Good morning!

I wanted to make you aware that there is a patchset that you might find interesting for this topic:
/messages/by-id/36b4f67d-5975-452c-a6b8-b6407f0924ee@Spark

Regards

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@gmail.com>
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

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:

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: