Re: BUG #19701: GIN trigram index loses rows at similarity_threshold 0

Hi Manu,

Thanks for your patch, I reviewed your patch and tested it on master
at 4545cee303c with
assertions enabled. All four pg_trgm regression tests pass.
Without the C changes, the new tests fail as expected.

I also compared exact row sets from sequential and GIN/GiST index
scans for all six operator forms at zero and positive thresholds.
These covered ordinary, empty and punctuation-only queries, with
empty strings and NULLs among the stored values.

Without the patch, sequential and index scans returned different
rows only when the relevant similarity threshold was zero.
With the patch, their results matched in every case tested.

I did not find a correctness issue. Could you add coverage for
these cases to the regression tests?

* Insert rows after creating the GIN index, then test an empty
query at threshold zero before and after pending-list cleanup.

* Include strict-word similarity (<<%) at zero, with stored empty
strings and NULLs.

The suggestions are to preserve that coverage in the regression suite.

Thanks,
Palak

On Thu, 24 Sept 2026 at 00:56, Manu <manuelreyesbravo(at)gmail(dot)com> wrote:
>
> Hi,
>
> I can reproduce this on master (374522aa63a), and it is wider than the
> report. With the thresholds at 0, on 5002 rows (100 of them empty
> strings), a sequential scan returns all 5002 rows for each of these
> queries. The indexes return:
>
> - v % 'apple', 'apple' <% v, 'apple' <<% v: GIN 315, GiST 5002
> - v % '', v % '#' (no trigrams): GIN 0, GiST 0
>
> So all three similarity operators are affected in GIN, and GiST is
> affected too, when the query has no trigrams. There are two causes:
>
> 1. gin_extract_query_trgm() returns the query's trigrams, so a GIN scan
> only visits rows that share one of them. At a threshold of 0 every
> row matches, including the ones that share none. The fix asks for
> GIN_SEARCH_MODE_ALL when the threshold is 0, as the function already
> does when the query has no trigrams.
>
> 2. When the query has no trigrams, gin_trgm_consistent() and
> gin_trgm_triconsistent() return false outright, and so does
> gtrgm_consistent() on GiST internal pages. The similarity with
> such a query is 0, which a threshold of 0 accepts. GiST leaf pages
> already get this right, which is why the report's three-row table
> (a single leaf page) shows GiST as correct.
>
> The attached patch fixes both and adds a test to the existing threshold
> test on the restaurants table, for GiST and GIN. Without the C changes
> the new test fails (GiST returns 0 for the empty query, GIN returns
> 10000 and 0 instead of 20000); with them, pg_trgm's tests pass. With
> the default thresholds, all the queries above return the same as
> before.
>
> It applies cleanly to REL_14_STABLE through REL_19_STABLE; I built and
> ran the pg_trgm tests on REL_18_STABLE as well.
>
> Regards,
> Manu