int8 index isn't used for query against number
От | Mark Dalphin |
---|---|
Тема | int8 index isn't used for query against number |
Дата | |
Msg-id | 381F44D5.19474FFC@amgen.com обсуждение исходный текст |
Ответы |
Re: [GENERAL] int8 index isn't used for query against number
(Peter Eisentraut <peter_e@gmx.net>)
|
Список | pgsql-general |
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)
В списке pgsql-general по дате отправления: