X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 88270475E77 for ; Fri, 25 Apr 2003 12:56:08 -0400 (EDT) Received: from flake.decibel.org (flake.decibel.org [66.93.216.19]) by postgresql.org (Postfix) with SMTP id 70615474E42 for ; Fri, 25 Apr 2003 12:56:07 -0400 (EDT) Received: (qmail 13733 invoked by uid 1001); 25 Apr 2003 16:56:13 -0000 Date: Fri, 25 Apr 2003 11:56:13 -0500 From: "Jim C. Nasby" To: Hannu Krosing Cc: Tom Lane , pgsql-performance@postgresql.org Subject: Re: More tablescanning fun Message-ID: <20030425115613.E66185@flake.decibel.org> Reply-To: jim@nasby.net References: <20030424183817.A66185@flake.decibel.org> <3660.1051228710@sss.pgh.pa.us> <20030424235924.B66185@flake.decibel.org> <1051288086.1765.95.camel@fuji.krosing.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5.1i In-Reply-To: <1051288086.1765.95.camel@fuji.krosing.net>; from hannu@tm.ee on Fri, Apr 25, 2003 at 07:28:06PM +0300 X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net X-Spam-Status: No, hits=-38.6 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,SIGNATURE_SHORT_SPARSE, USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/211 X-Sequence-Number: 1717 On Fri, Apr 25, 2003 at 07:28:06PM +0300, Hannu Krosing wrote: > I have been pondering if keeping pages half-empty (or even 70% empty) > could solve both clustering problems and longish updates for much data. > > If we could place the copy in the same page than original, most big > updates would be possible by one sweep of disk heads and also clustering > order would be easier to keep if pages were kept intentionally half > empty. > > So "VACUUM FULL 65% EMPTY;" could make sense ? That's actually a recommended practice, at least for sybase when you're using a clustered index, depending on what you're using it for. If you cluster a table in such a way that inserts will happen across the entire table, you'll actually end up with a fillratio (amount of data v. empty space on a page) of 75% over time, because of page splits. When sybase goes to insert, if it can't find room on the page it needs to insert into (keep in mind this is a clustered table, so a given row *must* go into a given position), it will split that single page into two pages, each of which will then have a fillratio of 50%. Of course they'll eventually approach 100%, so the average fill ratio across all pages for the table would be 75%. I'm not familiar enough with pgsql's guts to know how big an impact updates across pages are, or if they even happen often at all. If you're not maintaining a clustered table, couldn't all updates just occur in-place? Or are you thinking of the case where you have a lot of variable-length stuff? -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"