Hi all,
i was wondering if hibernate knows how to deal with the issue regarding =
int2/int8 in Postgresql ?
Example:
create table message(id int8 not null primary key,name text);
in my example i got 25 000 records in this table, if i issue the =
following query:
explain analyse select * from message where num=3D10;
NOTICE: QUERY PLAN:
Seq Scan on message (cost=3D0.00..1493.72 rows=3D1 width=3D117) (actual =
time=3D521.00..521.00 rows=3D0 loops=3D1)
Total runtime: 521.00 msec
As you can see, Postgres did not use the index on id, there are 2 =
possibles workarounds, either cast the parameter as an int8 as follow:
explain analyse select * from message where num=3D10::int8;
Or simpler just quote the paramater, postgres will cast it =
automatically:
explain analyse select * from message where num=3D'10';
And now the results:
NOTICE: QUERY PLAN:
Index Scan using message_pkey on message (cost=3D0.00..5.01 rows=3D1 =
width=3D117) (actual time=3D0.00..0.00 rows=3D0 loops=3D1)
Total runtime: 10.00 msec
FIX: the best approach should be to quote any number paramater.
Chris
|
