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 E234CDB68F for ; Tue, 1 Nov 2005 09:33: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 37492-09 for ; Tue, 1 Nov 2005 13:33:44 +0000 (GMT) Received: from tbmail.tradebot.com (Tradebot-Systems-1096753.cust-rtr.swbell.net [68.90.170.2]) by svr1.postgresql.org (Postfix) with ESMTP id 55B39DB68C for ; Tue, 1 Nov 2005 09:33:45 -0400 (AST) Received: from 192.168.200.29 ([192.168.200.29]) by tbmail.tradebot.com ([192.168.1.50]) with Microsoft Exchange Server HTTP-DAV ; Tue, 1 Nov 2005 13:33:49 +0000 Received: from krb06 by TBMAIL; 01 Nov 2005 07:33:49 -0600 Subject: Re: 8.x index insert performance From: Kelly Burkhart To: Tom Lane Cc: mark@mark.mielke.cc, Merlin Moncure , pgsql-performance@postgresql.org In-Reply-To: <19065.1130793527@sss.pgh.pa.us> 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> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Tue, 01 Nov 2005 07:33:49 -0600 Message-Id: <1130852029.7026.88.camel@krb06.tradebot.com> Mime-Version: 1.0 X-Mailer: Evolution 2.2.1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.021 required=5 tests=[AWL=0.020, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.021 X-Spam-Level: X-Archive-Number: 200511/4 X-Sequence-Number: 15261 On Mon, 2005-10-31 at 16:18 -0500, Tom Lane wrote: > 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? I'm doing the inserts via libpq copy. Commits are in batches of approx 15000 rows. I did a run last night after modifying the indexes and saw the same pattern. I'm dumping the database now and will modify my test program to copy data from the dump rather than purely generated data. Hopefully, this will allow me to reproduce the problem in a way that takes less time to set up and run. Tom, I'd be happy to profile the backend at several points in the run if you think that would be helpful. What compiler flags should I use? Current settings in Makefile.global are: CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing Should I change this to: CFLAGS = -g -pg -Wall ... Or should I leave the -O2 in? It may be weekend by the time I get this done. -K