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

Re: Insert performance vs Table size

From: Jacques Caron <jc(at)directinfos(dot)com>
To: "Praveen Raja" <praveen(dot)raja(at)netlight(dot)se>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Insert performance vs Table size
Date: 2005-06-27 12:05:03
Message-ID: 6.2.0.14.0.20050627135541.055202c0@wheresmymailserver.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

At 13:50 27/06/2005, Praveen Raja wrote:
>Just to clear things up a bit, the scenario that I'm interested in is a
>table with a large number of indexes on it (maybe 7-8).

If you're after performance you'll want to carefully consider which indexes 
are really useful and/or redesign your schema so that you can have less 
indexes on that table. 7 or 8 indexes is quite a lot, and that really has a 
cost.

>  In this scenario
>other than the overhead of having to maintain the indexes (which I'm
>guessing is the same regardless of the size of the table)

Definitely not: indexes grow with the size of the table. Depending on what 
columns you index (and their types), the indexes may be a fraction of the 
size of the table, or they may be very close in size (in extreme cases they 
may even be larger). With 7 or 8 indexes, that can be quite a large volume 
of data to manipulate, especially if the values of the columns inserted can 
span the whole range of the index (rather than being solely id- or 
time-based, for instance, in which case index updates are concentrated in a 
small area of each of the indexes), as this means you'll need to have a 
majority of the indexes in RAM if you want to maintain decent performance.

>does the size of the table play a role in determining insert performance 
>(and I mean
>only insert performance)?

In this case, it's really the indexes that'll cause you trouble, though 
heavily fragmented tables (due to lots of deletes or updates) will also 
incur a penalty just for the data part of the inserts.

Also, don't forget the usual hints if you are going to do lots of inserts:
- batch them in large transactions, don't do them one at a time
- better yet, use COPY rather than INSERT
- in some situations, you might be better of dropping the indexes, doing 
large batch inserts, then re-creating the indexes. YMMV depending on the 
existing/new ratio, whether you need to maintain indexed access to the 
tables, etc.
- pay attention to foreign keys

Jacques.



In response to

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2005-06-27 12:40:29
Subject: Re: Performance - moving from oracle to postgresql
Previous:From: Praveen RajaDate: 2005-06-27 11:50:13
Subject: Re: Insert performance vs Table size

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