Re: lost statistics; analyze needs to execute twice

On Wed, Sep 2, 2009 at 06:25, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>> and when i try to recover them via an analyze; (on all tables on the
>>> database) the result is nothing...
>>> i have to exexute the analyze commands twice to compute the statistics
>
>> pg_stat_* are not directly affected by ANALYZE. They collect runtime
>> statistics about activity in the tables,
>
> Yeah, but ANALYZE does update the stats collector stats too.  I looked
> into what's actually happening here, and it's a bit interesting:
>
> 1. Stats collector tables are empty.
>
> 2. ANALYZE does its thing and sends a PgStat_MsgAnalyze message.
>
> 3. pgstat_recv_analyze *intentionally throws the data away*, on the
> grounds that if it were interesting there would already be a stats
> table entry for the table.
>
> 4. At completion of ANALYZE, the regular tabstat machinery sends
> off a tabstat message for the table, because guess what, ANALYZE did a
> scan of that table, and there are t_blocks_fetched counts to report.

Ah, d'uh. That's the part I missed :-)

> 5. pgstat_recv_tabstat happily creates a table entry.  (The pg_statio
> counts in it are nonzero, even though the pg_stat counts aren't.)
>
> 6. Now, if you repeat the cycle, the stats collector will accept
> the second PgStat_MsgAnalyze message, because this time there's
> a stats table entry.
>
> This is a bit silly I guess --- we dropped the data but didn't actually
> save any stats-table space.
>
> I'm inclined to think that the don't-create-a-table-entry behavior in
> pgstat_recv_vacuum and pgstat_recv_analyze should just be dropped.
> I'm dubious that it ever worked as intended.  To have it work right
> you'd need to suppress vacuum/analyze physical I/O from the tabstats
> counts, which doesn't seem like an amazingly good idea.  Moreover,
> autovacuum is unlikely to issue vacuum or analyze against a table
> that hasn't already got a stats-table entry, so the filter doesn't
> seem likely to buy much if it did work.  There might have been some
> value in the idea back when cron-driven database-wide VACUUM ANALYZE
> was the standard maintenance mechanism, but that's not the recommended
> thing anymore.

Agreed. I doubt it had much value back then either, really, and
definitely even less so now.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/