multi-column index for string match + string similarity with pg_trgm?

Question

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)

Answer 1

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.