Alberto Dalmaso <dalmaso@xxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
