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

James,

Have you run vacuum analyze on the database ?

Dave
On Sat, 2003-09-06 at 14:38, James Robinson wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
--
Dave Cramer <Dave@micro-automation.net>