Re: performance with query

От: Kevin Grittner
Тема: Re: performance with query
Дата: ,
Msg-id: 4A376EA90200002500027BE1@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: performance with query  (Alberto Dalmaso)
Ответы: Re: performance with query  (Alberto Dalmaso)
Список: pgsql-performance

Скрыть дерево обсуждения

performance with query  (Alberto Dalmaso, )
 Re: performance with query  (Joshua Tolley, )
 Re: performance with query  ("Kevin Grittner", )
 Re: performance with query  (Alberto Dalmaso, )
  Re: performance with query  (Matthew Wakeling, )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  (Tom Lane, )
     Re: performance with query  (Alberto Dalmaso, )
      Re: performance with query  (Tom Lane, )
      Re: performance with query (OT)  ("Albe Laurenz", )
    Re: performance with query  ("Kevin Grittner", )
  Re: performance with query  ("Kevin Grittner", )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  ("Kevin Grittner", )
    Re: performance with query  ("Kevin Grittner", )
     Re: performance with query  (Alberto Dalmaso, )
      Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  (Anthony Presley, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  ("Albe Laurenz", )
        Re: Speeding up a query.  (Grzegorz Jaśkiewicz, )
       Re: Speeding up a query.  (Alberto Dalmaso, )
        Re: Speeding up a query.  ("Kevin Grittner", )
        Re: Speeding up a query.  (Tom Lane, )
       Re: Speeding up a query.  (Merlin Moncure, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  ("Hartman, Matthew", )
       Re: Speeding up a query.  (Simon Riggs, )
        Re: Speeding up a query.  ("Hartman, Matthew", )
 Re: performance with query  (Alberto Dalmaso, )
 Re: performance with query  (Alberto Dalmaso, )
  Re: performance with query  ("Kevin Grittner", )
   Re: performance with query  (Alberto Dalmaso, )
    Re: performance with query  ("Kevin Grittner", )
     Re: performance with query  (Alberto Dalmaso, )
      Re: performance with query  ("Kevin Grittner", )
       Re: performance with query  (Tom Lane, )
        Re: performance with query  ("Kevin Grittner", )

Alberto Dalmaso <> 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 по дате сообщения:

От: "Mark Steben"
Дата:
Сообщение: Performance discrepancy
От: Aaron Turner
Дата:
Сообщение: High cost of ... where ... not in (select ...)