On Mon, Mar 31, 2008 at 1:33 PM, Stuart Brooks <stuartb@xxxxxxxxx> wrote: > I have a table with about 15 million rows which is constantly having > tuples added to the head and deleted in blocks from the tail to maintain > the size. The dead tuple count in pg_stat_user_tables tracks the deleted > rows fairly accurately until an auto-ANALYZE is done in the background > at which point the value it calculates is wrong by a factor of 2-3 times > (calculated value is 30-50% of the correct value) (copying -hackers) Seems like the redirected-dead line pointers are playing spoil-sport here. In this particular example, the deleted tuples may get truncated to redirected-dead line pointers. Analyze would report them as empty slots and not as dead tuples. So in the worst case, if all the deleted tuples are already truncated to redirected-dead line pointers, analyze may report "zero" dead tuple count. This is a slightly tricky situation because in normal case we might want to delay autovacuum to let subsequent UPDATEs in the page to reuse the space released by the deleted tuples. But in this particular example, delaying autovacuum is not a good thing because the relation would just keep growing. I think we should check for redirected-dead line pointers in analyze.c and report them as dead tuples. The other longer term alternative could be to track redirected-dead line pointers and give them some weightage while deciding on autovacuum. We can also update the FSM information of a page when its pruned/defragged so that the page can also be used for subsequent INSERTs or non-HOT UPDATEs in other pages. This might be easier said than done. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
