pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (29+ messages)

Message-ID: <6.2.5.6.0.20051114104706.01dcf728@earthlink.net>
Permalink:  ../6.2.5.6.0.20051114104706.01dcf728@earthlink.net/
Also on:    postgresql.org/message-id/6.2.5.6.0.20051114104706.01dcf728@earthlink.net

 · 

reply

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