Re: Statistics mismatch between n_live_tup and actual row count

> Andreas Brandl <ml(at)3(dot)141592654(dot)de> writes:
> >> The planner doesn't use n_live_tup;
>
> > I'm just curious: where does the planner take the (approximate)
> > row-count from?
>
> It uses the tuple density estimated by the last vacuum or analyze
> (viz,
> reltuples/relpages) and multiplies that by the current relation size.
> There are various reasons for not using n_live_tup, some historical
> and
> some still pretty relevant.

Thanks. I checked pg_class.reltuples against the corresponding count(*) and it's the same drifting here: reltuples equals n_live_tup for our problematic tables and is way off the actual row count.

So I guess it's only one problem again (or it's likely that the problem of bad plans is correlated with the reltuples estimation).

> > Might there be a link between n_live_tup drifting and doing
> > unnecessary (blind) updates, which do not change any information of
> > a row?
>
> Possibly. It's premature to speculate with no test case, but I'm
> wondering if HOT updates confuse that arithmetic. No-op updates
> would follow the HOT path as long as there was room on the page...

I try to come up with a test case, if possible.

Thank you!

Best regards, Andreas