Re: ANALYZE getting dead tuple count hopelessly wrong

On Mon, Mar 31, 2008 at 1:33 PM, Stuart Brooks <stuartb(at)cat(dot)co(dot)za> 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