X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8EA635E37D0 for ; Fri, 27 Aug 2004 16:43:52 -0300 (ADT) 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 32563-07 for ; Fri, 27 Aug 2004 19:43:47 +0000 (GMT) Received: from candle.pha.pa.us (candle.pha.pa.us [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id AC5615E46F2 for ; Fri, 27 Aug 2004 16:43:36 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id i7RJgoR03090; Fri, 27 Aug 2004 15:42:50 -0400 (EDT) From: Bruce Momjian Message-Id: <200408271942.i7RJgoR03090@candle.pha.pa.us> Subject: Re: Equivalent praxis to CLUSTERED INDEX? In-Reply-To: <87acwgml26.fsf@stark.xeocode.com> To: Greg Stark Date: Fri, 27 Aug 2004 15:42:50 -0400 (EDT) Cc: Adi Alurkar , pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL108 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/402 X-Sequence-Number: 8038 Greg Stark wrote: > > Bruce Momjian writes: > > > but is there any significant performance benefit to doing that which would > > offset the compaction advantage? > > Just as a side comment. Setting PCTFREE 0 PCTUSED 100 on tables that have no > updates on them has an astonishingly big effect on speed. So the penalty for > leaving some space free really is substantial. > > I think the other poster is right. Oracle really needs pctfree because of the > way it handles updates. Postgres doesn't really need as much because it > doesn't try to squeeze the new tuple in the space the old one took up. If it > doesn't fit on the page the worst that happens is it has to store it on some > other page, whereas oracle has to do its strange row chaining thing. Oracle also does that chain thing so moving updates to different pages might have more of an impact than it does on PostgreSQL. We have chains too but just for locking. Not sure on Oracle. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073