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 1D576DB509 for ; Mon, 31 Oct 2005 17:18:47 -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 37899-03 for ; Mon, 31 Oct 2005 21:18:45 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D7F9DDB4E8 for ; Mon, 31 Oct 2005 17:18:45 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j9VLIlha019066; Mon, 31 Oct 2005 16:18:47 -0500 (EST) To: Kelly Burkhart Cc: mark@mark.mielke.cc, Merlin Moncure , pgsql-performance@postgresql.org Subject: Re: 8.x index insert performance In-reply-to: <1130792391.7026.55.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> Comments: In-reply-to Kelly Burkhart message dated "Mon, 31 Oct 2005 14:59:51 -0600" Date: Mon, 31 Oct 2005 16:18:47 -0500 Message-ID: <19065.1130793527@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006] X-Spam-Score: 0.006 X-Spam-Level: X-Archive-Number: 200510/485 X-Sequence-Number: 15253 Kelly Burkhart writes: > Ha! So I'm creating an index 98% full of nulls! Looks like this is > easily fixed with partial indexes. Still, though, it's not immediately clear why you'd be seeing a severe dropoff in insert performance after 50M rows. Even though there are lots of nulls, I don't see why they'd behave any worse for insert speed than real data. One would like to think that the insert speed would follow a nice O(log N) rule. Are you doing the inserts all in one transaction, or several? If several, could you get a gprof profile of inserting the same number of rows (say a million or so) both before and after the unexpected dropoff occurs? regards, tom lane