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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2005-06-27 12:40:29 | Re: Performance - moving from oracle to postgresql |
Previous Message | Praveen Raja | 2005-06-27 11:50:13 | Re: Insert performance vs Table size |