Re: DB Tuning Notes for comment...

Tom Lane wrote:
> Scott Shattuck <ss(at)technicalpursuit(dot)com> writes:
>
>>Robert Treat wrote:
>>
>>>I don't think this is entirely true. On tables that have large numbers
>>>of inserts, but no updates or deletes, you do not need to run vacuum.
>>
>
>>In my experience I've seen tables with numerous indexes continue to
>>benefit greatly from vacuum/vacuum full operations when large volumes of
>>inserts are performed. This is true even when the update/delete activity
>>on the base table itself is manageable.
>
>
> This is hard to believe, as VACUUM does not even touch the indexes
> unless it has found deletable tuples --- and I am quite certain that
> btree indexes, at least, do not do any VACUUM-time reorganization beyond
> deleting deletable entries. (I wouldn't swear to it one way or the
> other for GiST though.) Robert's opinion coincides with what I know of
> the code.
>

Willing to learn here but skipping a vacuum full has caused some issues
for us. Here's some data from a recent 3 day test run that was done with
regular vacuums but not vacuum fulls. When running with vacuum full the
indexes remain in line:

nsuite-10=# select relname, relpages, reltuples from pg_class where
relname not like 'pg_%' order by reltuples desc;
-[ RECORD 1 ]------------------------------
relname | directory_fullpath_ix
relpages | 96012
reltuples | 1.38114e+06
-[ RECORD 2 ]------------------------------
relname | directory_pkey
relpages | 16618
reltuples | 1.38114e+06
-[ RECORD 3 ]------------------------------
relname | directory
relpages | 23924
reltuples | 59578
<snip>

Needless to say, the system performance was pathetic but the test did
serve to highlight this index issue.

Anyone want to give a quick summary of index maintenance or give me a
pointer into the codebase where someone who's not a C expert might still
get a sense of what's being done? I'd really like to understand how an
index can get so completely out of whack after a weekend of testing.

It seems you're telling me that the data here "proves" there's an update
or delete going on somewhere in the system, even though this test is of
a database initialization driven by a stored procedure with no update or
delete operations targeting the directory table. There may be some
operations being done external to that process that I've not been made
aware of but I'm still curious to learn more about indexing behavior so
I know why something like this happens in the first place.

ss