X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 83F439DC98D for ; Tue, 14 Feb 2006 23:33:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50385-05 for ; Tue, 14 Feb 2006 23:33:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 64D699DC87F for ; Tue, 14 Feb 2006 23:33:37 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1F3XeHj025690; Tue, 14 Feb 2006 22:33:40 -0500 (EST) To: "Mark Liberman" cc: pgsql-admin@postgresql.org Subject: Re: Feedback on auto-pruning approach In-reply-to: <9D938282F8C6EE43B748B910386DE93E0138B3F5@srvgpimail1.GPI.local> References: <9D938282F8C6EE43B748B910386DE93E0138B3F5@srvgpimail1.GPI.local> Comments: In-reply-to "Mark Liberman" message dated "Tue, 14 Feb 2006 17:41:47 -0800" Date: Tue, 14 Feb 2006 22:33:40 -0500 Message-ID: <25689.1139974420@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.109 required=5 tests=[AWL=0.109] X-Spam-Score: 0.109 X-Spam-Level: X-Archive-Number: 200602/183 X-Sequence-Number: 20604 "Mark Liberman" writes: > where c.oid = a.attrelid > and c.relnamespace = n.oid > and c.relkind in ('i','r') > and a.atttypid not in (26,27,28,29) > group by c.relname) by_table; > A few notes: > 1) I have used 32 bytes for the row tuple header overhead and 4 bytes = > for index tuple overhead > 2) The attribute types 26,27,28,29 are oid,tid,xid,cid - which, I = > believe are already counted in the row overhead You should not do it that way, because those are perfectly valid datatypes for user columns. Instead of the type test, check for attnum > 0. The "system columns" that represent row overhead items have attnum < 0. You might want to consider ignoring columns where attisdropped, too, though this is a bit of a judgment call since a dropped column might still be eating storage space. Another thing you could do is left-join to pg_stats and use ANALYZE's estimate of average column width where available, instead of hardwired guesses. Another important point is that this calculation is ignoring TOAST space ... do you have any columns wide enough to get toasted? > 1) I have found the 32 bytes overhead mentioned in a few places, but = > have not seen any specific reference to the byte overhead of an index = > header row. Does know the best number to use here for an assumption? 12 bytes (8-byte header + 4-byte line pointer). > 3) Has anyone solved this entire problem in another fashion (e.g. = > auto-pruning - only delete what's necessary to stay within limits). Have you looked at contrib/pgstattuple? regards, tom lane