Re: 8.x index insert performance

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