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)

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 :) — John Bachir, Oct 04 '14 at 16:26
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. http://www.postgresql.org/docs/8.3/static/indexes-bitmap-scans.html — Macario, Oct 04 '14 at 18:22
@Macario cool, good to know. yep, we do have both indexes in place. — John Bachir, Oct 05 '14 at 02:04

score 9 · Accepted Answer · answered Apr 02 '15 at 13:41

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.

n.b. there is also btree_gin: http://www.postgresql.org/docs/9.4/static/btree-gin.html — John Bachir, Oct 09 '15 at 19:11

score 0 · Answer 2 · answered Feb 05 '14 at 22:03

0

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.

answered Feb 05 '14 at 22:03

Gordon Linoff

a trigram or other strategy is necessary in order to partial matches inside of words, such as searching for "over" and matching "stackoverflow" – John Bachir Feb 05 '14 at 22:27
@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*? – Gordon Linoff Feb 05 '14 at 22:30
it's not fulltext, it's pg_trgm -- i've updated my question to clarify. – John Bachir Feb 06 '14 at 00:24
@JohnBachir . . . And for the queries in the question, a b-tree index should work just as well. – Gordon Linoff Feb 06 '14 at 01:14

2 Answers2