4 ms·

Actually since PostgreSQL 9.1 LIKE '%this%' is indexable with the pg_trgm contrib module. Since it is based on the trigrams in your search query it obviously ha

by jeltz 14y ago

Actually since PostgreSQL 9.1 LIKE '%this%' is indexable with the pg_trgm contrib module. Since it is based on the trigrams in your search query it obviously has its caveats, generally the longer the query the more effective the index lookup is. I would imagine for example '%th%' requires a full table/index scan since it contains zero trigrams. http://www.postgresql.org/docs/9.1/static/pgtrgm.html#AEN137099 http://www.postgresql.org/docs/9.1/static/pgtrgm.html#AEN137...

einhverfr 14y ago
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 to me how "%this%" would be differentiated from "his thin snake."
- 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.
jeltz 14y ago
Found there is also a third party module, made by the same guys who made LIKE able to use pg_trgm indexes, called wildspeed. http://www.sai.msu.su/~megera/wiki/wildspeed http://www.sai.msu.su/~megera/wiki/wildspeed It may be faster than using pg_trgm but I have no idea if it is still maintained.

by jeltz 14y ago

einhverfr 14y ago

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 to me how "%this%" would be differentiated from "his thin snake."

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.

jeltz 14y ago

Found there is also a third party module, made by the same guys who made LIKE able to use pg_trgm indexes, called wildspeed. http://www.sai.msu.su/~megera/wiki/wildspeed http://www.sai.msu.su/~megera/wiki/wildspeed It may be faster than using pg_trgm but I have no idea if it is still maintained.