I would rather keep this on list since other people can chime in.
Otto Blomqvist wrote:
It does not seem to be a Stats collector problem.
oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del ---------+---------+--------------+----------+-------------+-----------+---- --------+-----------+-----------+----------- 9384219 | file_92 | 2200 | 8423 | f | 49837 | public | 158176 | 318527 | 158176 (1 row)
I insert 50000 records
secom=# select createfile_92records(1, 50000); <--- this is a pg script that inserts records 1 threw 50000. createfile_92records ---------------------- 0
oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del ---------+---------+--------------+----------+-------------+-----------+---- --------+-----------+-----------+----------- 9384219 | file_92 | 2200 | 8423 | f | 49837 | public | 208179 | 318932 | 158377 (1 row)
reltuples does not change ? Hmm. n_tup_ins looks fine.
That is expected, reltuples only gets updated by a vacuum or an analyze.
This table is basically a queue full of records waiting to get transfered over from our 68030 system to the PG database. The records are then moved into folders (using a trigger) like file_92_myy depending on what month the record was created on the 68030. During normal operations there should not be more than 10 records at a time in the table, although during the course of a day a normal system will get about 50k records. I create 50000 records to simulate incoming traffic, since we don't have much traffic in the test lab.
After a few hours we have
secom=# select count(*) from file_92; count ------- 42072
So we have sent over approx 8000 Records.
oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del ---------+---------+--------------+----------+-------------+-----------+---- --------+-----------+-----------+----------- 9384219 | file_92 | 2200 | 8423 | f | 49837 | public | 208218 | 334521 | 166152 (1 row)
n_tup_upd: 318932 + (50000-42072)*2 = 334788 pretty close. (Each record gets updated twice, then moved) n_tup_del: 158377 + (50000-42072) = 166305 pretty close. (there are also minor background traffic going on)
I could send over the full vacuum verbose capture as well as the autovacuum capture if that is of interest.
That might be helpful. I don't see a stats system problem here, but I also haven't heard of any autovac problems recently, so this might be something new.
Thanks,
Matthew O'Connor
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org