Re: Autoanalyze and OldestXmin

On Thu, Jun 9, 2011 at 2:20 AM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
> Ah, I see. Would there will be benefits if we can do some special handling
> for cases where we know that ANALYZE is running outside a transaction block
> and that its not going to invoke any user-defined functions ?

We'd have to distinguish between user-defined typanalyze functions and
system-defined typanalyze functions, which doesn't seem to appealing,
or robust.

> If user is
> running ANALYZE inside a transaction block, he is probably already aware and
> ready to handle long-running transaction. But running them under the covers
> as part of auto-analyze does not see quite right. The pgbench test already
> shows the severe bloat that a long running analyze may cause for small
> tables and many wasteful vacuum runs on those tables.
> Another idea would be to split the ANALYZE into multiple small transactions,
> each taking a new snapshot. That might result in bad statistics if the table
> is undergoing huge change, but in that case, the stats will be outdated soon
> anyways if we run with a old snapshot. I understand there could be issues
> like counting the same tuple twice or more, but would that be a common case
> to worry about ?

I am wondering if we shouldn't be asking ourselves a different
question: why is ANALYZE running long enough on your tables for this
to become an issue? How long is it taking?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company