X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A1834D9BAD for ; Mon, 14 Nov 2005 11:57:49 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01813-01 for ; Mon, 14 Nov 2005 15:57:45 +0000 (GMT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by svr1.postgresql.org (Postfix) with ESMTP id BF982D9443 for ; Mon, 14 Nov 2005 11:57:47 -0400 (AST) Received: from smtpauth02.mail.atl.earthlink.net (smtpauth02.mail.atl.earthlink.net [209.86.89.62]) by svr2.postgresql.org (Postfix) with ESMTP id AAEBDF0C46 for ; Mon, 14 Nov 2005 15:57:52 +0000 (GMT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=dk20050327; d=earthlink.net; b=UuPDBMxSoiOxKdAWi+Oh5UpC1VnF7YhP7GL/9RBSNoi9/PfqZmzA4jPs9heWFD5w; h=Received:Message-Id:X-Mailer:Date:To:From:Subject:Cc:In-Reply-To:References:Mime-Version:Content-Type:X-ELNK-Trace:X-Originating-IP; Received: from [70.22.226.16] (helo=ron-6d52adff2a6.earthlink.net) by smtpauth02.mail.atl.earthlink.net with asmtp (Exim 4.34) id 1EbgiK-0002xb-6u; Mon, 14 Nov 2005 10:57:48 -0500 Message-Id: <6.2.5.6.0.20051114104706.01dcf728@earthlink.net> X-Mailer: QUALCOMM Windows Eudora Version 6.2.5.6 Date: Mon, 14 Nov 2005 10:57:42 -0500 To: Kelly Burkhart , pgsql-performance@postgresql.org From: Ron Subject: Re: 8.x index insert performance Cc: pgsql-performance@postgresql.org 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> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-ELNK-Trace: acd68a6551193be5d780f4a490ca69563f9fea00a6dd62bc3fdc3a5e2ac505c4707c912fb9379aed350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 70.22.226.16 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.479 X-Spam-Level: X-Archive-Number: 200511/224 X-Sequence-Number: 15481 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