Re: indexed column but queries still slow

On Tue, Aug 17, 2004 at 14:06:11 -0700,
Amir Zicherman <amir(dot)zicherman(at)gmail(dot)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.