Re: UPDATE slow

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?
>

I tried this earlier on just a plain:

create table testme ( id integer unique, clicks integer unique );

This is my explain:

desticorp=> explain analyze select count(1) from testme;
NOTICE: QUERY PLAN:

Aggregate (cost=22.50..22.50 rows=1 width=0) (actual time=78.52..78.52 rows=1 loops=1)
-> Seq Scan on testme (cost=0.00..20.00 rows=1000 width=0) (actual time=0.11..51.74 rows=11999 loops=1)
Total runtime: 78.67 msec

EXPLAIN

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.

--
Nigel J. Andrews