Re: index bloat WAS: reindexing pg_shdepend - Mailing list pgsql-general

OK, as far as I saw you never mentioned what PG version you are running,
but if it's 8.2.x then I think I know what's going on.  The thing that
was bothering me was the discrepancy in size of the two indexes.  Now
the entries in pg_shdepend_reference_index are all going to be
references to roles, and the index dump you sent me showed that they're
all references to the *same* role, ie, there's only one user ID doing
all the creation and deletion of temp tables.  On the other hand, the
entries in pg_shdepend_depender_index will be pg_class and pg_type
references with all different OIDs as the OID counter advances over
time.  So the keys in pg_shdepend_depender_index are all distinct
whereas the ones in pg_shdepend_reference_index are mostly the same.

What apparently is happening is that the 8.2 optimization to avoid
splitting btree pages when we are able to make room by removing
LP_DELETED index tuples kicks in for pg_shdepend_reference_index but not
for pg_shdepend_depender_index.  I think this is precisely because the
keys are all the same in the former: when we scan to mark the latest
temp table's entry deleted, we will visit all the other keys that aren't
yet LP_DELETED, and if they are now dead they'll get marked, and then
the next time we fill up the page we will find we can remove them.  But
in pg_shdepend_reference_index there is not any reason for the system to
revisit an old index entry and discover that it can be marked LP_DELETED.
So those entries will stay there until they get vacuumed.

In short, I think what happened is that pg_shdepend bloated because
autovacuum wasn't touching it (because of that statistics bug), and
pg_shdepend_depender_index bloated right along with it, but
pg_shdepend_reference_index didn't bloat because it was able to recycle
tuples sooner.  The current state you report (in which the table is
small too) would have been reached after a manual VACUUM or VACUUM FULL.

I was able to duplicate this behavior by having an unprivileged user
create and immediately drop a temp table, several thousand times in
succession, and then finally vacuuming pg_shdepend (autovac was off to
prevent bollixing the experiment).  The vacuum shows

regression=# vacuum verbose pg_shdepend;
INFO:  vacuuming "pg_catalog.pg_shdepend"
INFO:  scanned index "pg_shdepend_depender_index" to remove 4000 row versions
DETAIL:  CPU 0.00s/0.03u sec elapsed 0.02 sec.
INFO:  scanned index "pg_shdepend_reference_index" to remove 4000 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_shdepend": removed 4000 row versions in 26 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_shdepend_depender_index" now contains 1 row versions in 19 pages
DETAIL:  4000 index row versions were removed.
15 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_shdepend_reference_index" now contains 1 row versions in 2 pages
DETAIL:  346 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_shdepend": found 4000 removable, 1 nonremovable row versions in 26 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
26 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.03u sec elapsed 0.02 sec.
INFO:  "pg_shdepend": truncated 26 to 1 pages
DETAIL:  CPU 0.01s/0.00u sec elapsed 0.01 sec.
VACUUM

Notice that pg_shdepend_reference_index never actually split at all ---
it's still just the metapage and the root page.  So every time the root
filled, it was able to clean most of it out.

In short: nothing much to see here after all.  We'll just have to keep
in mind that the LP_DELETED recycling patch can have drastically
different effectiveness in different indexes of the same table, and
so it's very possible now for indexes to be of much different sizes.

            regards, tom lane