Subject: Re: 8.x index insert performance
From: Kelly Burkhart <kelly@tradebotsystems.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <13167.1131668008@sss.pgh.pa.us>
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>
	<13167.1131668008@sss.pgh.pa.us>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Fri, 11 Nov 2005 16:48:25 -0600
Message-Id: <1131749305.14024.32.camel@krb06.tradebot.com>
Mime-Version: 1.0

On Thu, 2005-11-10 at 19:13 -0500, Tom Lane wrote:
> 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?

I modified my original program to insert generated, sequential data.
The following graph shows the results to be flat:

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

Thus, hardware is sufficient to handle predictably sequential data.
There very well could be a pattern in the data which could affect
things, however, I'm not sure how to identify it in 100K rows out of
100M.

If I could identify a pattern, what could I do about it?  Could I do
some kind of a reversible transform on the data?  Is it better to insert
nearly random values?  Or nearly sequential?


I now have an 8G and a 16G machine I'm loading the data into.  I'll
report back after that's done.

I also want to try eliminating the order_main table, moving fields to
the transition table.  This will reduce the number of index updates
significantly at the cost of some wasted space in the table...

-K