At 09:43 AM 11/14/2005, Kelly Burkhart wrote:
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
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.
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.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly