Alberto Dalmaso <dalmaso@clesius.it> wrote:
>> What version of PostgreSQL?
> 8.3 that comes with opensuse 11.1
Could you show us the result of SELECT version(); ?
> max_prepared_transactions = 30
Unless you're using distributed transactions or need a lot of locks,
that's just going to waste some RAM. Zero is fine for most people.
> maintenance_work_mem =50MB
That's a little small -- this only comes into play for maintenance
tasks like index builds. Not directly part of your reported problem,
but maybe something to bump to the 1GB range.
> max_fsm_pages = 160000
> max_fsm_relations = 5000
Have you done any VACUUM VERBOSE lately and captured the output? If
so, what do the last few lines say? (That's a lot of relations for
the number of pages; just curious how it maps to actual.)
> enable_hashjoin = off
> enable_nestloop = off
> enable_seqscan = off
> enable_sort = off
That's probably a bad idea. If particular queries aren't performing
well, you can always set these temporarily on a particular connection.
Even then, turning these off is rarely a good idea except for
diagnostic purposes. I *strongly* recommend you put all of these back
to the defaults of 'on' and start from there, turning off selected
items as needed to get EXPLAIN ANALYZE output to demonstrate the
better plans you've found for particular queries.
> effective_cache_size = 3600MB
That seems a little on the low side for an 8GB machine, unless you
have other things on there using a lot of RAM. Do you?
If you could set the optimizer options back on and get new plans where
you show specifically which options (if any) where turned off for the
run, that would be good. Also, please attach the plans to the email
instead of pasting -- the word wrap makes them hard to read. Finally,
if you could do \d on the tables involved in the query, it would help.
I'll hold off looking at these in hopes that you can do the above.
-Kevin
