quote java.lang.Long in SQL statement
zipper78 Aug 13, 2003 6:30 AMHi,
I've written an EJB 2.0 CMP applikation based on JBoss 3.0.7 and PostgreSQL 7.2.3. It runs fine but is quite slow when getting beans out of the database.
I've found out that the follwoing SQL statement generated by JBoss is wrong:
SELECT id,zip,city,country FROM zipcode where (id=1) or (id=2) or (id=3) or (id=4)
It should be:
SELECT id,zip,city,country FROM zipcode where (id='1') or (id='2') or (id='3') or (id='4')
or:
SELECT id,zip,city,country FROM zipcode where (id=1::bigint) or (id=2::bigint) or (id=3::bigint) or (id=4::bigint)
although the column id is numeric (int8).
The problem with the first statement is that PostgreSQL does not know the type of 1,2,3 or 4 and therefore has to cast it while comparing it with every single id value row by row (seq scan). As I have more than 40.000 entries in the database, this tooks about 600ms.
With the second and third statement PostgreSQL knows the data type (don't ask me, why in the second, too) and can use an index. This takes about 20ms.
So how can I tell JBoss to quote numeric values, too as it does for strings?
PostgreSQL query plan as additional infiormation:
db=# explain analyze select id,zip,city from zipcode where id=1 or id=2 or id=3 or id=4;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on zipcode (cost=100000000.00..100001265.02 rows=4 width=30) (actual time=0.39..623.53 rows=4 loops=1)
Filter: ((id = 1) OR (id = 2) OR (id = 3) OR (id = 4))
Total runtime: 625.07 msec
(3 rows)
db=# explain analyze select id,zip,city from zipcode where id='1' or id='2' or id='3' or id='4';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_zipcode, pk_zipcode, pk_zipcode, pk_zipcode on zipcode (cost=0.00..22.59 rows=4 width=30) (actual time=19.77..20.04 rows=4 loops=1)
Index Cond: ((id = 1::bigint) OR (id = 2::bigint) OR (id = 3::bigint) OR (id = 4::bigint))
Total runtime: 20.48 msec
(3 rows)