X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BA3ACDBC41 for ; Thu, 24 Nov 2005 15:06:21 -0400 (AST) 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 86120-02 for ; Thu, 24 Nov 2005 19:06:21 +0000 (GMT) X-Greylist: delayed 02:52:28.643381 by SQLgrey- Received: from ahab.skio.peachnet.edu (ahab.skio.peachnet.edu [168.20.254.11]) by svr1.postgresql.org (Postfix) with ESMTP id 19FE9DBBB8 for ; Thu, 24 Nov 2005 15:06:18 -0400 (AST) Received: from skio.peachnet.edu (sprat.skio.peachnet.edu [168.20.2.26]) by ahab.skio.peachnet.edu (8.12.10/8.12.10) with ESMTP id jAOJ68hl018324 for ; Thu, 24 Nov 2005 14:06:09 -0500 (EST) Message-ID: <43860F20.5080709@skio.peachnet.edu> Date: Thu, 24 Nov 2005 14:06:08 -0500 From: Colton Smith User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:1.4) Gecko/20040414 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: pgstattuple, vacuum and free_space Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-SkIO-MailScanner-Information: Please contact the ISP for more information X-SkIO-MailScanner: Found to be clean X-MailScanner-From: smith@skio.peachnet.edu X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/314 X-Sequence-Number: 19687 Hi: I did the following after installing the pgstattuple contrib code: select * from pgstattuple('wind'); -[ RECORD 1 ]------+---------- table_len | 224854016 tuple_count | 1492601 tuple_len | 207535124 tuple_percent | 92.3 dead_tuple_count | 11569 dead_tuple_len | 1361848 dead_tuple_percent | 0.61 free_space | 3311416 free_percent | 1.47 vacuum verbose wind; INFO: vacuuming "public.wind" INFO: index "wind_pkey" now contains 1492601 row versions in 4152 pages DETAIL: 11569 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.56s/1.77u sec elapsed 36.73 sec. INFO: index "wind_measurement_date_index" now contains 1492601 row versions in 4156 pages DETAIL: 11569 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.43s/1.83u sec elapsed 35.90 sec. INFO: "wind": removed 11569 row versions in 371 pages DETAIL: CPU 0.03s/0.03u sec elapsed 2.38 sec. INFO: "wind": found 11569 removable, 1492601 nonremovable row versions in 27448 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 2.01s/3.95u sec elapsed 79.39 sec. VACUUM select * from pgstattuple('wind'); -[ RECORD 1 ]------+---------- table_len | 224854016 tuple_count | 1492601 tuple_len | 207535124 tuple_percent | 92.3 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 4673256 free_percent | 2.08 My question: when you vacuum a table and generate 'free_space', who is allowed to consume this 'free_space'? Is it released to the OS for general use? Or is it reserved just for the database? If the latter, is it reserved just for 'wind' (in this case)? Thanks again!