Thread

1. index not used

   Enrico Weigelt <weigelt@metux.de> — 2005-04-21T19:05:44Z

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

2. Re: index not used

   Darcy Buskermolen <darcy@wavefire.com> — 2005-04-21T19:23:52Z

   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
   
   

3. Re: index not used

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2005-04-21T21:15:08Z

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