Re: performance problems inserting random vals in index

On Mon, Apr 19, 2010 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Leonardo F <m_lists@yahoo.it> writes:
>> I have a simple table that has indexes on 2 integer columns.
>> Data is inserted very often (no updates, no deletes, just inserts):
>> at least 4000/5000 rows per second.
>> The input for the 2 indexed columns is very random.
>
>> Everything is "fine" for the first 10-20M rows; after that, performance
>> gets worse and worse, and by 50M rows I can't insert more than
>> 1500 rows per second.
>
>> How can I improve it?
>
> Does it help to reindex the index at that point?
>
>> 1) the table is already partitioned; at the moment it's based on
>> 30 partitions. What kind of "select" performance impact would I
>> hit going to 3000 partitions?
>
> Bad.  The partitioning code isn't designed to scale beyond a few dozen
> partitions.

On a few very narrow applications I've gotten good performance in the
low hundreds.  After that things fall apart quickly.