Re: indexed column not working as fast as expected

"Amir Zicherman" <amir(dot)zicherman(at)gmail(dot)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