From: Tom Lane <tgl@sss.pgh.pa.us>
To: Arjen van der Meijden <acmmailing@tweakers.net>
Cc: Tilo Buschmann <mailinglist.postgresql.performance@b-n-w.org>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: fast DISTINCT or EXIST
Date: Sat, 07 Apr 2007 11:54:08 -0400
Message-ID: <9293.1175961248@sss.pgh.pa.us> (raw)
In-Reply-To: <46178F65.5090807@tweakers.net>
References: <20070407124713.1d4efe63@wonderland>
<46178F65.5090807@tweakers.net>
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
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, acmmailing@tweakers.net, mailinglist.postgresql.performance@b-n-w.org
Subject: Re: fast DISTINCT or EXIST
In-Reply-To: <9293.1175961248@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