Re: Statistics mismatch between n_live_tup and actual row count

Hi,

> Andreas Brandl <ml(at)3(dot)141592654(dot)de> writes:
> > we're currently investigating a statistics issue on postgres. We
> > have some tables which frequently show up with strange values for
> > n_live_tup. If you compare those values with a count on that
> > particular table, there is a mismatch of factor 10-30. This causes
> > the planner to come up with very bad plans (we also have this issue
> > on bigger table like the one below).
>
> The planner doesn't use n_live_tup; the only thing that that's used
> for
> is decisions about when to autovacuum/autoanalyze. So you have two
> problems here not one.

So, you're saying that having a mismatch between n_live_tup and the actual row count is not that much of a problem (besides it influences when to auto-vacuum/analyze), right?

I'm just curious: where does the planner take the (approximate) row-count from?

> Can you provide a test case for the n_live_tup drift? That is,
> something that when done over and over causes n_live_tup to get
> further
> and further from reality?

I'll try to implement a minimal piece of code showing this, although I'm not sure if this will work.

Might there be a link between n_live_tup drifting and doing unnecessary (blind) updates, which do not change any information of a row?

Thank you!

Best regards
Andreas