Re: Possible to improve query plan?

От: Jeremy Palmer
Тема: Re: Possible to improve query plan?
Дата: ,
Msg-id: 666FB8D75E95AE42965A0E76A5E5337E06D2C68681@prdlsmmsg01.ad.linz.govt.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Possible to improve query plan?  ("Ing. Marcos Ortiz Valmaseda")
Список: pgsql-performance

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

Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  (Andy Colson, )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Ing. Marcos Ortiz Valmaseda", )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Kevin Grittner", )
    Re: Possible to improve query plan?  (Robert Haas, )
     Re: Possible to improve query plan?  ("Kevin Grittner", )
      Re: Possible to improve query plan?  (Tom Lane, )
       Re: Possible to improve query plan?  ("Kevin Grittner", )
        Re: Possible to improve query plan?  (Jeremy Palmer, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Cédric Villemain, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Jeremy Palmer, )
     Re: Possible to improve query plan?  (Tom Lane, )

It fits a Data Warehousing type application.

Apart from work_mem, my other parameters are pretty close to these numbers. I had the work_mem down a little because a
noticedsome clients were getting out of memory errors with large queries which involved lots of sorting. 

Thanks
Jeremy

-----Original Message-----
From: Ing. Marcos Ortiz Valmaseda [mailto:]
Sent: Tuesday, 18 January 2011 2:38 a.m.
To: Jeremy Palmer
Cc: ; Andy Colson
Subject: Re: [PERFORM] Possible to improve query plan?

Which is the type of your application? You can see it on the Performance Whackamole Presentation from Josh Berkus on
the 
PgCon 2009:
- Web application
- Online Transaction Processing (OLTP)
- Data WareHousing (DW)

And based on the type of your application, you can configure the postgresql.conf to gain a better performance of your
PostgreSQLserver. 
PostgreSQL postgresql.conf baseline:
 shared_buffers = 25% RAM
 work_mem = 512K[W] 2 MB[O] 128 MB[D]
 - but no more that RAM/no_connections
 maintenance_work_mem = 1/16 RAM
 checkpoint_segments = 8 [W], 16-64 [O], [D]
 wal_buffer = 1 MB [W], 8 MB [O], [D]
 effective_cache_size = 2/3 RAM

Regards


Ing. Marcos Luís Ortíz Valmaseda
Linux User # 418229 && PostgreSQL DBA
Centro de Tecnologías Gestión de Datos (DATEC)
http://postgresql.uci.cu
http://www.postgresql.org
http://it.toolbox.com/blogs/sql-apprentice

______________________________________________________________________________________________________

This message contains information, which is confidential and may be subject to legal privilege.
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or ) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________


В списке pgsql-performance по дате сообщения:

От: Fernando Hevia
Дата:
Сообщение: Re: Problem with query
От: Achilleas Mantzios
Дата:
Сообщение: Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2