On Fri, Aug 17, 2007 at 07:49:08PM +0800, Phoenix Kiula wrote: > However, I am a little frustrated by the amount of time PGSQL takes to > complete tasks. Just to accommodate these tasks, my conf file has the > following: > > autovacuum = off > wal_buffers=64 > checkpoint_segments=1000 > checkpoint_timeout=900 > fsync = off > maintenance_work_mem = 128MB I note you did not give either the shared_buffers setting or the work_mem setting, both of which would have a major impact on your problem. > I have dropped all indexes/indicises on my table, except for the > primary key. Still, when I run the query: > > UPDATE mytable SET mycolumn = lower(mycolumn); > > This is, at the time of this writing, has taken well over 35 minutes! > On a table of a "mere" 6 million rows (quoted from one discussion on > this mailing list). How big is the actual table itself (in bytes). > Suspecting that locking may be the cause of this, I read up on > http://www.postgresql.org/docs/8.2/static/explicit-locking.html and > found nothing specific that would help a person starting out on the DB > to actually do meaningful explicit locking that the UPDATE command > does not already do. I doubt it's locking. > What else can I do to make this go fast enough to be normal!? Penny > for any thoughts and tips. Check your I/O throughput. You should be maxing out your disk... Although, with 6million rows, it might even fit in memory. Can you see (in ps) what it's actually doing? Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment:
signature.asc
Description: Digital signature
