multi-column index for string match + string similarity with pg_trgm?
full-text-search, postgresql, sql, trigram
Solution
You can use a multicolumn index combining different types.
First, add the two extensions required in your case:
CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gist;
pg_trgm allows you to use trigram indexes and btree_gist allows you to combine `gist` and `b-tree` indexes, which is what you want!
For a query like:
SELECT * FROM foo WHERE type = 'baz' AND name ilike '%bar%';
You can now create an index like:
CREATE INDEX ON foo USING gist (type, name gist_trgm_ops);
As usual, the order of columns has to be the same between the query and the index.
Problem
Given this table: ``` foos integer id string name string type ``` And a query like this: ``` select * from foos where name ilike '%bar%' ``` I can make a pg_trgm index like this to make lookups faster: ``` CREATE INDEX ON foos USING gin (name gin_trgm_ops) ``` (right?) my question: what about a query like this: ``` select * from foos where name ilike '%bar%' AND type = 'baz' ``` Can I possibly make an index that will help the lookup of both columns? (I know that trigram isn't strictly fulltext but I'm tagging this question as such anyway)