Re: JBoss w/int8 primary keys in postgres ...

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(at)micro-automation(dot)net>