Re: indexed column not working as fast as expected

Amir,

What gnari said!

The vacuum analyze is absolutely necessary to update the statistics that the query planner uses.

Says chapter 11 of the postgres 7.4.2 manual:
"A major motivation for partial indexes is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all. This reduces the size of the index, which will speed up queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases."

So this would not help for the other 4 values in this column, but would help queries for this other value.

Greg

-----Original Message-----
From: gnari [mailto:gnari(at)simnet(dot)is]
Sent: Wed 8/18/2004 1:40 AM
To: Amir Zicherman; pgsql-general(at)postgresql(dot)org
Cc:
Subject: Re: [GENERAL] 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings