Tom Lane wrote:
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:Scott Shattuck <[EMAIL PROTECTED]> 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.
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
