Thanks a lot folks, Left the query running for 10+ hours and had to kill it. I guess there really was no need to have lots of shared buffers (the hope was that postgresql will cache the whole table). I ended up doing this step inside the application as a pre-processing step. Can't have postgres running with different fsych options since this will be part of an "easy to install and run" app, that should just require a typical PosgreSQL installation.
Pavel Velikhov ----- Original Message ---- From: Kenneth Marshall <[EMAIL PROTECTED]> To: Pavel Velikhov <[EMAIL PROTECTED]> Cc: Jonah H. Harris <[EMAIL PROTECTED]>; pgsql-performance@postgresql.org Sent: Friday, October 19, 2007 8:17:48 PM Subject: Re: [PERFORM] need help with a query On Fri, Oct 19, 2007 at 09:11:36AM -0700, Pavel Velikhov wrote: > Thanks for you help! > > Got a very different query plan this time, with a hash join between links and articles. At least now postgres is using both shared memory buffers and working mem, but its still completely IO bound, only getting in 5-6% CPU once in a while. I guess I can't squeeze more out of the laptop, but I also have a machine with 16GB RAM that I'll try this on next. Should I allocate tons of memory into shared buffers or into the working memory? This is an extremely I/O intensive query that must rewrite every entry in the table. You could speed it up by starting postgresql with fsync disabled, run the update, and then restart it with fsync re-enabled. Ken __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
