Обсуждение: index not used

От:
Enrico Weigelt
Дата:

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:     
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

От:
Darcy Buskermolen
Дата:

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

От:
Litao Wu
Дата:

If id is PK, the query shoudl return 1 row only...
--- 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.
>
>
> thx
> --
>
---------------------------------------------------------------------
>  Enrico Weigelt    ==   metux IT service
>
>   phone:     +49 36207 519931         www:
> http://www.metux.de/
>   fax:       +49 36207 519932         email:
> 
>   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/

От:
Stephan Szabo
Дата:

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