From: Mike Winter <mike.winter@frontlogic.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Efficiency Issue with "SELECT IN (..)" and "SELECT .. OR
Date: Fri, 16 May 2003 13:28:31 -0600 (CST)
Message-ID: <Pine.LNX.4.33L2.0305161307330.19426-100000@frontlogic.com> (raw)
In-Reply-To: <200305161829.09631.dev@archonet.com>
On Fri, 16 May 2003, Richard Huxton wrote:
> > My question is, does anyone have any alternate ideas for how I
> > can do a query like this and have it perform well? The tables I
> > am working with are big enough that a sequential scan is not
> > helpful. Is this a bug I am encountering or an error in my
> > query? Is this a known issue?
>
> Known issue - the usual advice is to rewrite in the form of EXISTS, but I
> can't think how to do that if you have a long list of literal values. You
> could create a temp table to hold your matching values and join against it,
> but I realise that's not a terribly elegant solution. Unless of course, it's
> a search-engine type of situation where it makes a certain amount of sense.
Thanks to everyone for the replies.
The temporary table route is something we did. It's an acceptable
stopgap, but it's still not outstanding in terms of performance
and has lead to other issues. I will examine the 'EXISTS' clause
option depending on the progress of 7.4.
> > I have seen this beahaviour on 7.2.1 and 7.3.2 on both Solaris
> > and Linux platforms.
>
> Supposed to be some improvements in the forthcoming 7.4 but I don't know if
> that will help your particular case.
That's good news.
--
_______________________________________________________________________
Front Logic Inc. Tel: 306.653.2725 x14
226 Pacific Ave or 1.800.521.4510 x14
Saskatoon, SK Fax: 306.653.0972
S7K 1N9 Canada Cell: 306.717.3746
http://www.frontlogic.com mike.winter@frontlogic.com
Find out what TYPENGO(tm) N300 Search Technology can do for your
company: http://www.frontlogic.com/interactive/hosts/typengo/index.html
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-sql@postgresql.org
Cc: mike.winter@frontlogic.com, dev@archonet.com
Subject: Re: Efficiency Issue with "SELECT IN (..)" and "SELECT .. OR
In-Reply-To: <Pine.LNX.4.33L2.0305161307330.19426-100000@frontlogic.com>
* 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