Re: slow update but have an index

Martijn van Oosterhout wrote:

> On Fri, Aug 17, 2001 at 01:08:29PM +0200, Feite Brekeveld wrote:
> > Now this table has about 80,000 records. I need to update 74,000 status
> > fields. So I made a dump, and hacked the dump into SQL statements like:
> >
> > update accounting set status = 'C' where seqno = 1566385;
> > ....
> > and the other 74,000
> >
> > This is awfully slow. How come ? The index on the seqno should give
> > speedy access to the record.
>
> Well, an index speeds it up, but that times 80,000 will still take a while.
> Is there any trickery or will this work?
>
> update accounting set status = 'C';
>
> If so, that will be much faster.

No that will not work, because they other 6000 need not to be changed. Of
course I could update the this way and change the other 6000 back to their
original status, but the query I issued is so slow that I think something is
wrong.

>
>
> One sequential scan is faster than 80,000 index scans.
>
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org>
> http://svana.org/kleptog/
> > It would be nice if someone came up with a certification system that
> > actually separated those who can barely regurgitate what they crammed over
> > the last few weeks from those who command secret ninja networking powers.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Feite Brekeveld
feite(dot)brekeveld(at)osiris-it(dot)nl
http://www.osiris-it.nl