Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 66AE99FB3F0 for ; Sun, 25 Feb 2007 15:39:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 69670-03 for ; Sun, 25 Feb 2007 15:39:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from furbychan.cocan.org (furbychan.cocan.org [80.68.91.176]) by postgresql.org (Postfix) with ESMTP id AD6409FB3ED for ; Sun, 25 Feb 2007 15:39:25 -0400 (AST) Received: from rich by furbychan.cocan.org with local (Exim 3.35 #1 (Debian)) id 1HLPD7-0004ZW-00; Sun, 25 Feb 2007 19:39:05 +0000 Date: Sun, 25 Feb 2007 19:39:05 +0000 To: Tom Lane Cc: Joe Conway , pgsql-sql@postgresql.org Subject: Re: Very large IN-clause is slow, but how to rewrite it? Message-ID: <20070225193905.GD16669@furbychan.cocan.org> References: <20070225123913.GA17124@furbychan.cocan.org> <7794.1172422591@sss.pgh.pa.us> <45E1D4FC.4050901@joeconway.com> <9473.1172428484@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9473.1172428484@sss.pgh.pa.us> User-Agent: Mutt/1.5.9i From: Richard Jones X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.028 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/257 X-Sequence-Number: 27591 On Sun, Feb 25, 2007 at 01:34:44PM -0500, Tom Lane wrote: > Joe Conway writes: > > If 8.2, what about > > ... WHERE foo IN (select x from (values (1$),(2$),...,(N$)) as t(x)) > > ? > > Well, the OP wasn't using 8.2 --- judging from the selected plan, it had > to be 8.0 or older. But yeah, a values-list is an interesting > alternative on 8.2. I think actually you don't need all that much extra > notation; this seems to work: > > WHERE foo IN (VALUES ($1),($2),($3),...) That's right, it is in fact PG 7.4. I will try the values suggestion to see if that makes a difference. Rich.