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

Howdy:

Java middlewares like JBossCMP issue many queries in the general form
of:

SELECT t1.att1, t1.att2 ... t1.attN FROM t1 WHERE (t1.id = X) or
(t1.id = Y) ....

where there may be anywhere between 1 and thousands of "(id = N)"
blocks ORed together. These may be transformed to the "WHERE t1.id IN
(X, Y, ...)" form for possibly a little performance gain (possibly --
I've not yet checked to see if this plans better than the other, but I
could imagine this form being parsed into the hashjoin form as opposed
to a huge index filter form).

Real performance gains, however, could be attained through being able
to ultimately use either the v2 PREPARE / EXECUTE statements or the
better v3 protocol's bind / execute commands, but only if the SQL-level
form of the query could better represent the fact there are not really
N params, but, rather, one single param of type Set (or, heck, Array?).
This would let all such queries map onto one single backend prepared
statement, regardless of the size of the id set being passed in.

I guess that separate preparation for each different cardinality would
be okay performance-wise, but if there were some way to get all such
queries factored-down into one single planned statement, it could:

1) Make far better use of JBoss's connection-wide LRU cache of
PreparedStatements, since only one entry (with much higher cache hit
rate) could exist for the entire family of queries.

2) Make better use of backend memory, since it only needed to prepare
one such (generic) form, as opposed to one for each cardinality.

Problems in implementation:

1) JBoss query engine would need to be educated about the potential to
use this form as opposed to the "OR (t1.id=X)" form. Likewise, JBoss
could / should well be educated about being able to use the "WHERE
t1.id IN (X, Y, ...)" form for databases which support "WHERE .. IN (
.. )", probably an easier sell since this is most likely supported by
more DBs than just PG.

2) Does the JDBC spec allow any hooks for passing in a set of ids as
one single param? We'd need the SQL-template to be prepared to look
something like:

SELECT t1.attr1 FROM t1 where t1.id in ( ? )

From memory, perhaps setArray() might could be hacked for the job. I
know JBossCMP uses
the setObject() call, so perhaps JDBC could be tricked out to handle a
java.util.Collection, an arguably cleaner way to do it -- no backward
compat issues since could be all-new functionality. JDBC driver could
just iterate through the collection contents, calling setObject
accordingly. Harder part would be educating JBoss to do this. Hardest
part would be convincing someone to commit it into JBoss.

3) Can a cardinality-free plan even be made? I bet I'm assuming a
little too much in asserting all such plans are equal, but I suspect
that Tom is going to tell me that the query for just one id value would
and should be planned differently from the 800-value form, since the
800-value form might well prefer a full sequential scan, since the
table might only have 900 live rows in it.

Anyone have any insight or opinions?

[ crossposted to pgsql-sql for anyone's insight into the pure SQL /
planning matters. Apologies in advance ].

----
James Robinson
Socialserve.com