"Matthew T. O'Connor" <matthew@zeut.net> writes:
> None of this directly addresses the question of what the stats system
> *should* track, but perhaps it is wrongheaded to totally redesign the
> stats system for the purposes of autovacuum.
I'd argue it's fine: there are tons of people using row-level stats
via autovacuum, and (AFAICT) just about nobody using 'em for any other
purpose. Certainly you never see anyone suggesting them as a tool for
investigating problems on pgsql-performance. Sure, it's a repurposing
of the stats subsystem, but we should be willing to do that when field
experience teaches us what's really needed.
> As a quick semi-fix, perhaps autovacuum should look at the number of
> rollbacks vs. commits in an attempt to determine the accuracy of the
> stats. For example if 50% of the transactions are getting rolled
> back, then autovacuum might include 50% of the inserts in the count
> towards the vacuum threshold. Obviously this isn't perfect, but it
> probably gets us closer to reality with the information already
> available.
But all that we have is *global* counts. Inferring ratios applicable to
particular tables seems a big stretch to me. Any given application is
likely to have some types of transactions that roll back much more often
than others.
One thing we could do is tie the stats message sending more tightly to
top-level transaction commit/abort. (It's already effectively true that
we send stats only after commit/abort, but we send 'em from the wrong
place, ie PostgresMain.) Then the semantics of the message could be
read as "here's what I did before committing" or "here's what I did
before aborting" and the collector could interpret the counts
accordingly. However, this still fails in the case where a committed
top-level transaction includes some failed subtransactions. I think
the only full solution will involve backends doing some extra work at
subtransaction commit/abort so that they can report properly classified
update counts.
regards, tom lane