From: Gunnar Ingvi Thorisson <gunni@if.is>
To: pgsql-general@postgreSQL.org
To: pgsql-sql@postgreSQL.org
Subject: Bad update performance?
Date: Wed, 21 Jul 1999 00:06:15 +0100
Message-ID: <000001bed304$78193380$65dea8c0@gunni.gi.hf.is> (raw)
In-Reply-To: <199907201401.KAA03008@ll.mit.edu>
Hi there,
I´ve a table with about 142000 rows like shown below and I want to set field
"divis" to "unknown" by executing following update command:
update ipacct set divis = 'unknown';
However this seems to take hours, I´ve a PII 350MHz with 192Mb memory and
Quantum UIDE disk, is there a way for me to speed up the update process
except for switching to a SCSI hard drive? Does indexing the field "divis"
speed up the update performance?
The load on the machine is about 1,5 to 2 all the time and it´s hacking on
the hard drive like hell.
I´ve the same problem when I do "UPDATE ipacct SET traf = (src+dst)", it
takes hours. Is this normal? Is there another way to do this?
However reading from the database doing select is very fast.
Many thanks in advance for any hint,
best regards,
Gunnar Ingvi Thorisson
CCIE 4826
Iceland
gunni@if.is
venus:~$ psql ipacct
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.0 on i686-pc-linux-gnulibc1, compiled by gcc 2.7.2.3]
[cut]
ipacct=> select * from ipacct where id = 1;
id| datetime|pcol|saddr |sport|daddr |dport|pcnt| size|if
|login |mac |src|dst|traf|depart|divis
--+---------+----+-----------------+-----+------------+-----+----+-----+----
+-------+------------+---+---+----+------+-----
1|928368167| 6|209.85.127.151/32| 80|10.10.2.3/32| 2448|
57|74856|eth0|unknown|00805FC1525C|100| 3| 103| |
(1 row)
ipacct=> update ipacct set divis = 'unknown';
The table:
ID INT4 UNIQUE,
DATETIME TEXT,
PCOL INTEGER,
SADDR CIDR,
SPORT INTEGER,
DADDR CIDR,
DPORT INTEGER,
PCNT INT4
SIZE INT4;
IF TEXT;
LOGIN TEXT;
MAC TEXT;
SRC INTEGER;
DST INTEGER;
TRAF INTEGER;
DEPART TEXT;
DIVIS TEXT;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: gunni@if.is, pgsql-general@postgreSQL.org
Subject: Re: Bad update performance?
In-Reply-To: <000001bed304$78193380$65dea8c0@gunni.gi.hf.is>
References: <199907201401.KAA03008@ll.mit.edu> <000001bed304$78193380$65dea8c0@gunni.gi.hf.is>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox