To: Tilo Buschmann <mailinglist.postgresql.performance@b-n-w.org>
cc: Arjen van der Meijden <acmmailing@tweakers.net>,
	PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: fast DISTINCT or EXIST 
In-reply-to: <20070407182407.297e7e35@wonderland> 
References: <20070407124713.1d4efe63@wonderland>
	<46178F65.5090807@tweakers.net> <9293.1175961248@sss.pgh.pa.us>
	<20070407182407.297e7e35@wonderland>
Comments: In-reply-to Tilo Buschmann
	<mailinglist.postgresql.performance@b-n-w.org>
	message dated "Sat, 07 Apr 2007 18:24:07 +0200"
Date: Sat, 07 Apr 2007 12:39:38 -0400
Message-ID: <9915.1175963978@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Tilo Buschmann <mailinglist.postgresql.performance@b-n-w.org> writes:
>> Arjen van der Meijden <acmmailing@tweakers.net> writes:
>>> SELECT ... FROM cd
>>> JOIN tracks ...
>>> WHERE cd.id IN (SELECT DISTINCT cd_id FROM (SELECT t.cd_id FROM tracks t
>>> WHERE t.tstitle @@ plainto_tsquery('simple','education') LIMIT 30) 
>>> as foo LIMIT 10)

> Unfortunately, the query above will definitely not work correctly, if
> someone searches for "a" or "the". 

Well, the "incorrectness" is only that it might deliver fewer than the
hoped-for ten CDs ... but that was a completely arbitrary cutoff anyway,
no?  I think in practice this'd give perfectly acceptable results.

> Actually, I hoped to find an alternative, that does not involve
> DISTINCT.

You could try playing around with GROUP BY rather than DISTINCT; those
are separate code paths and will probably give you different plans.
But I don't think you'll find that GROUP BY does any better on this
particular measure of yielding rows before the full input has been
scanned.

			regards, tom lane