Обсуждение: index not used
Hi folks,
I'm doing a simple lookup in a small table by an unique id, and I'm
wondering, why explains tells me seqscan is used instead the key.
The table looks like:
id bigint primary key,
a varchar,
b varchar,
c varchar
and I'm quering: select * from foo where id = 2;
I've got only 15 records in this table, but I wanna have it as
fast as possible since its used (as a map between IDs and names)
for larger queries.
thx
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------
On Thursday 21 April 2005 12:05, Enrico Weigelt wrote: > Hi folks, > > > I'm doing a simple lookup in a small table by an unique id, and I'm > wondering, why explains tells me seqscan is used instead the key. > > The table looks like: > > id bigint primary key, > a varchar, > b varchar, > c varchar > > and I'm quering: select * from foo where id = 2; > > I've got only 15 records in this table, but I wanna have it as > fast as possible since its used (as a map between IDs and names) > for larger queries. The over head to load the index, fetch the record in there, then check the table for visibility and return the value, is far greater than just doing 15 compares in the original table. > > > thx -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759
If id is PK, the query shoudl return 1 row only... --- Enrico Weigelt <weigelt@metux.de> wrote: > > Hi folks, > > > I'm doing a simple lookup in a small table by an > unique id, and I'm > wondering, why explains tells me seqscan is used > instead the key. > > The table looks like: > > id bigint primary key, > a varchar, > b varchar, > c varchar > > and I'm quering: select * from foo where id = 2; > > I've got only 15 records in this table, but I wanna > have it as > fast as possible since its used (as a map between > IDs and names) > for larger queries. > > > thx > -- > --------------------------------------------------------------------- > Enrico Weigelt == metux IT service > > phone: +49 36207 519931 www: > http://www.metux.de/ > fax: +49 36207 519932 email: > contact@metux.de > cellphone: +49 174 7066481 > --------------------------------------------------------------------- > -- DSL ab 0 Euro. -- statische IP -- UUCP -- > Hosting -- Webshops -- > --------------------------------------------------------------------- > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
On Thu, 21 Apr 2005, Enrico Weigelt wrote: > I'm doing a simple lookup in a small table by an unique id, and I'm > wondering, why explains tells me seqscan is used instead the key. > > The table looks like: > > id bigint primary key, > a varchar, > b varchar, > c varchar > > and I'm quering: select * from foo where id = 2; > > I've got only 15 records in this table, but I wanna have it as > fast as possible since its used (as a map between IDs and names) > for larger queries. Two general things: For 15 records, an index scan may not be faster. For simple tests you can play with enable_seqscan to see, but for more complicated queries it's a little harder to tell. If you're using a version earlier than 8.0, you'll need to quote or cast the value you're searching for due to problems with cross-type comparisons (the 2 would be treated as int4).