Re: UPDATE slow

From: John Smith <john_smith_45678(at)yahoo(dot)com>
To: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
Cc: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: UPDATE slow
Date: 2003-02-05 06:08:08
Message-ID: 20030205060808.13757.qmail@web40706.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


No, it's still in dev. I've tried vacuum/full/analyze and reindex stats - none cause any noticable speed improvements.
Here are my postgresql.conf settings (I just uncommented the mentioned vars and restarted):
#
# Shared Memory Size
#
shared_buffers = 64 # 2*max_connections, min 16, typically 8KB each
max_fsm_relations = 100 # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4, typically 8KB each
#
# Non-shared Memory Sizes
#
sort_mem = 1024 # min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB

After restarting and before I added the indexes, the update query was nearly instantaneous. But after adding the indexes, it's back to about the same speed (5-15+ seconds) - on both the cygwin and linux boxes.

John

Is this a table that got a lot of updates en masse? If it was regularly
vacuumed but you still had a problem, it might be index bloat. Keep an
eye on it, and if the table starts to get slow, try reindexing the indexes
on that table and see if that happens.

The "fix" for this problem is to crank up max fsm settings, and run vacuum
more often, but that doesn't always actually fix things.

---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Smith 2003-02-05 06:21:48 Re: UPDATE slow
Previous Message John Smith 2003-02-05 05:56:46 Re: UPDATE slow [Viruschecked]