X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP
	id C858AD1B493; Mon, 19 Apr 2004 23:58:07 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 18484-02; Mon, 19 Apr 2004 23:58:10 -0300 (ADT)
Received: from yass.opencloud.co.nz (yass.opencloud.co.nz [203.79.85.162])
	by svr1.postgresql.org (Postfix) with ESMTP
	id 503A7D1B470; Mon, 19 Apr 2004 23:58:05 -0300 (ADT)
Received: from 203-79-104-60.cable.paradise.net.nz ([203.79.104.60]
	helo=opencloud.com)
	by yass.opencloud.co.nz with asmtp (Exim 3.35 #1 (Debian))
	id 1BFlSP-0000Bk-00; Tue, 20 Apr 2004 14:57:58 +1200
Message-ID: <408491B1.20800@opencloud.com>
Date: Tue, 20 Apr 2004 14:57:53 +1200
From: Oliver Jowett <oliver@opencloud.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.6b) Gecko/20031205 Thunderbird/0.4
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: James Robinson <jlrobins@socialserve.com>
Cc: pgsql-jdbc@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: Prepared Statements and large where-id-in constant blocks?
References: <11531DB4-921A-11D8-8696-000A9566A412@socialserve.com>
	<408450CE.1090009@opencloud.com>
In-Reply-To: <408450CE.1090009@opencloud.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200404/192
X-Sequence-Number: 9921

Oliver Jowett wrote:

> The CMP layer could perhaps use the = ANY array syntax and setArray() 
> (note that setArray() in the current driver needs fixing, I have an old 
> patch to do this):
> 
>   SELECT t1.attr1 FROM t1 where t1.id = ANY (?)

Unfortunately a bit of experimentation indicates that the planner 
doesn't do anything clever with ANY + constant array values (at least in 
7.4.1 which is what I have to hand):

> test=> explain select * from test_array where i in (1,2,3,4,5);
>                                                                       QUERY PLAN                                                                       
> -------------------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using test_array_pkey, test_array_pkey, test_array_pkey, test_array_pkey, test_array_pkey on test_array  (cost=0.00..15.12 rows=5 width=4)
>    Index Cond: ((i = 1) OR (i = 2) OR (i = 3) OR (i = 4) OR (i = 5))
> (2 rows)
> 
> test=> explain select * from test_array where i = any ('{1,2,3,4,5}'::integer[]);
>                            QUERY PLAN                           
> ----------------------------------------------------------------
>  Seq Scan on test_array  (cost=0.00..807.80 rows=10240 width=4)
>    Filter: (i = ANY ('{1,2,3,4,5}'::integer[]))
> (2 rows)
> 
> test=> select version();
>                                                      version                                                      
> ------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 7.4.1 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.3 20040110 (prerelease) (Debian)
> (1 row)

-O