From: Warren Little <warren.little@meridiascapital.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: questions on toast tables
Date: Sat, 29 Apr 2006 06:52:55 -0600
Message-ID: <1146315175.4995.15.camel@wjlnotebook> (raw)
In-Reply-To: <15655.1146254635@sss.pgh.pa.us>
References: <1146240597.9528.17.camel@wlittle.meridias.local>
<15655.1146254635@sss.pgh.pa.us>
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 <warren.little@meridiascapital.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: warren.little@meridiascapital.com, tgl@sss.pgh.pa.us
Subject: Re: questions on toast tables
In-Reply-To: <1146315175.4995.15.camel@wjlnotebook>
References: <1146240597.9528.17.camel@wlittle.meridias.local> <15655.1146254635@sss.pgh.pa.us> <1146315175.4995.15.camel@wjlnotebook>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox