From: Ron <rjpeace@earthlink.net>
To: Kelly Burkhart <kelly@tradebotsystems.com>
To: pgsql-performance@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: 8.x index insert performance
Date: Mon, 14 Nov 2005 10:57:42 -0500
Message-ID: <6.2.5.6.0.20051114104706.01dcf728@earthlink.net> (raw)
In-Reply-To: <1131979410.14024.49.camel@krb06.tradebot.com>
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>
<1131979410.14024.49.camel@krb06.tradebot.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rjpeace@earthlink.net, kelly@tradebotsystems.com
Subject: Re: 8.x index insert performance
In-Reply-To: <6.2.5.6.0.20051114104706.01dcf728@earthlink.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox