pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedindex performance
2+ messages / 2 participants
[nested] [flat]
* index performance
@ 2008-05-23 13:20 Jessica Richard <rjessil@yahoo.com>
2008-05-23 16:18 ` Re: index performance Chander Ganesan <chander@otg-nc.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jessica Richard @ 2008-05-23 13:20 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: index performance
2008-05-23 13:20 index performance Jessica Richard <rjessil@yahoo.com>
@ 2008-05-23 16:18 ` Chander Ganesan <chander@otg-nc.com>
0 siblings, 0 replies; 2+ messages in thread
From: Chander Ganesan @ 2008-05-23 16:18 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin
> 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-05-23 16:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-23 13:20 index performance Jessica Richard <rjessil@yahoo.com>
2008-05-23 16:18 ` Chander Ganesan <chander@otg-nc.com>
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