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

Arjen van der Meijden <acmmailing@tweakers.net> writes:
> If that is your main culprit, you could also use two limits based on the 
> fact that there will be at most X songs per cd which would match your 
> title (my not very educated guess is 3x). Its a bit ugly... but if that 
> is what it takes to make postgresql not scan your entire index, so be it...

> 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)

I think that's the only way.  There is no plan type in Postgres that
will generate unique-ified output without scanning the whole input
first, except for Uniq on pre-sorted input, which we can't use here
because the tsearch scan isn't going to deliver the rows in cd_id order.

I can see how to build one: make a variant of HashAggregate that returns
each input row immediately after hashing it, *if* it isn't a duplicate
of one already in the hash table.  But it'd be a lot of work for what
seems a rather specialized need.

			regards, tom lane