Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 76EA09FBC85 for ; Tue, 10 Jul 2007 08:23:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 13367-05 for ; Tue, 10 Jul 2007 08:23:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from oxford.xeocode.com (unknown [62.232.55.118]) by postgresql.org (Postfix) with ESMTP id D680B9FBCC7 for ; Tue, 10 Jul 2007 08:23:41 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.67) (envelope-from ) id 1I8DoS-0000ze-Q6; Tue, 10 Jul 2007 12:23:24 +0100 From: Gregory Stark To: "Tom Lane" Cc: "Heikki Linnakangas" , "Jaime Casanova" , "Jim C. Nasby" , "Bruce Momjian" , "PostgreSQL-patches" Subject: Re: Maintaining cluster order on insert In-Reply-To: <457.1184023772@sss.pgh.pa.us> (Tom Lane's message of "Mon, 09 Jul 2007 19:29:32 -0400") Organization: EnterpriseDB References: <200705152223.l4FMNJa17969@momjian.us> <464A33AB.1050401@enterprisedb.com> <20070516003810.GI20707@nasby.net> <464B15EF.4000808@enterprisedb.com> <464D96D6.1030608@enterprisedb.com> <457.1184023772@sss.pgh.pa.us> X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.patches" 2010) Date: Tue, 10 Jul 2007 12:23:23 +0100 Message-ID: <87odikzems.fsf@oxford.xeocode.com> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/125 X-Sequence-Number: 3123 "Tom Lane" writes: > * delete a random 2% of the table > * vacuum to recover space > * insert a random 2% of the table > * select (about) 1000 consecutively-numbered rows > * select all the rows (this is just a cross check that the > number of rows isn't changing too much) > > What you would hope to see as the benefit of the patch is that the time > for the range SELECT degrades more slowly as more of the table is > replaced. Ignoring the first SELECT as being a startup transient, it > looks like HEAD degrades from about 3 msec to 6 msec over 10 iterations > (20% replacement of the table), whereas with the patch it's about 3 msec > to about 4 and a half. However, the INSERT steps went from around 20 > sec each to about twice that. I would suggest: a) continuing the test until you have 100% replacement. b) defining the tables with a fill-factor large enough to hold at least one extra tuple c) considering this patch alongside GII where it is especially important. It's pretty serious what you're suggesting since it means that we'll basically never have a real cluster feature. I would sure hope we're missing something and there's a way to make this work usefully. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com