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 C6F275E46CF for ; Sat, 28 Aug 2004 05:15:23 -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 18404-08 for ; Sat, 28 Aug 2004 08:15:15 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id EF59F5E46D1 for ; Sat, 28 Aug 2004 05:15:14 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1C0yMY-0003qU-00; Sat, 28 Aug 2004 04:15:02 -0400 To: Bruce Momjian Cc: Gaetano Mendola , Tom Lane , "pgsql-performance@postgresql.org" Subject: Re: Equivalent praxis to CLUSTERED INDEX? References: <200408280354.i7S3slb21543@candle.pha.pa.us> In-Reply-To: <200408280354.i7S3slb21543@candle.pha.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 28 Aug 2004 04:15:01 -0400 Message-ID: <87k6vjllve.fsf@stark.xeocode.com> Lines: 30 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 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/409 X-Sequence-Number: 8045 Bruce Momjian writes: > > Don't you think this will permit also to avoid extra disk seek and cache > > invalidation? If you are updating the row (0,1) I think is less expensive > > put the new version in (0,2) instead of thousand line far from that point. Well if the other buffer "a thousand lines far from that point" is already in ram, then no, there's no penalty at the time for storing it there. However it destroys the clustering, which was the original point. > It would, but does that outweigh the decreased I/O by having things more > densely packed? I would think not. Well the dense packing is worth something. But so is the clustering. There's definitely a trade-off. I always found my largest tables are almost always insert-only tables anyways. So in Oracle I would have pctused 100 pctfree 0 on them and get the performance gain. The tables that would benefit from this would be tables always accessed by indexes in index scans of more than one record. The better the clustering the fewer pages the index scan would have to read in. If the data took 10% more space but the index scan only needs 1/4 as many buffers it could be a big net win. -- greg