From: Oliver Jowett <oliver@opencloud.com>
To: James Robinson <jlrobins@socialserve.com>
Cc: pgsql-jdbc@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Prepared Statements and large where-id-in constant blocks?
Date: Tue, 20 Apr 2004 14:57:53 +1200
Message-ID: <408491B1.20800@opencloud.com> (raw)
In-Reply-To: <408450CE.1090009@opencloud.com>
References: <11531DB4-921A-11D8-8696-000A9566A412@socialserve.com>
<408450CE.1090009@opencloud.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliver@opencloud.com, jlrobins@socialserve.com, pgsql-jdbc@postgresql.org
Subject: Re: Prepared Statements and large where-id-in constant blocks?
In-Reply-To: <408491B1.20800@opencloud.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox