Hi Tom, Alvaro. Thanks so much for your replies.
On 22-Jun-06, at 12:56 PM, Alvaro Herrera wrote:
Hmm ... I'd think that the number of dead tuples plus live tuples should
never be smaller than the number of tuples seen at last analyze. Unless
some stats messages are lost (say, stop the server, start with stats
disabled, do a big DELETE, stop, restart normally).
We had to restart the server to upgrade to 8.1.4 when it was released, but since then we have not restarted our server. We haven't changed our stats gathering recently, although we've made some changes to auto-vacuum to do it more frequently (outlined in my first e-mail). Unfortunately, I do not have any data to supply previous to the upgrade as we have just recently changed our logging to be more verbose.
I'm interested in what Dylan can tell about the activity over this
table? What does pg_stat_user_tables say about it?
Here's the pg_stat_user_tables entry for this table:
-[ RECORD 1 ]-+----------
relid | #####
schemaname | public
relname | tablename
seq_scan | 345
seq_tup_read | 46699390
idx_scan | 33731562
idx_tup_fetch | 171166937
n_tup_ins | 3359
n_tup_upd | 8205786
n_tup_del | 0
As you can see, the number of deleted tuples is 0, so I don't believe that it could be an issue with DELETEs on the table because there aren't any!
It has to be something that occurs when the VACCUM threshold is hit, because it resets both the VACUUM and ANALYZE counters, and it decreases
Yesterday we tried running the ANALYZE command and it did reset the counter to a non-negative value. However, the next time that the VACUUM threshold was reached, the ANALYZE counter was decreased to a negative number.
Thanks for looking into this, please let me know if there's any information I can provide!
--
Dylan Hansen
Enterprise Systems Developer