CODEWITHSUNDEEP
postgresql
vobet
vobet

Reputation: 75

Postgresql - multicolumn gin index using OR / AND

I'm running the following query on a 'companies' table:

SELECT *
  FROM companies
  WHERE name ILIKE '%foo%'
    AND (city ILIKE 'bar%' OR state ILIKE 'bar%')

I'm trying to use gin indexes, and I've added an indidivual index for name, city and state:

CREATE INDEX trgm_idx_companies_name ON companies USING gin (name gin_trgm_ops);
CREATE INDEX trgm_idx_companies_city ON companies USING gin (city gin_trgm_ops);
CREATE INDEX trgm_idx_companies_state ON companies USING gin (state gin_trgm_ops);

However, only the name index is used when running the query. I suspect the answer lies in a multi-column index but I can't get it to work no matter what variation I try.

Upvotes: 1

Views: 106

Answers (1)

Hambone
Hambone

Reputation: 16377

My guess is that "Name" is far more exclusive than City or State. Presumably you have the same cities and states repeated very frequently within the table, but the names don't repeat that often.

For example, if these were hospitals, it would not be out of the question for two hospitals in completely different cities and states to have the same name "Memorial Hospital," but it would be far less frequent than two hospitals having the same city and state, which happens all the time (Houston, TX has a hundred hospitals).

As a result, the analyzer is probably realizing that searching "Memorial Hospital" may return X results, X is a very small number, and it's quicker to just scan those X results than to try to use the index. Why index a table with five records? That sort of thing.

As a test case, try this:

SELECT *
FROM companies
WHERE  (city ILIKE 'bar%' OR state ILIKE 'bar%')

My guess is you will find the query is using both indexes and a bitmap or. That should give you some confidence that the indexes are working -- they're just not necessary for your query.

Upvotes: 2

Related Questions