Thank you all. Both the double index & pg_trgm would be good solutions. On Oct 14, 2013, at 3:40 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > 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 > -- Scott Ribe scott_ribe@xxxxxxxxxxxxxxxx http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
