X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D1533476967 for ; Fri, 16 May 2003 15:17:59 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 33542-08 for ; Fri, 16 May 2003 15:17:58 -0400 (EDT) Received: from frontlogic.com (gw.frontlogic.com [207.195.92.149]) by postgresql.org (Postfix) with SMTP id EC014476835 for ; Fri, 16 May 2003 15:17:57 -0400 (EDT) Received: (qmail 11979 invoked by uid 614); 16 May 2003 19:28:31 -0000 Received: from localhost (sendmail-bs@127.0.0.1) by localhost with SMTP; 16 May 2003 19:28:31 -0000 Date: Fri, 16 May 2003 13:28:31 -0600 (CST) From: Mike Winter X-X-Sender: To: Richard Huxton Cc: Subject: Re: Efficiency Issue with "SELECT IN (..)" and "SELECT .. OR In-Reply-To: <200305161829.09631.dev@archonet.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/355 X-Sequence-Number: 13520 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