From: Anibal David Acosta <aa@devshock.com>
To: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
Cc: 'Tomas Vondra' <tv@fuzzy.cz>
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> (raw)
In-Reply-To: <4E688FFC0200002500040F10@gw.wicourts.gov>
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
<4E688FFC0200002500040F10@gw.wicourts.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aa@devshock.com, Kevin.Grittner@wicourts.gov, tv@fuzzy.cz
Subject: Re: how delete/insert/update affects select performace?
In-Reply-To: <012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox