Re: BUG #7853: Incorrect statistics in table with many dead rows.

On Fri, Feb 22, 2013 at 3:41 PM, James R Skaggs
<james(dot)r(dot)skaggs(at)seagate(dot)com>wrote:

> Okay, I have some more info.
>
> Some background info. This one table gets so many changes, I CLUSTER it
> each night. However, after I do this. The statistics still appear to be
> incorrect. Even after I do a "select pg_stat_reset();" Followed by 3
> ANALYZE at default_statistics_target as 1, 10, and 100
>
> select relname, n_live_tup, n_dead_tup, n_tup_ins, n_tup_del,
> n_tup_hot_upd
> from pg_stat_all_tables
> ('stream_file', 109061143L, 103913868L, 0L, 14201L, 0L)
>
> Is it possible that there are still dead tuples after a CLUSTER?
>

Yes. A cluster must bring along any tuples which are possibly visible to
any open transaction. Your root problem seems to be that you have
long-open transactions which are preventing vacuum from doing its thing,
which leads you try clustering, but the long-open transaction prevents that
from doing its things effectively as well.

Perhaps PG could deal with this situation more gracefully, but
fundamentally you have to figure why you have these ancient transactions
lying around, and fix them or kill them.

Cheers,

Jeff