Hello all.
I am doing a straight forward update on a table with over 1.6 million
records based on a where clause that only uses one of its indexed
columns (btree on a varchar). However postgres instists on not using the
index and instead does a sequence scan that takes ages to complete.
The weird thing is that the same table has another column indexed in
exactly the same way (btree on a varchar) which postgres correctly uses
the index on.
One difference between the two columns is that on one I have fixed the
maximum size of the varchar to 80 while the other is free to grow as
much as it wants.
The other difference between these two columns (or rather the kind of
data they contain) is that the column for which postgres *uses* the
index on contains around 32000 distinct values while the other only
contains 14 distinct values.
I have a suspicion that postgres (i'm using 7.1.3) incorrectly assumes
that a sequence scan is cheaper for the column with the 14 distinct
values in it and ends up scanning 1.6 million rows.
Bug? If so, has it been fixed on later versions?
TIA for any replies,
kp
