3 ms·

But pg_trgm isn't really the same either, is it? I have looked at pg_trgm primarily for handling misspellings and suggested alternatives. Also it wasn't clear

• jeltz 14y ago
  Yes, pg_trgm was built for that but someone figured out how LIKE could be hacked to use the trigram indexes (gist_trgm_ops, gin_trgm_ops). So if you have a * _trgm_ops index on the column normal LIKE and ILIKE queries may use that index. I assume your example would be a false index hit which then is necessary to verify against the real value. The same would apply to make sure 'This' is not a hit when doing a case sensitive search for '%this%'. So index-only LIKE scans are not possible with * _trgm_ops indexes. EDIT: I just realized how awesome the extensibility of PostgreSQL is. An extension can make a core operator such as LIKE indexable in an entirely new way without touching the core code.
  • einhverfr 14y ago
    The extensibility is really cool. And yeah, that looks like a very useful thing. Thanks for the pointer.