Message-ID: <4617D4D4.2080509@tweakers.net>
Date: Sat, 07 Apr 2007 19:28:52 +0200
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Thunderbird 2.0b2 (Windows/20070116)
MIME-Version: 1.0
To: Tilo Buschmann <mailinglist.postgresql.performance@b-n-w.org>
CC: Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: fast DISTINCT or EXIST
References: <20070407124713.1d4efe63@wonderland>
	<46178F65.5090807@tweakers.net>	<9293.1175961248@sss.pgh.pa.us>
	<20070407182407.297e7e35@wonderland>
In-Reply-To: <20070407182407.297e7e35@wonderland>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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