Обсуждение: indexed column but queries still slow

hi,

i have a btree index on col1 in table1.  The column has either values
1,2,3, or 4.  4 does not appear that much in the table (only 5 times).
 there are about 20 million rows in the table.  when i do a "select *
from table1 where col1=4" it takes very long time to get back to me
(around 4 minutes).  why is it taking so long if i have an index on
it?  I also tried this with a hash index and it was still slow.

thanx, amir

Amir Zicherman wrote:

 > hi,
 >
 > i have a btree index on col1 in table1.  The column has either values
 > 1,2,3, or 4.  4 does not appear that much in the table (only 5 times).
 >  there are about 20 million rows in the table.  when i do a "select *
 > from table1 where col1=4" it takes very long time to get back to me
 > (around 4 minutes).  why is it taking so long if i have an index on
 > it?  I also tried this with a hash index and it was still slow.

May I see the result of
explain analyze select * from table1 where col1 = 4;

and also the table definition could be usefull.

Did you run recently an analyze on your table ?


Regards
Gaetano Mendola

On Tue, Aug 17, 2004 at 14:06:11 -0700,
  Amir Zicherman <amir.zicherman@gmail.com> wrote:
> hi,
>
> i have a btree index on col1 in table1.  The column has either values
> 1,2,3, or 4.  4 does not appear that much in the table (only 5 times).
>  there are about 20 million rows in the table.  when i do a "select *
> from table1 where col1=4" it takes very long time to get back to me
> (around 4 minutes).  why is it taking so long if i have an index on
> it?  I also tried this with a hash index and it was still slow.

With that kind of distribution, you may be better off with a partial
index on the table for col1=4 instead of the current index. If the
other values show up with roughly equal frequency, you aren't going to
want an index scan to be used anyway, so you might as well use the
smaller partial index.