Re: UPDATE slow

On Wed, 5 Feb 2003, Nigel J. Andrews wrote:

> On Tue, 4 Feb 2003, Stephan Szabo wrote:
>
> > On Tue, 4 Feb 2003, John Smith wrote:
> >
> > > That works - updates on foo take about 1.4 seconds. I dropped all the
> > > indexes and fk's on stats and updates there take about 2.8 seconds.
> > > These are on the cygwin machine.
> >
> > The 2.8 seconds is on stats after dropping the fks and indexes? But
> > it didn't help on the linux box?
> >
>
> desticorp=> explain analyze update testme set clicks = clicks + 123;
> NOTICE: QUERY PLAN:
> Seq Scan on testme (cost=0.00..20.00 rows=1000 width=14) (actual time=0.03..256.21 rows=11999 loops=1)
> Total runtime: 2060.41 msec
>
> EXPLAIN
>
>
> As you can see this took 2 seconds after already been given the chance to cache
> the table. It's also possible to see that I used 12000 rows in my table and
> that the sequential scan part of the operation is a helluva lot slower when
> writing.
>
> This is on a dual P-III 550MHz system. Memory settings probably aren't tuned
> too much though I don't think that would impact too much on this quick
> test. Although loaded and usually noticable delays in window refresh when
> flicking through my screens this system seems to have a very low CPU
> utilisation and plenty of memory usable (for a change). Although I am wondering
> what I've run in the last couple of weeks that's pushed me to use 150MB of swap
> (768MB physical so not an insignificant amount).
>
> Therefore John's 2.8s seems a reasonable time to me.

Yeah, but I thought he'd said that on the linux box, even after dropping
indexes and fks it was taking 5-10 seconds.

I'm also a bit confused because I'm not sure he's getting 2.8 seconds to
update all the records or just a single record.