Re: indexed column not working as fast as expected

"Amir Zicherman" <amir.zicherman@gmail.com> wrote:
>
> 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.

you need to "analyze table1"

possibly a "select * from table1 where col1=4 order by col1 DESC" could
be faster, as this is the highest value, but i am not sure.

if it is known that the value 4 will always appear MUCH less frequently
then the others, you might try
  create index table1_partial on table1(col1) where col1=4;
then the select will definitively be faster for this value.

gnari