|
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=10;
NOTICE: QUERY PLAN:
Seq Scan on message (cost=0.00..1493.72 rows=1 width=117) (actual
time=521.00..521.00 rows=0 loops=1)
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=10::int8;
Or simpler just quote the paramater, postgres will
cast it automatically:
explain analyse select * from message where num='10';
And now the results:
NOTICE: QUERY PLAN:
Index Scan using message_pkey on message (cost=0.00..5.01 rows=1
width=117) (actual time=0.00..0.00 rows=0 loops=1)
Total runtime: 10.00 msec FIX: the best approach should be to quote any
number paramater.
Chris |
