Re: performance with query

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: performance with query
Дата
Msg-id 4A376EA90200002500027BE1@gw.wicourts.gov
обсуждение исходный текст
Ответ на Re: performance with query  (Alberto Dalmaso <dalmaso@clesius.it>)
Ответы Re: performance with query  (Alberto Dalmaso <dalmaso@clesius.it>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Matthew Wakeling
Дата:
Сообщение: Re: performance with query
Следующее
От: Alberto Dalmaso
Дата:
Сообщение: Re: performance with query