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: <22258.1131750131@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>
	<1131749305.14024.32.camel@krb06.tradebot.com>
	<22258.1131750131@sss.pgh.pa.us>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Mon, 14 Nov 2005 08:43:30 -0600
Message-Id: <1131979410.14024.49.camel@krb06.tradebot.com>
Mime-Version: 1.0

On Fri, 2005-11-11 at 18:02 -0500, Tom Lane wrote:
> > 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.
> 
> I conjecture that the problem areas represent places where the key
> sequence is significantly "more random" than it is elsewhere.  Hard
> to be more specific than that though.
> 

OK, I understand the pattern now.

My two tables hold orders, and order state transitions.  Most orders
have two transitions: creation and termination.  The problem happens
when there is a significant number of orders where termination is
happening a long time after creation, causing order_transition rows with
old ord_id values to be inserted.

This is valid, so I have to figure out a way to accomodate it.

You mentioned playing with checkpointing and bgwriter earlier in this
thread.  I experimented with the bgwriter through the weekend, but I
don't have a good idea what sensible parameter changes are...

Re: checkpointing, currently my checkpoints are happening every 5
minutes (if I turn on fsync, the graph shows checkpoints dramatically).
If I increase the checkpoint_timeout, could that be beneficial?  Or
would I just have more time between larger spikes? 

-K