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
