From: | Vincent Trussart <trussarv(at)CIRANO(dot)UMontreal(dot)CA> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Quoting affects usage of indices on int8 columns... |
Date: | 2000-08-24 14:25:02 |
Message-ID: | 39A5303E.6655DE08@CIRANO.UMontreal.CA |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)CIRANO(dot)UMontreal(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ross J. Reedstrom | 2000-08-24 15:44:26 | Re: FW: Count & Distinct |
Previous Message | Darrin Ladd | 2000-08-24 14:07:22 | User security on tuple level |