Skip site navigation (1) Skip section navigation (2)

Re: Prepared Statements and large where-id-in constant blocks?

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: James Robinson <jlrobins(at)socialserve(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: Prepared Statements and large where-id-in constant blocks?
Date: 2004-04-20 02:57:53
Message-ID: 408491B1.20800@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-sql
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


In response to

Responses

pgsql-sql by date

Next:From: Stijn VanroyeDate: 2004-04-20 07:26:03
Subject: Re: three-way join - solved
Previous:From: Tom LaneDate: 2004-04-20 01:11:33
Subject: Re: relation X does not exist

pgsql-jdbc by date

Next:From: Tom LaneDate: 2004-04-20 04:15:05
Subject: Re: EXECUTE command tag returns actual command
Previous:From: Tom LaneDate: 2004-04-20 01:02:38
Subject: Re: [SQL] Prepared Statements and large where-id-in constant blocks?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group