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