Quoting affects usage of indices on int8 columns...

Is it normal that this query will be performed using sequential scan
(as reported by explain) :

SELECT b FROM test WHERE a=1;

while this one will use the index on a?

SELECT b FROM test WHERE a='1';


It seems that the quoting affects the way the query is performed
when the index is on a column on type "int8".  The index is used
when the column type is "int".


Here is how to reproduce the problem :

CREATE TABLE test (a int8 PRIMARY KEY NOT NULL, b int);
INSERT INTO test VALUES (1, 1);
INSERT INTO test VALUES (2, 1);
INSERT INTO test VALUES (3, 1);
INSERT INTO test VALUES (4, 1);
EXPLAIN SELECT b FROM test WHERE a=1;
EXPLAIN SELECT b FROM test WHERE a='1';


However, if the table is defined this way :

CREATE TABLE test (a int PRIMARY KEY NOT NULL, b int);

both SELECTs are done using the index.


(I am using postgresql 7.0.2)


This is quite problematic for me since I have no control on how the
selects are built;
I am using postgresql as a backend for an Enterprise JavaBeans server
(weblogic) and
the "finder" methods for the container managed entity beans are built
automatically....


--
Vincent Trussart, trussarv@CIRANO.UMontreal.CA
Clé publique GnuPG/PGP : http://www.CIRANO.UMontreal.CA/~trussarv/key.asc
Key ID = FD1D419C
Key fingerprint = 8F0B D1A3 8933 DA27 4DAA  9724 E69E 2D44 FD1D 419C