CODEWITHSUNDEEP
sqlpostgresqlfull-text-searchtrigram
John Bachir
John Bachir

Reputation: 22721

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

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)

Upvotes: 9

Views: 5922

Answers (3)

Amlan S Warman
Amlan S Warman

Reputation: 11

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%');

Upvotes: 1

pidupuis
pidupuis

Reputation: 363

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.

Upvotes: 10

Gordon Linoff
Gordon Linoff

Reputation: 1269743

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.

Upvotes: 1

Related Questions