Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id D30C99FB83E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun, 25 Feb 2007 14:27:14 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 91323-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun, 25 Feb 2007 14:27:09 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from joeconway.com (joeconway.com [68.15.9.201])
	by postgresql.org (Postfix) with ESMTP id BA0239FB831
	for <pgsql-sql@postgresql.org>; Sun, 25 Feb 2007 14:27:09 -0400 (AST)
Received: from [192.168.4.40] (account jconway [192.168.4.40] verified)
	by joeconway.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP-TLS id 4768231; Sun, 25 Feb 2007 10:27:08 -0800
Message-ID: <45E1D4FC.4050901@joeconway.com>
Date: Sun, 25 Feb 2007 10:27:08 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Thunderbird 1.5.0.9 (X11/20070212)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Richard Jones <rich@annexia.org>,  pgsql-sql@postgresql.org
Subject: Re: Very large IN-clause is slow, but how to rewrite it?
References: <20070225123913.GA17124@furbychan.cocan.org>
	<7794.1172422591@sss.pgh.pa.us>
In-Reply-To: <7794.1172422591@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.066 tagged_above=0 required=5 tests=AWL, BAYES_50,
	SPF_HELO_PASS, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200702/255
X-Sequence-Number: 27589

Tom Lane wrote:
> Richard Jones <rich@annexia.org> 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)".

If 8.2, what about
   ... WHERE foo IN (select x from (values (1$),(2$),...,(N$)) as t(x))
?

It would be interesting to see how that compares performance-wise.

Joe