X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id BA2E911F6087 for ; Sat, 29 Apr 2006 09:53:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 39545-05 for ; Sat, 29 Apr 2006 09:53:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.meridiascapital.com (exchange.meridiascapital.com [65.90.55.78]) by postgresql.org (Postfix) with ESMTP id E13B211F6091 for ; Sat, 29 Apr 2006 09:53:01 -0300 (ADT) Received: from 192.168.19.130 ([192.168.19.130]) by atherton-ex-01.meridias.local ([192.168.8.78]) with Microsoft Exchange Server HTTP-DAV ; Sat, 29 Apr 2006 12:52:55 +0000 Received: from wjlnotebook by exchange.meridiascapital.com; 29 Apr 2006 06:52:55 -0600 Subject: Re: questions on toast tables From: Warren Little To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <15655.1146254635@sss.pgh.pa.us> References: <1146240597.9528.17.camel@wlittle.meridias.local> <15655.1146254635@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Sat, 29 Apr 2006 06:52:55 -0600 Message-Id: <1146315175.4995.15.camel@wjlnotebook> Mime-Version: 1.0 X-Mailer: Evolution 2.6.1 (2.6.1-1.fc5.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/347 X-Sequence-Number: 21516 I am now a little confused. I ran the following with all but localhost connections disabled vacuumdb --full --verbose -t casedocument -d tigris > vacfull.log 2>&1 which produced the following output: INFO: vacuuming "public.casedocument" INFO: "casedocument": found 0 removable, 39663 nonremovable row versions in 852 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 138 to 1953 bytes long. There were 1855 unused item pointers. Total free space (including removable row versions) is 65168 bytes. 0 pages are or will become empty, including 0 at the end of the table. 90 pages containing 14644 free bytes are potential move destinations. CPU 0.02s/0.00u sec elapsed 0.24 sec. INFO: index "copycasedoc_pkey" now contains 39663 row versions in 387 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.56 sec. INFO: "casedocument": moved 0 row versions, truncated 852 to 852 pages DETAIL: CPU 0.00s/0.01u sec elapsed 0.03 sec. INFO: vacuuming "pg_toast.pg_toast_24216115" INFO: "pg_toast_24216115": found 0 removable, 23125332 nonremovable row versions in 5781284 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 45 to 2030 bytes long. There were 1 unused item pointers. Total free space (including removable row versions) is 185874460 bytes. 0 pages are or will become empty, including 0 at the end of the table. 24019 pages containing 24668944 free bytes are potential move destinations. CPU 217.21s/16.97u sec elapsed 1745.04 sec. INFO: index "pg_toast_24216115_index" now contains 23125332 row versions in 94046 pages DETAIL: 0 index row versions were removed. 1427 index pages have been deleted, 1427 are currently reusable. CPU 4.60s/1.05u sec elapsed 49.34 sec. INFO: "pg_toast_24216115": moved 0 row versions, truncated 5781284 to 5781284 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Not sure if I'm reading the output correctly, but it appears no rows in the toast table were removed. What else could be holding onto the data in "pg_toast_24216115" ??? On Fri, 2006-04-28 at 16:03 -0400, Tom Lane wrote: > Warren Little writes: > > 3) I know that once upon a time the table had a bytea column, but that > > was dropped. Do I need to do a full vacuum on that table to get rid of > > the related toast data? > > Yup. (I take it it was a pretty darn bulky bytea column, too) > > regards, tom lane -- Warren Little Chief Technology Office Meridias Capital Inc 1018 W Atherton Dr SLC, UT 84123 ph 866.369.7763