Updates are slow.. - Mailing list pgsql-general

Hello,

We've been using PostgreSQL for a few months and it has been running
much slower than anticipated. We've tried playing with configuration
options with limited results, and I'm hoping someone may have some tips.

For the purposes of our performance test, we created a PostgreSQL
database with only a single table of about 1.2 million records. Updating
an indexed column for 600K records on that table is unusually slow. It
takes anywhere from forty minutes to over an hour.

To describe the situation, I'm going to call our table EMP (employee).
Our EMP has 1.2MM records, and 600K of those records have a Dept_ID of 4.
Our EMP has 20 columns, and there are two indexes on EMP:
1) (EMP_ID)
2) (DEPT_ID, EMP_STATUS)

The update statement that takes so long is:
UPDATE Emp
   SET Dept_ID = 5
 WHERE Dept_ID = 4;

Thus it is reading from the index and also having to update it at the
same time. As I mentioned, 600K records are impacted by this update. An
out of the box configuration of PostgreSQL takes over an hour to do this.

I've tried changing the default settings as follows:
shared_buffers = 8192
sort_mem = 15360
fsync = false
checkpoint_segments = 15
wal_files = 10
wal_sync_method = fdatasync

With these settings, it still takes 39 minutes. Here are the executor
statistics from the last test we did yesterday:
2002-06-10 13:15:18 DEBUG:  EXECUTOR STATISTICS
! system usage stats:
!       2315.929107 elapsed 142.990000 user 87.310000 system sec
!       [163.390000 user 93.610000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       8/14 [16609/5363] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
!       Shared blocks:     552060 read,     476736 written, buffer hit rate =
95.33%
!       Local  blocks:          0 read,          0 written, buffer hit rate =
0.00%
!       Direct blocks:          0 read,          0 written


For the basis of comparison, we downloaded Oracle, created the same table,
and loaded the same data. With Oracle, the same update took at most 5 minutes!

Our machine is a PIII 850 with 768MB RAM and an ATA100 40GB drive and RH Linux
7.2.  Postgres is v7.2. Every time we run the test on Postgres, we a vacuum
analyze immediately before.  Foreign key constraints are not enabled.

I would not expect the performance difference between Oracle and Postgres to
be so large. While the example we chose for our test may not happen that often
(updating an indexed column), various other queries we run on our full system
have not performed to our expectations.

I'm hoping we're overlooking something basic. If anyone has any suggestions,
I would greatly appreciate it.

Thanks,

Tom Burke
Eppend, Inc.
http://www.eppend.com