Re: fast DISTINCT or EXIST

From: Arjen van der Meijden
Subject: Re: fast DISTINCT or EXIST
Date: ,
Msg-id: 4617D4D4.2080509@tweakers.net
(view: Whole thread, Raw)
In response to: Re: fast DISTINCT or EXIST  (Tilo Buschmann)
List: pgsql-performance

Tree view

fast DISTINCT or EXIST  (Tilo Buschmann, )
 Re: fast DISTINCT or EXIST  (Arjen van der Meijden, )
  Re: fast DISTINCT or EXIST  (Tom Lane, )
   Re: fast DISTINCT or EXIST  (Tilo Buschmann, )
    Re: fast DISTINCT or EXIST  (Tom Lane, )
    Re: fast DISTINCT or EXIST  (Arjen van der Meijden, )

On 7-4-2007 18:24 Tilo Buschmann wrote:
> Unfortunately, the query above will definitely not work correctly, if
> someone searches for "a" or "the".

That are two words you may want to consider not searching on at all.

As Tom said, its not very likely to be fixed in PostgreSQL. But you can
always consider using application logic (or a pgpsql function, you could
even use a set returning function to replace the double-limit subselects
in your in-statement) which will automatically fetch more records when
the initial guess turns out to be wrong, obviously using something like
a NOT IN to remove the initially returned cd.id's for the next batches.
Then again, even 'a' or 'the' will not likely be in *all* tracks of a
cd, so you can also use the 'average amount of tracks per cd' (about 10
or 11?) as your multiplier rather than my initial 3. Obviously you'll
loose performance with each increment of that value.

Best regards,

Arjen


pgsql-performance by date:

From: david@lang.hm
Date:
Subject: Re: SCSI vs SATA
From: Ron
Date:
Subject: Re: SCSI vs SATA