After a long battle with technology, PHatcher@macys.com (Patrick Hatcher), an earthling, wrote:
> PG=7.4.5
>
> I guess I never noticed this during vacuum verbose before, but is it common
> for the index to be 2 to 3 times the number of rows in a table? I've tried
> reindexing and then dropping and readding them. Still the same number of
> rows.
> Indexes are all btree
>
> mdc_oz=# select count(*) from kst;
> count
> -------
> 919
> (1 row)
>
> mdc_oz=# vacuum full verbose analyze kst;
> INFO: vacuuming "public.kst"
> INFO: "kst": found 0 removable, 2757 nonremovable row versions in 64 pages
> DETAIL: 1838 dead row versions cannot be removed yet.
> Nonremovable row versions range from 141 to 235 bytes long.
> There were 0 unused item pointers.
> Total free space (including removable row versions) is 9112 bytes.
> 0 pages are or will become empty, including 0 at the end of the table.
> 8 pages containing 5328 free bytes are potential move destinations.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "xie1kst" now contains 2757 row versions in 9 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
This is NOT consistent with the old "index bloat" problem.
Those evidently are row versions being held onto because some old
transactions are still running that could access them.
The index bloat problem didn't usually happen with small tables; it
happened with large ones where the index values were assigned by a
sequence so that old "dead zones" wouldn't get touched again. What
you would expect to see is some atrociously low density of index
usage. One tuple per page, that sort of thing, where it *ought* to be
possible to fit on the order of a thousand tuples into each page.
Your table has 64 pages, and the indices all have way fewer than that.
If an index started having _more_ pages than the table, and this would
only start to happen when there were thousands of pages of both, THEN
you've got index bloat...
What you're talking about here? It all fits trivially in shared
cache, so even doing seq scans on this table wouldn't hurt too
badly...
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/unix.html
"Catapultam habeo! Nisi pecuniam omnem mihi dabis, ad caput tuum
saxum immane mittam !!" (I have a catapult! If you do not pay me the
money you owe me, I will hit you with a big rock !!)
-- Simon Gornall <simon@unique-id.com>
