Re: like & optimization

Thank you all. Both the double index & pg_trgm would be good solutions.

On Oct 14, 2013, at 3:40 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Sat, Oct 12, 2013 at 4:28 PM, Torsten Förtsch
> <torsten(dot)foertsch(at)gmx(dot)net> wrote:
>> On 12/10/13 20:08, Scott Ribe wrote:
>>> select * from test where tz >= start and tz < end and colb like '%foobar%'
>>
>> I think you can use an index only for wildcard expressions that are
>> anchored at the beginning. So,
>>
>> select * from test where tz >= start and tz < end
>> and colb like 'foobar%'
>>
>> can use an index on colb.
>>
>> You could perhaps
>>
>> select * from test where tz >= start and tz < end
>> and colb like 'foobar%'
>> union all
>> select * from test where tz >= start and tz < end
>> and reverse(colb) like 'raboof%'
>>
>> Then you need 2 indexes, one on colb the other on reverse(colb).
>>
>> You can have duplicates in the result set if the table contains rows
>> where colb='foobar'. If that's a problem, use union distinct.
>>
>> Alternatively, if foobar is kind of a word (with boundaries), you could
>> consider full-text search.
>
> pg_trgm module optimizes 'like with wildcards' without those
> restrictions. It's very fast for what it does. Because of the
> GIST/GIN dependency index only scans are not going to be used through
> pg_tgrm though.
>
> merlin
>

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice