Обсуждение: int8 index isn't used for query against number

Hi,

I was having trouble with what seemed to be very slow access to a
table I had created.  When I used EXPLAIN to clarify matters, it
seemed that my query required quotes around the parameter in order to
get the use of my index; otherwise a sequential scan is used (the
table is over 2 million rows so that was taking too long...).

Is this "expected" behavior and, if so, how do I know when to use
quotes around query parameters?

System:
        Postgresql 6.5.1 with patches for vacuum and unique index
        Irix 6.5

Thanks,
Mark

=========================================================================
CREATE TABLE Seq  (
        Contig_ID int8 PRIMARY KEY,
        Contig_Accession int8 UNIQUE NOT NULL,
        FileID smallint NOT NULL,
        ByteOffset int8 NOT NULL,       -- Use ftell64() under IRIX
        SeqLength int NOT NULL,
        FOREIGN KEY (FileID) REFERENCES File
                ON UPDATE CASCADE ON DELETE CASCADE
);


db=> \d Seq
Table    = seq
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| contig_id                        | int8 not null                    |     8 |
| contig_accession                 | int8 not null                    |     8 |
| fileid                           | int2 not null                    |     2 |
| byteoffset                       | int8 not null                    |     8 |
| seqlength                        | int4 not null                    |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  seq_contig_accession_key
          seq_pkey

db=> select count(*) from seq;
  count
-------
2370400
(1 row)

-- Without quotes, no Index scan; very slow
db=> EXPLAIN
db-> SELECT FileID, Contig_Accession, ByteOffset, SeqLength FROM Seq S
db-> WHERE Contig_Accession=6739795;
NOTICE:  QUERY PLAN:

Seq Scan on seq s  (cost=105160.20 rows=1 width=22)

EXPLAIN

-- With quotes, index scan and almost instantaneous responce.
db=> explain
db-> SELECT FileID, Contig_Accession, ByteOffset, SeqLength FROM Seq S
db-> WHERE Contig_Accession='6739795';
NOTICE:  QUERY PLAN:

Index Scan using seq_contig_accession_key on seq s  (cost=2.05 rows=1 width=22)

EXPLAIN

====================================================================

--
Mark Dalphin                          email: mdalphin@amgen.com
Mail Stop: 29-2-A                     phone: +1-805-447-4951 (work)
One Amgen Center Drive                       +1-805-375-0680 (home)
Thousand Oaks, CA 91320                 fax: +1-805-499-9955 (work)

On Nov 2, Mark Dalphin mentioned:

> -- Without quotes, no Index scan; very slow
> db=> EXPLAIN
> db-> SELECT FileID, Contig_Accession, ByteOffset, SeqLength FROM Seq S
> db-> WHERE Contig_Accession=6739795;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on seq s  (cost=105160.20 rows=1 width=22)
>
> EXPLAIN
>

Try ... WHERE Contig_Accession = 6739795::int8. The same thing happens
with int2 I believe because integers are all assumed to be int4's and the
backend isn't too smart in that regard one could say.

> -- With quotes, index scan and almost instantaneous responce.
> db=> explain
> db-> SELECT FileID, Contig_Accession, ByteOffset, SeqLength FROM Seq S
> db-> WHERE Contig_Accession='6739795';
> NOTICE:  QUERY PLAN:
>
> Index Scan using seq_contig_accession_key on seq s  (cost=2.05 rows=1 width=22)
>
> EXPLAIN

Interesting. Perhaps the the fact that you give it a string forces it to
undertake some sort of typecast and it actually casts it to int8.

--
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden