From: Christian Kratzer <ck-lists@cksoft.de>
To: PFC <lists@peufeu.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: Big IN() clauses etc : feature proposal
Date: Tue, 9 May 2006 11:01:00 +0200 (CEST)
Message-ID: <20060509105214.P90693@vesihiisi.cksoft.de> (raw)
In-Reply-To: <op.s89zl30wcigqcu@apollo13>
References: <445F7FF8.6070707@comcast.net>
<1147110142.32369.7.camel@archimedes>
<20060508175013.GY99570@pervasive.com>
<28288.1147111196@sss.pgh.pa.us>
<op.s89zl30wcigqcu@apollo13>
Hi,
On Tue, 9 May 2006, PFC wrote:
<snipp/>
> Back to the point : I can't use the temp table method, because temp
> tables are too slow.
> Creating a temp table, filling it, analyzing it and then dropping it
> takes about 100 ms. The search query, on average, takes 10 ms.
just some thoughts:
You might consider just selecting your primary key or a set of
primary keys to involved relations in your search query. If you
currently use "select *" this can make your result set very large.
Copying all the result set to the temp. costs you additional IO
that you propably dont need.
Also you might try:
SELECT * FROM somewhere JOIN result USING (id)
Instead of:
SELECT * FROM somewhere WHERE id IN (SELECT id FROM result)
Joins should be a lot faster than large IN clauses.
Here it will also help if result only contains the primary keys
and not all the other data. The join will be much faster.
On the other hand if your search query runs in 10ms it seems to be fast
enough for you to run it multiple times. Theres propably no point in
optimizing anything in such case.
Greetings
Christian
--
Christian Kratzer ck@cksoft.de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
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: ck-lists@cksoft.de, lists@peufeu.com, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: Big IN() clauses etc : feature proposal
In-Reply-To: <20060509105214.P90693@vesihiisi.cksoft.de>
* 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