Implementing K-Gram indexing in postgres

user_13759580 0
Viewed 32

I am trying to implement an index in postgres for wild card queries such as

SELECT * FROM TABLENAME WHERE COL1 LIKE '<text>%';

I know postgres offers gin and gist trigram indexing through pg_trgm extension. But, it stores the trigrams of the text in the index value. is there any way I can modify the implementation from trigram to digram or n-gram where n can be modified, in postgres ? It would be much preferred if there is a way to do it with GIN indexing. if not, any other alternatives are also much welcomed.

pg-trgm database-indexes indexing postgresql database
1 Answers

As long as the pattern doesn't start with a wild card (which your example suggests is true), trigrams should still work. For example, the pattern 'th%' will generate the bigram-like trigram ' th'.

If your true pattern is such as '%th%', that would generate no trigrams, but auto completion starting into the middle of words generally doesn't make sense so i would reconsider doing it that way.

user_17212390
Related