Re: Re: External search engine, advice

mlw wrote:
>
> Tom Lane wrote:
> >
> > mlw <markw(at)mohawksoft(dot)com> writes:
> > > freedb=# select * from cdsongs where songid = ftss_results() ;
> > > ERROR: Set-valued function called in context that cannot accept a set
> >
> > '=' is a scalar operation. Try
> >
> > select * from cdsongs where songid IN (select ftss_results());
>
> I was afraid you'd say that. That does not use indexes.
>
> It is pointless to use a text search engine if the result has to perform a
> table scan anyway.
>
> If I do:
>
> create temp table fubar as select ftss_results() as songid;
> select * from cdsongs where songid = fubar.songid;
>
> That works, but that is slow and a lot of people have emotional difficulties
> with using temporary tables. (Oracle syndrome) Also, an 'IN' clause does not
> preserve the order of the results, where as a join should.

So the standard answer to "IN doesn't use indexes" is to use EXISTS instead. I'm
surely being hopelessly naive here, but why won't that work in this case?

Regards,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(21)635-694, Fax: +64(4)499-5596, Office: +64(4)499-2267xtn709