Skip site navigation (1) Skip section navigation (2)

Re: index performance on large tables with update and insert

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: Jessica Richard <rjessil(at)yahoo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: index performance on large tables with update and insert
Date: 2008-05-23 16:35:17
Message-ID: 20080523123517.6305fbde@mha-laptop.hagander.net (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: Andrzej ZawadzkiDate: 2008-05-24 16:39:16
Subject: Re: Quad Xeon or Quad Opteron?
Previous:From: Kevin GrittnerDate: 2008-05-23 15:15:29
Subject: IBM ServRAID-MR10M / LSI1078ROC advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group