From aa@devshock.com Thu Sep  8 12:56:57 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 2C109B5DBE0
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 09:56:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 09551-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 12:56:50 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com
	[209.85.213.174])
	by mail.postgresql.org (Postfix) with ESMTP id CEB68B5DBDA
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 09:56:49 -0300 (ADT)
Received: by yxp4 with SMTP id 4so424573yxp.19
	for <pgsql-performance@postgresql.org>;
	Thu, 08 Sep 2011 05:56:49 -0700 (PDT)
Received: by 10.236.22.33 with SMTP id s21mr4037545yhs.70.1315486254032;
	Thu, 08 Sep 2011 05:50:54 -0700 (PDT)
Received: from AnibalVAIO ([200.85.32.68])
	by mx.google.com with ESMTPS id u13sm779404anf.14.2011.09.08.05.50.46
	(version=TLSv1/SSLv3 cipher=OTHER);
	Thu, 08 Sep 2011 05:50:50 -0700 (PDT)
From: "Anibal David Acosta" <aa@devshock.com>
To: <pgsql-performance@postgresql.org>
Subject: how delete/insert/update affects select performace?
Date: Thu, 8 Sep 2011 08:51:13 -0400
Message-ID: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0044_01CC6E04.7A497220"
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AcxuJfxC66VLuGn/QpO99fVSw14MYQ==
Content-Language: es-py
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.584 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, SARE_MILLIONSOF=0.315
X-Spam-Level: 
X-Archive-Number: 201109/75
X-Sequence-Number: 44851

This is a multipart message in MIME format.

------=_NextPart_000_0044_01CC6E04.7A497220
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi!

 

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.

 

The table structure is very simple:

 

account_id integer (PK)

service_id integer (PK)

enabled char(1)

 

The index created on this has the same 3 columns.

 

Most of time the table has more insert or delete than update, when update
occur the column changed is enabled;

 

Thanks!

 

 


------=_NextPart_000_0044_01CC6E04.7A497220
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><META =
HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EstiloCorreo17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri","sans-serif";}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue =
vlink=3Dpurple><div class=3DWordSection1><p =
class=3DMsoNormal>Hi!<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>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.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>So, I need =
to know if the insert/delete/update really affect the select performance =
and how to deal with it.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>The table =
structure is very simple:<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>account_id =
integer (PK)<o:p></o:p></p><p class=3DMsoNormal>service_id integer =
(PK)<o:p></o:p></p><p class=3DMsoNormal>enabled char(1)<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>The index =
created on this has the same 3 columns.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Most of time =
the table has more insert or delete than update, when update occur the =
column changed is enabled;<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Thanks!<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p></div></body></html>
------=_NextPart_000_0044_01CC6E04.7A497220--




From tv@fuzzy.cz Thu Sep  8 14:10:28 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id CB87CB5DC5D
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 11:10:28 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 83874-01-3
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 14:10:22 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from elizabeth.gransy.com (ns2.gransy.com [89.187.132.100])
	by mail.postgresql.org (Postfix) with ESMTP id F3BD7B5DBE0
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 11:10:21 -0300 (ADT)
Received: from sq.gransy.com (localhost [127.0.0.1])
	by elizabeth.gransy.com (Postfix) with ESMTP id 8BF2C15E4124;
	Thu,  8 Sep 2011 16:10:19 +0200 (CEST)
Received: from 78.45.133.255 (SquirrelMail authenticated user tv@fuzzy.cz)
	by sq.gransy.com with HTTP; Thu, 8 Sep 2011 16:10:19 +0200
Message-ID: <a1d1b28341b1453b7ea85692cad35455.squirrel@sq.gransy.com>
In-Reply-To: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
Date: Thu, 8 Sep 2011 16:10:19 +0200
Subject: Re: how delete/insert/update affects select performace?
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "Anibal David Acosta" <aa@devshock.com>
Cc: pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
X-Priority: 3 (Normal)
Importance: Normal
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.585 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, SARE_MILLIONSOF=0.315
X-Spam-Level: 
X-Archive-Number: 201109/76
X-Sequence-Number: 44852

On 8 Září 2011, 14:51, Anibal David Acosta wrote:
> Hi!
>
>
>
> 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.

Yes, insert/update do affect query performance, because whenever a row is
modified a new copy is created. So the table might grow over time, and
bigger tables mean more data to read.

There are two ways to prevent this:

1) autovacuum - has to be configured properly (watch the table size and
number of rows, and if it grows then make it a bit more aggressive)

2) HOT

> The table structure is very simple:
>
> account_id integer (PK)
>
> service_id integer (PK)
>
> enabled char(1)
>
> The index created on this has the same 3 columns.
>
> Most of time the table has more insert or delete than update, when update
> occur the column changed is enabled;

So there's one index on all three columns? I'd remove the "enabled" from
the index, it's not going to help much I guess and it makes HOT possible
(the modified column must not be indexed). Plus there will be one less
index (the other two columns are already a PK, so there's a unique index).

Tomas




From Kevin.Grittner@wicourts.gov Thu Sep  8 14:51:05 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 93A0EB5DC24
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 11:51:05 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 55810-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 14:50:59 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id B86A6B5DC20
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 11:50:58 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Thu, 08 Sep 2011 09:50:58 -0500
Message-Id: <4E688FFC0200002500040F10@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Thu, 08 Sep 2011 09:50:52 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Anibal David Acosta" <aa@devshock.com>,
 <pgsql-performance@postgresql.org>
Subject: Re: how delete/insert/update affects select
	 performace?
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
In-Reply-To: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.089 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504, SARE_MILLIONSOF=0.315
X-Spam-Level: 
X-Archive-Number: 201109/77
X-Sequence-Number: 44853

"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



From aa@devshock.com Thu Sep  8 16:39:58 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 5CF91B5DBE7
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 13:39:58 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 77119-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 16:39:51 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yx0-f175.google.com (mail-yx0-f175.google.com
	[209.85.213.175])
	by mail.postgresql.org (Postfix) with ESMTP id 9EEDDB5DBE5
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 13:39:51 -0300 (ADT)
Received: by yxj17 with SMTP id 17so999644yxj.34
	for <pgsql-performance@postgresql.org>;
	Thu, 08 Sep 2011 09:39:50 -0700 (PDT)
Received: by 10.150.166.3 with SMTP id o3mr1008864ybe.200.1315499990563;
	Thu, 08 Sep 2011 09:39:50 -0700 (PDT)
Received: from AnibalVAIO ([200.85.32.68])
	by mx.google.com with ESMTPS id 14sm827324ybn.10.2011.09.08.09.39.40
	(version=TLSv1/SSLv3 cipher=OTHER);
	Thu, 08 Sep 2011 09:39:42 -0700 (PDT)
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
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQHYrwFqiugYygNYi1ErgOQqHWWRlwF7ZGtRlR9R08A=
Content-Language: es-py
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.585 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, SARE_MILLIONSOF=0.315
X-Spam-Level: 
X-Archive-Number: 201109/78
X-Sequence-Number: 44854

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




From Kevin.Grittner@wicourts.gov Thu Sep  8 17:00:40 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id ACA9AB5DC45
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 14:00:40 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 06598-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 17:00:34 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 1E732B5DC44
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 14:00:33 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Thu, 08 Sep 2011 12:00:33 -0500
Message-Id: <4E68AE600200002500040F47@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Thu, 08 Sep 2011 12:00:32 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Anibal David Acosta" <aa@devshock.com>,
 <pgsql-performance@postgresql.org>
Cc: "'Tomas Vondra'" <tv@fuzzy.cz>
Subject: Re: how delete/insert/update affects select
	 performace?
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
	<4E688FFC0200002500040F10@gw.wicourts.gov>
	<012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
In-Reply-To: <012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504
X-Spam-Level: 
X-Archive-Number: 201109/79
X-Sequence-Number: 44855

"Anibal David Acosta" <aa@devshock.com> wrote:
 
> Tthe reason I add the enabled column to index is because a select
> won't need to read the table to get this value
 
That's not true in PostgreSQL, although there is an effort to
support that optimization, at least to some degree.  In all current
versions of PostgreSQL, it will always need to read the heap to
determine whether the index entry is pointing at a version of the
row which is visible to your transaction.  Adding the enabled column
to an index will prevent faster HOT updates to that column.
 
> My select is : exists(select * from table where account_id=X and
> service_id=Y and enabled='T')
 
On the other hand, if you have very many rows where enabled is not
'T', and you are generally searching for where enabled = 'T', you
might want a partial index (an index with a WHERE clause in its
definition).  If enabled only has two states, you will probably get
better performance using a boolean column.
 
-Kevin



From aa@devshock.com Thu Sep  8 19:33:43 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 22BF2B5DBFE
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 16:33:43 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 03080-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 19:33:36 +0000 (UTC)
X-Greylist: delayed 06:42:42.668459 by SQLgrey-1.7.6
Received: from mail-gx0-f178.google.com (mail-gx0-f178.google.com
	[209.85.161.178])
	by mail.postgresql.org (Postfix) with ESMTP id A719EB5D80A
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 16:33:36 -0300 (ADT)
Received: by gxk8 with SMTP id 8so326325gxk.37
	for <pgsql-performance@postgresql.org>;
	Thu, 08 Sep 2011 12:33:36 -0700 (PDT)
Received: by 10.91.69.33 with SMTP id w33mr1079953agk.112.1315510416009;
	Thu, 08 Sep 2011 12:33:36 -0700 (PDT)
Received: from AnibalVAIO ([200.85.32.68])
	by mx.google.com with ESMTPS id j11sm2049988ang.5.2011.09.08.12.33.32
	(version=TLSv1/SSLv3 cipher=OTHER);
	Thu, 08 Sep 2011 12:33:33 -0700 (PDT)
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>
	<012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
	<4E68AE600200002500040F47@gw.wicourts.gov>
In-Reply-To: <4E68AE600200002500040F47@gw.wicourts.gov>
Subject: Re: how delete/insert/update affects select	 performace?
Date: Thu, 8 Sep 2011 15:34:00 -0400
Message-ID: <008001cc6e5e$44737420$cd5a5c60$@devshock.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQHYrwFqiugYygNYi1ErgOQqHWWRlwF7ZGtRALkg6ZgB5VikY5UKjsfQ
Content-Language: es-py
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201109/81
X-Sequence-Number: 44857

>On the other hand, if you have very many rows where enabled is not 'T', and
you are generally searching for where enabled = 'T', you might want a
partial index (an index with a WHERE clause in its definition).  If >enabled
only has two states, you will probably get better performance using a
boolean column.

Maybe 1% or 2% are enabled='F' all others are 'T'

Another question Kevin (thanks for your time)

When an insert/update occur, the index is "reindexed" how index deals with
new or deleted rows.

Whay happened with select, it wait that index "reindex" or rebuild or
something? Or just select view another "version" of the table?

Thanks




-----Mensaje original-----
De: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] 
Enviado el: jueves, 08 de septiembre de 2011 01:01 p.m.
Para: Anibal David Acosta; pgsql-performance@postgresql.org
CC: 'Tomas Vondra'
Asunto: RE: [PERFORM] how delete/insert/update affects select performace?

"Anibal David Acosta" <aa@devshock.com> wrote:
 
> Tthe reason I add the enabled column to index is because a select 
> won't need to read the table to get this value
 
That's not true in PostgreSQL, although there is an effort to support that
optimization, at least to some degree.  In all current versions of
PostgreSQL, it will always need to read the heap to determine whether the
index entry is pointing at a version of the row which is visible to your
transaction.  Adding the enabled column to an index will prevent faster HOT
updates to that column.
 
> My select is : exists(select * from table where account_id=X and 
> service_id=Y and enabled='T')
 
On the other hand, if you have very many rows where enabled is not 'T', and
you are generally searching for where enabled = 'T', you might want a
partial index (an index with a WHERE clause in its definition).  If enabled
only has two states, you will probably get better performance using a
boolean column.
 
-Kevin




From Kevin.Grittner@wicourts.gov Thu Sep  8 19:52:54 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id C472EB5DBCB
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 16:52:54 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 39560-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 19:52:46 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 2BEFFB5DC09
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 16:52:45 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Thu, 08 Sep 2011 14:52:44 -0500
Message-Id: <4E68D6BB0200002500040F8D@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Thu, 08 Sep 2011 14:52:43 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Anibal David Acosta" <aa@devshock.com>,
 <pgsql-performance@postgresql.org>
Cc: "'Tomas Vondra'" <tv@fuzzy.cz>
Subject: Re: how delete/insert/update affects select
	 performace?
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
	<4E688FFC0200002500040F10@gw.wicourts.gov>
	<012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
	<4E68AE600200002500040F47@gw.wicourts.gov>
	<008001cc6e5e$44737420$cd5a5c60$@devshock.com>
In-Reply-To: <008001cc6e5e$44737420$cd5a5c60$@devshock.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504
X-Spam-Level: 
X-Archive-Number: 201109/82
X-Sequence-Number: 44858

"Anibal David Acosta" <aa@devshock.com> wrote:
 
> Maybe 1% or 2% are enabled='F' all others are 'T'
 
Then an index on this column is almost certainly going to be
counter-productive.  The only index on this column which *might*
make sense is WHERE enabled = 'F', and only if you run queries for
that often enough to outweigh the added maintenance cost.  If it's
always one of those two values, I would use boolean (with NOT NULL
if appropriate).
 
> When an insert/update occur, the index is "reindexed" how index
> deals with new or deleted rows.
 
Ignoring details of HOT updates, where less work is done if no
indexed column is updated and there is room for the new version of
the row (tuple) on the same page, an UPDATE is almost exactly like a
DELETE and an INSERT in the same transaction.  A new tuple (from an
INSERT or UPDATE) is added to the index(es), and if you query
through the index, it will see entries for both the old and new
versions of the row; this is why it must visit both versions -- to
check tuple visibility.  Eventually the old tuples and their index
entries are cleaned up through a "vacuum" process (autovacuum or an
explicit VACUUM command).  Until then queries do extra work visiting
and ignoring the old tuples.  (That is why people who turn off
autovacuum almost always regret it later.)
 
> Whay happened with select, it wait that index "reindex" or rebuild
> or something? Or just select view another "version" of the table?
 
The new information is immediately *added*, but there may be other
transactions which should still see the old state of the table, so
cleanup of old tuples and their index entries must wait for those
transactions to complete.
 
See this for more information:
 
http://www.postgresql.org/docs/9.0/interactive/mvcc.html
 
-Kevin



From greg@2ndQuadrant.com Fri Sep  9 01:28:48 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id E3C1AB5DBFC
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 22:28:48 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 71395-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  9 Sep 2011 01:28:42 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.186])
	by mail.postgresql.org (Postfix) with ESMTP id 4FEF3B5DBDA
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 22:28:42 -0300 (ADT)
Received: from [192.168.1.7] (mail.highperformancepostgresql.com
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis)
	id 0MCx2B-1QtH3a2WmJ-009R9G; Fri, 09 Sep 2011 03:28:42 +0200
Message-ID: <4E696BC8.3030905@2ndQuadrant.com>
Date: Thu, 08 Sep 2011 21:28:40 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110818 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: how delete/insert/update affects select	 performace?
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
	<4E688FFC0200002500040F10@gw.wicourts.gov>
	<012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
In-Reply-To: <012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: V02:K0:n7UVJuvDlD5RIU+S1Hjf8n8L6AFx6ASyAX0pAvq0pjA
	NopYeU6N/82ke6ebztkZOWdGxbtJ7cXUI0Cued3QOLbiYjCh7j
	s7YP58EKLKZU8tFBA1OiFdqahfS7hR7S+39JJ50L54P/QcpMSF
	wx1UFtn5zrKtbrH1r3UY8oVxlYVEyD4u3eauJhjJnopR+VCNry
	6LmCCnEBP4fdjZRc+WUr+g47UjkWXqMPEQlZ2AOPTJVR3sk7IN
	hVYd8/al1oeFLlhc0dXcAVdPVC3VL4B/YtmWuLvKv3fmlFO+eg
	jPYo+E8sdcMp1wMLD7ABhE8bIoe50/Tcxzo73++FrWQfPJrN0l
	zCPESCtgqrbNEhJbf8NI=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201109/85
X-Sequence-Number: 44861

On 09/08/2011 12:40 PM, Anibal David Acosta wrote:
> Postgres 9.0 on windows server 2008 r2
> HW is a dell dual processor with 16gb of ram .
>    

The general guidelines for Windows servers such as 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server recommend 
a fairly small setting for the shared_buffers parameters on Windows--no 
more than 512MB.  That makes your server a bit less likely to run in the 
nasty checkpoint spike issues Kevin was alluding to.  I don't think 
we've seen any reports of that on Windows.  The problem is worst on Linux.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us




From aa@devshock.com Fri Sep  9 02:42:03 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id A55CDB5DC03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  8 Sep 2011 23:42:03 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 21224-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  9 Sep 2011 02:41:56 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com
	[209.85.213.174])
	by mail.postgresql.org (Postfix) with ESMTP id 7CF9EB5DC01
	for <pgsql-performance@postgresql.org>;
	Thu,  8 Sep 2011 23:41:56 -0300 (ADT)
Received: by yxp4 with SMTP id 4so1048632yxp.19
	for <pgsql-performance@postgresql.org>;
	Thu, 08 Sep 2011 19:41:56 -0700 (PDT)
Received: by 10.101.147.31 with SMTP id z31mr1399255ann.12.1315535783237;
	Thu, 08 Sep 2011 19:36:23 -0700 (PDT)
Received: from AnibalVAIO (host-1-30.b9.cvc.com.py [186.2.200.30])
	by mx.google.com with ESMTPS id 8sm3195305anv.24.2011.09.08.19.36.19
	(version=TLSv1/SSLv3 cipher=OTHER);
	Thu, 08 Sep 2011 19:36:21 -0700 (PDT)
From: "Anibal David Acosta" <aa@devshock.com>
To: "'Greg Smith'" <greg@2ndQuadrant.com>, <pgsql-performance@postgresql.org>
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
	<4E688FFC0200002500040F10@gw.wicourts.gov>
	<012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
	<4E696BC8.3030905@2ndQuadrant.com>
In-Reply-To: <4E696BC8.3030905@2ndQuadrant.com>
Subject: Re: how delete/insert/update affects select	 performace?
Date: Thu, 8 Sep 2011 22:36:47 -0400
Message-ID: <000501cc6e99$5484c220$fd8e4660$@devshock.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQHYrwFqiugYygNYi1ErgOQqHWWRlwF7ZGtRALkg6ZgB+MkIJ5UKaJ/Q
Content-Language: es-py
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201109/86
X-Sequence-Number: 44862

Even if I have a server with 16GB of ram, I must set the shared_buffer to
512MB on windows?

In the wiki page they talk about 1/4 of ram, in my case that represent a
shared_buffer = 4GB, that is incorrect?

I have 8 GB of ram for each processor, each processor is a quad core with
hyperthreading, that means 16 "processors" o something like that. Windows
show 16 in task manager.

If I can't configure more than 512MB of shared_buffer all other RAM is
unnecessary?

Thanks for your time.



-----Mensaje original-----
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] En nombre de Greg Smith
Enviado el: jueves, 08 de septiembre de 2011 09:29 p.m.
Para: pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] how delete/insert/update affects select performace?

On 09/08/2011 12:40 PM, Anibal David Acosta wrote:
> Postgres 9.0 on windows server 2008 r2 HW is a dell dual processor 
> with 16gb of ram .
>    

The general guidelines for Windows servers such as
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server recommend a
fairly small setting for the shared_buffers parameters on Windows--no more
than 512MB.  That makes your server a bit less likely to run in the nasty
checkpoint spike issues Kevin was alluding to.  I don't think we've seen any
reports of that on Windows.  The problem is worst on Linux.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




From Kevin.Grittner@wicourts.gov Fri Sep  9 12:04:44 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 83BE4B5DC35
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  9 Sep 2011 09:04:44 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 87136-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  9 Sep 2011 12:04:38 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id E40D3B5DBDA
	for <pgsql-performance@postgresql.org>;
	Fri,  9 Sep 2011 09:04:37 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Fri, 09 Sep 2011 07:04:36 -0500
Message-Id: <4E69BA830200002500040FD2@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Fri, 09 Sep 2011 07:04:35 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <greg@2ndQuadrant.com>,<aa@devshock.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: how delete/insert/update affects select
	 performace?
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504
X-Spam-Level: 
X-Archive-Number: 201109/87
X-Sequence-Number: 44863

"Anibal David Acosta"  wrote:
 
>> The general guidelines for Windows servers such as
>> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>> recommend a fairly small setting for the shared_buffers parameters
>> on Windows--no more than 512MB.
 
> Even if I have a server with 16GB of ram, I must set the
> shared_buffer to 512MB on windows?
> 
> In the wiki page they talk about 1/4 of ram, in my case that
> represent a shared_buffer = 4GB, that is incorrect?
 
There's an effective maximum, depending on the platform.  On Linux
that seems to be somewhere in the 8GB to 10GB.  On Windows it is much
less.
 
> If I can't configure more than 512MB of shared_buffer all other RAM
> is unnecessary?
 
The RAM not used for other purposes is used by the OS for caching. 
PostgreSQL goes through that cache, so the extra memory will be used;
it's a question of what balance between PostgreSQL buffers and OS
cache gives the best performance.
 
-Kevin