Re: Sequence scans on indexed row

Hello,
I had the same problem days ago. My fault was that I didn't use 'XXX' on
update. I mean:
Update Table set column = XXX where indexcoulm='something';

Perhaps it is something like this...

good luck

yours

Cris..
----- Original Message -----
From: "kp" <pgsql@pobox.gr>
To: <pgsql-admin@postgresql.org>
Sent: Sunday, April 20, 2003 7:36 AM
Subject: [ADMIN] Sequence scans on indexed row


> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly