Re: 8.x index insert performance

At 09:43 AM 11/14/2005, Kelly Burkhart wrote:
>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.
Perhaps a small schema change would help? Instead of having the
order state transitions explicitly listed in the table, why not
create two new tables; 1 for created orders and 1 for terminated
orders. When an order is created, its ord_id goes into the
CreatedOrders table. When an order is terminated, its ord_id is
added to the TerminatedOrders table and then deleted from the
CreatedOrders table.

Downsides to this approach are some extra complexity and that you
will have to make sure that system disaster recovery includes making
sure that no ord_id appears in both the CreatedOrders and
TerminatedOrdes tables. Upsides are that the insert problem goes
away and certain kinds of accounting and inventory reports are now
easier to create.

Ron