Re: like & optimization

On Sat, Oct 12, 2013 at 4:28 PM, Torsten Förtsch
<torsten.foertsch@gmx.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