Re: like & optimization

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