From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tilo Buschmann <mailinglist.postgresql.performance@b-n-w.org>
Cc: Arjen van der Meijden <acmmailing@tweakers.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: fast DISTINCT or EXIST
Date: Sat, 07 Apr 2007 12:39:38 -0400
Message-ID: <9915.1175963978@sss.pgh.pa.us> (raw)
In-Reply-To: <20070407182407.297e7e35@wonderland>
References: <20070407124713.1d4efe63@wonderland>
<46178F65.5090807@tweakers.net>
<9293.1175961248@sss.pgh.pa.us>
<20070407182407.297e7e35@wonderland>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mailinglist.postgresql.performance@b-n-w.org, acmmailing@tweakers.net
Subject: Re: fast DISTINCT or EXIST
In-Reply-To: <9915.1175963978@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox