Re: indexed column not working as fast as expected - Mailing list pgsql-general

From gnari
Subject Re: indexed column not working as fast as expected
Date
Msg-id 004601c484fe$f467a7d0$0100000a@wp2000
Whole thread Raw
In response to indexed column not working as fast as expected  (Amir Zicherman <amir.zicherman@gmail.com>)
List pgsql-general
"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




pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: apple uses Postgres for RemoteDesktop 2
Next
From: "Gregory S. Williamson"
Date:
Subject: Re: indexed column not working as fast as expected