On Sat, Oct 12, 2013 at 4:28 PM, Torsten Förtsch <torsten.foertsch@xxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
