From: | Edmund Mergl <E(dot)Mergl(at)bawue(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers Mailinglist <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] strange behavior of UPDATE |
Date: | 1999-05-25 18:46:41 |
Message-ID: | 374AF011.22C181AE@bawue.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
>
> Edmund Mergl <E(dot)Mergl(at)bawue(dot)de> writes:
> > ... That's the reason I
> > was talking about the strange UPDATE behavior of
> > PostgreSQL. If it can determine a specific number
> > of rows in a reasonable time, it should be able to
> > update these rows in the same time frame.
>
> Not necessarily --- this table has a remarkably large number of indexes,
> and all of them have to be updated when a tuple is replaced. So the
> amount of work is significantly greater than simply finding the tuples
> will require.
>
> As I posted later, I think that much of the problem comes from poor
> handling of equal-key cases in our btree index routines...
>
> regards, tom lane
if this is the case, these routines must be very poor.
Again some numbers:
1.000.000 rows:
- select * from bench where k100 = 30
with indeces 10 seconds
without indeces 28 seconds
- update bench set k500k = k500k + 1 where k100 = 30
with indeces unknown
without indeces 36 seconds
Still the poor update routines do not explain the
strange behavior, that the postmaster runs for
hours using at most 10% CPU, and all the time
heavy disk activity is observed. According to
top, there are over 80MB free Mem and the postmaster
has been started with -o -F. Hence this disk activity
can not be simple swapping.
Some more numbers:
database #rows inserts create make_sqs make_nqs
index selects updates
----------------------------------------------------------------------------
pgsql 10.000 00:24 00:09 00:16 00:25
pgsql 100.000 04:01 01:29 01:06 49:45
pgsql 1.000.000 39:24 20:49 23:42 ???
whereas the increase of elapsed time is somewhat proportional
to the number of rows, for the update-case it is rather
exponential.
Edmund
--
Edmund Mergl mailto:E(dot)Mergl(at)bawue(dot)de
Im Haldenhau 9 http://www.bawue.de/~mergl
70565 Stuttgart fon: +49 711 747503
Germany
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-05-25 18:48:46 | Re: [HACKERS] create index updates nrows statistics |
Previous Message | ZEUGSWETTER Andreas IZ5 | 1999-05-25 18:39:00 | AW: [HACKERS] pg_dump core dump, upgrading from 6.5b1 to 5/24 sna pshot |