Thread: Clarification on two bits on VACUUM FULL VERBOSE output

Clarification on two bits on VACUUM FULL VERBOSE output

From
Josh Berkus
Date:
Folks,

Wanted to get clarification on two bits of output from 7.4's VACUUM FULL
VERBOSE:

"Total free space (including removable row versions) is 2932036 bytes."
If the table referenced has no dead row versions, does this indicate open
space on partially full pages?

"There were 33076 unused item pointers."
Is this a count of dead index pointers, or something else?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: Clarification on two bits on VACUUM FULL VERBOSE output

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Wanted to get clarification on two bits of output from 7.4's VACUUM FULL
> VERBOSE:

> "Total free space (including removable row versions) is 2932036 bytes."
> If the table referenced has no dead row versions, does this indicate open
> space on partially full pages?

Yes.

> "There were 33076 unused item pointers."
> Is this a count of dead index pointers, or something else?

No, it's currently-unused item pointers (a/k/a line pointers) on heap
pages.  See http://developer.postgresql.org/docs/postgres/page.html

            regards, tom lane

Re: Clarification on two bits on VACUUM FULL VERBOSE output

From
Josh Berkus
Date:
Tom,

> > "There were 33076 unused item pointers."
> > Is this a count of dead index pointers, or something else?
>
> No, it's currently-unused item pointers (a/k/a line pointers) on heap
> pages.  See http://developer.postgresql.org/docs/postgres/page.html

So this would be a count of pointers whose items had already been moved?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: Clarification on two bits on VACUUM FULL VERBOSE output

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
>>> "There were 33076 unused item pointers."
>>> Is this a count of dead index pointers, or something else?
>>
>> No, it's currently-unused item pointers (a/k/a line pointers) on heap
>> pages. See http://developer.postgresql.org/docs/postgres/page.html

> So this would be a count of pointers whose items had already been moved?

Either deleted, or moved to another page during VACUUM FULL compaction.
Such a pointer can be recycled to point to a new item, if there's room
to put another item on its page ... but if not, the pointer is
wasted space.  I don't believe we ever try to physically eliminate
unused item pointers.

            regards, tom lane