From: "Anibal David Acosta" <aa@devshock.com>
To: "'Kevin Grittner'" <Kevin.Grittner@wicourts.gov>,
	<pgsql-performance@postgresql.org>
Cc: "'Tomas Vondra'" <tv@fuzzy.cz>
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
	<4E688FFC0200002500040F10@gw.wicourts.gov>
In-Reply-To: <4E688FFC0200002500040F10@gw.wicourts.gov>
Subject: Re: how delete/insert/update affects select	 performace?
Date: Thu, 8 Sep 2011 12:40:07 -0400
Message-ID: <012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQHYrwFqiugYygNYi1ErgOQqHWWRlwF7ZGtRlR9R08A=
Content-Language: es-py

Postgres 9.0 on windows server 2008 r2
HW is a dell dual processor with 16gb of ram .

Tthe reason I add the enabled column to index is because a select won't need
to read the table to get this value

My select is : exists(select * from table where account_id=X and
service_id=Y and enabled='T')

So, do you think I must remove the enabled from index?

Thanks


-----Mensaje original-----
De: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] 
Enviado el: jueves, 08 de septiembre de 2011 10:51 a.m.
Para: Anibal David Acosta; pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] how delete/insert/update affects select performace?

"Anibal David Acosta" <aa@devshock.com> wrote:
 
> I have a table not too big but with aprox. 5 millions of rows, this 
> table must have 300 to 400 select per second. But also must have 10~20 
> delete/insert/update per second.
> 
> So, I need to know if the insert/delete/update really affect the 
> select performance and how to deal with it.
 
In addition to the advice from Tomas (which was all good) you should be
aware that depending on the version of PostgreSQL (which you didn't
mention), your hardware (which you didn't describe), and your configuration
(which you didn't show) the data modification can make you vulnerable to a
phenomenon where a checkpoint can cause a blockage of all disk I/O for a
matter of minutes, causing even simple SELECT statements which normally run
in under a millisecond to run for minutes.  This is more likely to occur in
a system which has been aggressively tuned for maximum throughput -- you may
need to balance throughput needs against response time needs.
 
Every one of the last several major releases of PostgreSQL has gotten better
at preventing this problem, so your best protection from it is to use a
recent version.
 
There's a good chance that you won't run into this, but if you do, you can
generally correct it by reducing your shared_buffers setting or making your
background writer more aggressive.
 
-Kevin