Re: huge pgstat.stat file on PostgreSQL 8.3.24

On 20 Červen 2014, 5:33, Pavel Stehule wrote:
> 2014-06-20 1:44 GMT+02:00 Huang, Suya <Suya(dot)Huang(at)au(dot)experian(dot)com>:
>>
>> Thanks Pavel, to be more clear, what does " pg_stat_reset "really reset?
>> In the document it says " Reset all statistics counters for the current
>> database to zero(requires superuser privileges) ". I thought it would
>> reset all statistics of all tables/indexes, thus why I am thinking of
>> re-run analyze database to gather statistics. Because if table/indexes
>> don't have statistics, the query plan would be affected which is not a
>> good
>> thing to a production box... I'm not so sure if I understand "run
>> statistics" you mentioned here.
>>
>
> you have true - anyway you can clean a content of this directory - but if
> your database has lot of database objects, your stat file will have a
> original size very early
>
> Pavel
>

No, he's not right.

Suya, as I wrote in my previous message, there are two kinds of statistics
in PostgreSQL

a) data distribution statistics
- histograms, MCV lists, number of distinct values, ...
- stored in regular tables
- used for planning
- collected by ANALYZE
- not influenced by pg_stat_reset() at all

b) runtime statistics
- number of scans for table/index, rows fetched from table/index, ...
- tracks activity within the database
- stored in pgstat.stat file (or per-db files in the recent releases)
- used for monitoring, not for planning
- removed by pg_stat_reset()

So running pg_stat_reset will not hurt planning at all.

regards
Tomas