It seems a simple "vacuum" (not full or analyze) slows down the database dramatically. I am running vacuum every 15 minutes, but it takes about 5 minutes to run even after a fresh import. Even with vacuuming every 15 minutes, I'm not sure vacuuming is working properly.
There are a lot of updates. The slowest relation is the primary key index, which is composed of a sequence. I've appended a csv with the parsed output from vacuum. The page counts are growing way too fast imo. I believe this is caused by the updates, and index pages not getting re-used. The index values aren't changing, but other values in the table are. Any suggestions how to make vacuuming more effective and reducing the time it takes to vacuum? I'd settle for less frequent vacuuming or perhaps index rebuilding. The database can be re-imported in about an hour. Rob ---------------------------------------------------------------- Spacing every 15 minutes Pages,Tuples,Deleted 7974,1029258,1536 7979,1025951,4336 7979,1026129,52 7979,1025618,686 7979,1025520,152 7980,1025583,28 7995,1028008,6 8004,1030016,14 8010,1026149,4965 8012,1026684,6 8014,1025910,960 8020,1026812,114 8027,1027642,50 8031,1027913,362 8040,1028368,784 8046,1028454,1143 8049,1029155,6 8053,1029980,10 8065,1031506,24 8084,1029134,4804 8098,1031004,346 8103,1029412,3044 8118,1029736,1872 8141,1031643,1704 8150,1032597,286 8152,1033222,6 8159,1029436,4845 8165,1029987,712 8170,1030229,268 8176,1029568,1632 8189,1030136,1540 8218,1030915,3963 8255,1033049,4598 8297,1036583,3866 8308,1031412,8640 8315,1031987,1058 8325,1033892,6 8334,1030589,4625 8350,1031709,1040 8400,1033071,5946 8426,1031555,8368 8434,1031638,2240 8436,1031703,872 8442,1031891,612 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
