pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex performance on large tables with update and insert
2+ messages / 2 participants
[nested] [flat]
* index performance on large tables with update and insert
@ 2008-05-23 13:21 Jessica Richard <rjessil@yahoo.com>
2008-05-23 16:35 ` Re: index performance on large tables with update and insert Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 2+ messages in thread
From: Jessica Richard @ 2008-05-23 13:21 UTC (permalink / raw)
To: pgsql-performance
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 on large tables with update and insert
2008-05-23 13:21 index performance on large tables with update and insert Jessica Richard <rjessil@yahoo.com>
@ 2008-05-23 16:35 ` Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 2+ messages in thread
From: Magnus Hagander @ 2008-05-23 16:35 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-performance
Jessica Richard wrote:
> 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)?
All PostgreSQL indexes are like the non-clustered ones in Sybase or SQL
Server.
> 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?
Create the indexes you actually need to make the selects and updates
fast, just make sure you don't create any unnecessary ones. Usually,
your UPDATEs will also require indexes - only the INSERTs actually are
losing.
//Magnus
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-05-23 16:35 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:21 index performance on large tables with update and insert Jessica Richard <rjessil@yahoo.com>
2008-05-23 16:35 ` Magnus Hagander <magnus@hagander.net>
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