PostgreSQL LIKE query performance variations

Question

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 these help with like queries. Anyone have any ideas?

Sample SQL: select gid, owner1 from parcels where owner1 ILIKE '%someones name%' limit 10

Have 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

Answer 1

Here are some interesting things on the LIKE in SQL:

Answer 2

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.

Answer 3

The thing with a like '%something%' expression is that the only index that will help you is a full text index. So if you want consistent timing on your queries, make sure you have a FULL TEXT INDEX on that column.

What might be happing is the following. Suppose you have the following table:

create table foo (
  my_text_field varchar(2048)
);
crete index foo_text_idx on foo (my_text_field);

And consider the following two queries:

1. select * from foo where my_text_field like 'something%';
2. select * from foo where my_text_field like '%something%';

These seem similar queries, but execution plan might be very different, since in Query #1, database engine will be able to use *foo_text_idx* index while in Query #2, database engine will inevitable do a full table scan.

Hope it helps.

Answer 4

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.

Answer 5

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.