Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 1134E9FB41A for ; Sun, 25 Feb 2007 12:56:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 23911-02 for ; Sun, 25 Feb 2007 12:56:34 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id DD6F49FB419 for ; Sun, 25 Feb 2007 12:56:34 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l1PGuV0g007795; Sun, 25 Feb 2007 11:56:31 -0500 (EST) To: Richard Jones cc: pgsql-sql@postgresql.org Subject: Re: Very large IN-clause is slow, but how to rewrite it? In-reply-to: <20070225123913.GA17124@furbychan.cocan.org> References: <20070225123913.GA17124@furbychan.cocan.org> Comments: In-reply-to Richard Jones message dated "Sun, 25 Feb 2007 12:39:14 +0000" Date: Sun, 25 Feb 2007 11:56:31 -0500 Message-ID: <7794.1172422591@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.055 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/254 X-Sequence-Number: 27588 Richard Jones writes: > I've been profiling a PG database / mix of applications and found that > one statement which takes a very long time to execute is: PG 8.2 does better with long IN-lists ... although if the list is so long as to be fetching a significant fraction of the table, you'll still have problems. In that case I'd advise putting the values into a temp table, ANALYZEing same, and doing "WHERE foo IN (SELECT x FROM tmp_table)". regards, tom lane