Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 seconds to return results.

I understand that LIKE queries are very resource intensive but I just don't understand why there would be such a large difference in response times. I have built a btree index on the owner1 field but I don't think it helps with LIKE queries. Anyone have any ideas?

Sample SQL:

SELECT gid, owner1 FORM parcels
WHERE owner1 ILIKE '%someones name%' LIMIT 10

I've also tried:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10

And:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10

With similar results.
Table Row Count: about 95,000.

share|improve this question

4 Answers 4

The previously accepted answer was incorrect.

Full Text Search with its full text indexes are not for the LIKE operator at all, they have their own operators and don't work for arbitrary strings. They operate on words based on dictionaries and stemming.

Install the additional module pg_trgm which provides operator classes for GIN or GiST indexes to support (almost) all LIKE patterns, not just left-anchored ones:

Example index:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

Example query:

SELECT * FROM tbl WHERE col LIKE '%foo%';  -- wild card at beginning

For just left-anchored patterns you get the optimum with a suitable operator class for a B-tree index: text_pattern_ops or varchar_pattern_ops (part of standard Postgres, no additional module needed).

Example index:

CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);

Example query:

SELECT * FROM tbl WHERE col LIKE 'foo%';  -- no wild card at beginning

More details, explanation, examples and links in these related answers on dba.SE:

share|improve this answer

You could install Wildspeed, a different type of index in PostgreSQL. Wildspeed does work with %word% wildcards, no problem. The downside is the size of the index, this can be large, very large.

share|improve this answer

Possibly the fast ones are anchored patterns with case-sensitive like that can use indexes. i.e. there is no wild card at the beginning of the match string so the executor can use an index range scan. (the relevant comment in the docs is here) Lower and ilike will also lose your ability to use the index unless you specifically create an index for that purpose (see functional indexes).

If you want to search for string in the middle of the field, you should look into full text or trigram indexes. First of them is in Postgres core, the other is available in the contrib modules.

share|improve this answer
    
I hadn't thought about creating an index on the lowercase value of the field. That way I can convert the query text to lowercase on the backend before querying. –  Jason Oct 14 '09 at 15:03

Your like queries probably cannot use the indexes you created because:

1) your LIKE criteria begins with a wildcard.

2) you've used a function with your LIKE criteria.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.