From: Chander Ganesan <chander@otg-nc.com>
To: Jessica Richard <rjessil@yahoo.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: index performance
Date: Fri, 23 May 2008 12:18:23 -0400
Message-ID: <4836EE4F.5060304@otg-nc.com> (raw)
In-Reply-To: <501471.5203.qm@web56411.mail.re3.yahoo.com>
References: <501471.5203.qm@web56411.mail.re3.yahoo.com>
> 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.
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-admin@postgresql.org
Cc: chander@otg-nc.com, rjessil@yahoo.com
Subject: Re: index performance
In-Reply-To: <4836EE4F.5060304@otg-nc.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