Re: Behavior of a pg_trgm index for 2 (or < 3) character LIKE queries

On Sat, Jun 1, 2013 at 1:48 AM, Sawada Masahiko  wrote:
> On Fri, May 31, 2013 at 11:16 AM, Amit Langote  wrote:
>> 2) And if that is so, is there problem in gin_extract_query_trgm(),
>> that is while generating trigrams from a query search term that causes
>> trigrams (stored in the index if answer to (1) is yes) NOT to be used
>> in such a partial matching case?
>
> it means that we can't use trigrams in case of partial matching
> because trigrams (stored in index) are converted to different
> value(CRC).
> right?
>

When I debugged a partial match case such as  " column like '%st%'
", it appears that get_wildcard_trigrams return no trigrams for
wildcard part 'st' since charlen < 3. Hence, GIN_SEARCH_MODE_ALL mode
is used and results in  full index scan instead of trigrams being
used. This happens for multibyte case too. The problem is that for
wildcard part consisting of less than 3 characters,
get_wildcard_trigrams return nothing.


--
Amit Langote