To: Kelly Burkhart <kelly@tradebotsystems.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 8.x index insert performance 
In-reply-to: <1131660117.7514.57.camel@krb06.tradebot.com> 
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD725@Herge.rcsinc.local>
	<20051031193547.GA3311@mark.mielke.cc>
	<18647.1130790629@sss.pgh.pa.us>
	<1130792391.7026.55.camel@krb06.tradebot.com>
	<19065.1130793527@sss.pgh.pa.us>
	<1130852029.7026.88.camel@krb06.tradebot.com>
	<24845.1130852730@sss.pgh.pa.us>
	<1131660117.7514.57.camel@krb06.tradebot.com>
Comments: In-reply-to Kelly Burkhart <kelly@tradebotsystems.com>
	message dated "Thu, 10 Nov 2005 16:01:57 -0600"
Date: Thu, 10 Nov 2005 19:13:28 -0500
Message-ID: <13167.1131668008@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Kelly Burkhart <kelly@tradebotsystems.com> writes:
> ...  A graph showing the performance
> characteristics is here:

> <http://kkcsm.net/pgcpy.jpg>

I hadn't looked at this chart till just now, but it sure seems to put a
crimp in my theory that you are running out of room to hold the indexes
in RAM.  That theory would predict that once you fall over the knee of
the curve, performance would get steadily worse; instead it gets
markedly worse and then improves a bit.  And there's another cycle of
worse-and-better around 80M rows.  I have *no* idea what's up with that.
Anyone?  Kelly, could there be any patterns in the data that might be
related?

The narrow spikes look like they are probably induced by checkpoints.
You could check that by seeing if their spacing changes when you alter
checkpoint_segments and checkpoint_timeout.  It might also be
entertaining to make the bgwriter parameters more aggressive to see
if you can ameliorate the spikes.

			regards, tom lane