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
