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 <[email protected]> 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 <[email protected]> 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 >> >> <[email protected]> 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 >> >>
v1-0001-Add-gist_trgm_ops_noeq.patch
Description: Binary data
