Re: Simplifying Text Search

Trevor Talbot wrote:
> On 11/13/07, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>
>> Am Dienstag, 13. November 2007 schrieb Gregory Stark:
>>
>>> "Peter Eisentraut" <peter_e(at)gmx(dot)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.
>
>
>

The fact that we are having this discussion at all demonstrates to me
that we should leave well alone - any use of LIKE in this context is
just about guaranteed to cause massive confusion. (Not to mention that
it's far too late in the dev cycle to be making such changes, if we're
thinking of them for 8.3).

cheers

andrew