Re: slow update but have an index

Martijn van Oosterhout wrote:

> On Fri, Aug 17, 2001 at 01:40:32PM +0200, Feite Brekeveld wrote:
> > > 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.
>
> Well, there's a bit of an issue here. Each time you do an insert, the table
> gets larger, the index gets larger, etc. Disk accesses everywhere. If you
> can do it one query then the sequential is much friendlier to disk caches
> and the performance will be much more consistant.
>
> Can you codify in an SQL query how you decide which records to change. I've
> found the best way to improve performance is to minimise the number of
> queries, letting the database do the maximum optimisation possible.
>

hacked it with perl into several

update ... where seqno between x and y statements.

That went smoothly.

>
> --
> 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 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

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