Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 7BC7B2E0084
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Fri, 23 May 2008 10:28:43 -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 54093-01-7
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Fri, 23 May 2008 10:28:21 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from web56401.mail.re3.yahoo.com (web56401.mail.re3.yahoo.com
	[216.252.111.80])
	by developer.postgresql.org (Postfix) with SMTP id 55F472E0035
	for <pgsql-performance@postgresql.org>;
	Fri, 23 May 2008 10:27:59 -0300 (ADT)
Received: (qmail 62961 invoked by uid 60001); 23 May 2008 13:21:17 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=UHWkpgErS9ZiHlDuwNAS1wlB5lFp2LOCCLAfOKzbunVCK8TqTGDnqaBkgzhjw7WfEQJtl6sW7zWuivItMq3C9OP+CyzinQjpVBf/ONtjekGSMVuGtF7/irff/zlzngazq8yZbgi1he579rqVjoQrJz1fQUdB8Sooao70SEUwwC0=;
Received: from [198.119.134.32] by web56401.mail.re3.yahoo.com via HTTP;
	Fri, 23 May 2008 06:21:17 PDT
X-Mailer: YahooMailRC/975.41 YahooMailWebService/0.7.199
Date: Fri, 23 May 2008 06:21:17 -0700 (PDT)
From: Jessica Richard <rjessil@yahoo.com>
Subject: index performance on large tables with update and insert
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-737630713-1211548877=:54805"
Message-ID: <334290.54805.qm@web56401.mail.re3.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200805/319
X-Sequence-Number: 30287

--0-737630713-1211548877=:54805
Content-Type: text/plain; charset=us-ascii

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?

Thanks,
Jessica



      
--0-737630713-1211548877=:54805
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div><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>
<br>
Thanks,<br>
Jessica<br></div></div></div><br>

      </body></html>
--0-737630713-1211548877=:54805--