Обсуждение: 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

This has come up before so a more complete explanation should be in the
archives (and a discussion of typing came up on hackers in May-June
although I don't know if there was a resolution).

IIRC, the problem is due to the typing of the 1 constant into an
int4.  Unfortunately the normal workaround has been to explicitly
cast the constant, although you may not be able to do that if
you can't change the queries.  Other than that or using an int4
column, I don't remember seeing any effective workarounds but the
archives may turn something up.

Stephan Szabo
sszabo@bigpanda.com

On Thu, 24 Aug 2000, Vincent Trussart wrote:

> 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".