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)

Reductase

Could you figure it out? – Claudette

I'm pretty sure it's not possible. But my question didn't get much attention. If you vote it up maybe it will get more :) – Reductase

I could get my query optimized by using two indices. In your case it would be an index on name and another one on type. postgresql.org/docs/8.3/static/indexes-bitmap-scans.html – Claudette

@Claudette cool, good to know. yep, we do have both indexes in place. – Reductase

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.

Bidle

n.b. there is also btree_gin: postgresql.org/docs/9.4/static/btree-gin.html – Reductase

Answer 2

Use a composite index:

CREATE INDEX ON foos(name, type)

However, you might want:

CREATE INDEX ON foos(lower(name), type)

I don't see why a full text index is needed for your queries.

Nez

a trigram or other strategy is necessary in order to partial matches inside of words, such as searching for "over" and matching "stackoverflow" – Reductase

@JohnBachir . . . The OP is not using full text search functions in the question. Are full text indexes going to be any better than a regular index for the queries in the question? – Nez

it's not fulltext, it's pg_trgm -- i've updated my question to clarify. – Reductase

@JohnBachir . . . And for the queries in the question, a b-tree index should work just as well. – Nez

Answer 3

Other solution with pg_trgm, just concat all text:

CREATE INDEX idx_foo_name ON foo USING gin (UPPER(name||type) gin_trgm_ops);

and to query:

SELECT * FROM foo WHERE UPPER(name||type) LIKE UPPER('%bar%');

Ingenuity