Re: Less selective index chosen unexpectedly

On Tue, May 18, 2021 at 2:50 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> I think the real winner would be a mechanism to incrementally analyze
> tables, so that it updates the existing stats by sampling only blocks
> that have new data, and "somehow" merge that into the existing
> statistics. You could have such a process run much more frequently than
> standard analyze, because the cost is [supposed to be] smaller.

I wonder if there is a more general design that incorporates changes
over time. That is, a design that has ANALYZE store old statistics for
a table in order to give the system (and the DBA) a sense of how
things change over time. This could enable autoanalyze behavior that
more or less settles on an optimal frequency between ANALYZE
operations -- frequent enough to get stable statistics with some
margin of error, but not too frequent.

I also wonder if this general approach could enable a strategy that
uses something like Bayesian inference to detect bad statistics,
and/or to dampen the consequences of bad estimates.

--
Peter Geoghegan