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

Re: performance problems inserting random vals in index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Leonardo F <m_lists(at)yahoo(dot)it>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: performance problems inserting random vals in index
Date: 2010-04-19 17:04:30
Message-ID: 17147.1271696670@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Leonardo F <m_lists(at)yahoo(dot)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.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Scott MarloweDate: 2010-04-19 17:06:18
Subject: Re: performance problems inserting random vals in index
Previous:From: Vasiliy G TolstovDate: 2010-04-19 16:58:42
Subject: Re: best solution to backup full user databse

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