Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 263362E0060
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 23 May 2008 13:18:38 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 70769-03
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 23 May 2008 13:18:21 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp172.iad.emailsrvr.com (smtp172.iad.emailsrvr.com
	[207.97.245.172])
	by developer.postgresql.org (Postfix) with ESMTP id BF5BA2E002E
	for <pgsql-admin@postgresql.org>; Fri, 23 May 2008 13:18:21 -0300 (ADT)
Received: from relay7.relay.iad.mlsrvr.com (localhost [127.0.0.1])
	by relay7.relay.iad.mlsrvr.com (SMTP Server) with ESMTP id 59B5C1B4D82;
	Fri, 23 May 2008 12:18:20 -0400 (EDT)
Received: by relay7.relay.iad.mlsrvr.com (Authenticated sender:
	chander-AT-otg-nc.com) with ESMTP id 359731B461C;
	Fri, 23 May 2008 12:18:20 -0400 (EDT)
Message-ID: <4836EE4F.5060304@otg-nc.com>
Date: Fri, 23 May 2008 12:18:23 -0400
From: Chander Ganesan <chander@otg-nc.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: Jessica Richard <rjessil@yahoo.com>
CC: pgsql-admin@postgresql.org
Subject: Re: index performance
References: <501471.5203.qm@web56411.mail.re3.yahoo.com>
In-Reply-To: <501471.5203.qm@web56411.mail.re3.yahoo.com>
Content-Type: multipart/alternative;
	boundary="------------050203090709050206000801"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200805/197
X-Sequence-Number: 29281

This is a multi-part message in MIME format.
--------------050203090709050206000801
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


> I have a large table with about 2 million rows and it will keep growing...
>
> I need to do update/inserts, and select as well.
>
> An index will speed up the select, but it will slow down the updates.
>
> Are all Postgres indexes ordered? i.e., with every update, the index 
> pages will have to be physically reordered?
>
> Does Postgres have any kind of non-ordered indexes (like Syabse's 
> non-clustered index)?
>
> What is the common way to take care of the performance issue when you 
> have to do both update and select on the same large table?
You might consider table partitioning and enabling constraint exclusion. 

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Ask me about OTG's Expert On-Site & Public enrollment PostgreSQL, PostGIS, and other Open Source courses.


--------------050203090709050206000801
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<blockquote cite="mid:501471.5203.qm@web56411.mail.re3.yahoo.com"
 type="cite">
  <style type="text/css"><!-- DIV {margin:0px;} --></style>
  <div
 style="font-family: times new roman,new york,times,serif; font-size: 12pt;">
  <div>I have a large table with about 2 million rows and it will keep
growing...<br>
  <br>
I need to do update/inserts, and select as well.<br>
  <br>
An index will speed up the select, but it will slow down the updates.<br>
  <br>
Are all Postgres indexes ordered? i.e., with every update, the index
pages will have to be physically reordered?<br>
  <br>
Does Postgres have any kind of non-ordered indexes (like Syabse's
non-clustered index)?<br>
  <br>
What is the common way to take care of the performance issue when you
have to do both update and select on the same large table?<br>
  </div>
  </div>
</blockquote>
You might consider table partitioning and enabling constraint
exclusion.&nbsp; <br>
<pre class="moz-signature" cols="72">-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
919-463-0999/877-258-8987
<a class="moz-txt-link-freetext" href="http://www.otg-nc.com">http://www.otg-nc.com</a>
Ask me about OTG's Expert On-Site &amp; Public enrollment PostgreSQL, PostGIS, and other Open Source courses.
</pre>
</body>
</html>

--------------050203090709050206000801--