Re: Simplifying Text Search

On 11/13/07, Peter Eisentraut <peter_e@gmx.net> wrote:
> Am Dienstag, 13. November 2007 schrieb Gregory Stark:
> > "Peter Eisentraut" <peter_e@gmx.net> writes:

> > > What we'd need is a way to convert a LIKE pattern into a tsquery
> > > ('%foo%bar%' => 'foo & bar').  Then you might even be able to sneak
> > > index-optimized text search into existing applications.  Might be worth a
> > > try.

> > I don't think that's the right direction to go. Notably "%foo%bar%" isn't
> > the same thing as "foo & bar". Also most tsearch queries can't be expressed
> > as LIKE patterns anyways.

> The requirement is to express LIKE patterns as tsearch queries, not the other
> way around.

How?  LIKE queries are incapable of expressing word boundaries, do not
support substitution, and are implicitly ordered.  tsearch queries
operate entirely on word boundaries, may substitute words, and are
unordered.

I don't see the two as even working in the same space, let alone be
convertable for optimization purposes.  If the idea was just to use a
tsearch index as an initial filter, then running LIKE on the results,
dictionary-based substitution makes that unreliable.