JBoss w/int8 primary keys in postgres ... - Mailing list pgsql-jdbc

Greetings all,

    Having just read a thread on the lack of implicit type cooersion by
postgresql when planning a query to use an index scan as opposed to a
table scan (thread over on psql-performance list at
http://archives.postgresql.org/pgsql-performance/2003-09/msg00090.php),
it hit me that the DB backing our EJB application has int8 primary keys
throughout (cooresponding to java datatype Long), and that the vast
majority of queries that JBoss is issuing with respect to pk values are
getting worst-case treatment as sequential scans as opposed to index
scans just from the int4 / int8 type mismatch on the primary key index.
Sample query generated by JBossCMP code:

LOG:  query: SELECT id FROM address WHERE (contact=30410)

Here's the table:
social=# \d address;
        Table "public.address"
     Column    |  Type   | Modifiers
--------------+---------+-----------
  id           | bigint  | not null
  name         | text    |
  streetnumber | integer | not null
  street       | text    |
  street2      | text    |
  city         | text    |
  state        | text    |
  zip          | text    |
  contact      | bigint  |
Indexes: pk_address primary key btree (id),
          address_contact btree (contact)

Here's how it gets analyzed, since the 31410 gets treated by int4
naturally:

explain analyze SELECT id FROM address WHERE (contact=30410);
                                             QUERY PLAN
------------------------------------------------------------------------
---------------------------
  Seq Scan on address  (cost=0.00..166.51 rows=1 width=8) (actual
time=17.41..17.41 rows=0 loops=1)
    Filter: (contact = 30410)
  Total runtime: 17.50 msec
(3 rows)

Explicitly casting the literal number to int8, making it match the
index type lets us do an index scan:

# explain analyze SELECT id FROM address WHERE (contact=30410::int8);
                                                       QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------
  Index Scan using address_contact on address  (cost=0.00..4.20 rows=1
width=8) (actual time=0.04..0.04 rows=0 loops=1)
    Index Cond: (contact = 30410::bigint)
  Total runtime: 0.12 msec
(3 rows)

A great bit better, of course.

Is there any hope to get JBoss + PG JDBC to do this typecasting
automatically? Assuming JBoss is using PreparedStatements for its dirty
work, could the setLong() method on the JDBC driver's PreparedStatement
  implementation possibly always punch in the trailing '::int8' ?

Or does someone already know that JBossCMP does not use
PreparedStatements, and I'm really barking up the wrong tree here.

Thanks,

James