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)
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.