Re: pgsql: When estimating the selectivity of an inequality "column >

On Mon, Jan 4, 2010 at 2:50 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Maybe autovac could run such a cheap ANALYZE frequently on tables with
> large number of inserts (but not large enough to trigger a regular
> ANALYZE) ... say a fixed number of tuples (not depending on pg_class.reltuples)
>

Well that might cut down on the number of plans that need to do it
themselves. But I'm more concerned about a database that *doesn't*
have a frequent number of inserts. Such a database should presumably
trigger a real analyze reasonably quickly.

But consider a database that has one new record inserted per day but
thousands of queries per minute looking up the maximum value in the
table. This change has basically doubled the work that query needs to
do since the planner now needs to do the same lookup that the query
itself was going to do. And autovacuum won't fire for a long long time
against this table.

Admittedly the fact that there is 100% overhead isn't terribly
interesting since it's really a fixed overhead and only 100% if that
query happens to be correspondingly cheap. But it's still annoying to
me that we'll potentially never figure out what the new stats should
be and stop doing the lookup no matter how long the new row sits there
unless some unrelated activity triggers a real analyze.

--
greg