Re: [GENERAL] autoanalyze criteria

On 24/02/13 10:12, Stefan Andreatta wrote:
>
> On 02/23/2013 09:30 PM, Jeff Janes wrote:
>> Moved discussion from General To Hackers.
>>
>> On Sat, Feb 23, 2013 at 10:41 AM, Stefan Andreatta
>> <s(dot)andreatta(at)synedra(dot)com <mailto:s(dot)andreatta(at)synedra(dot)com>> wrote:
>>
>>
>> On 02/23/2013 05:10 PM, Jeff Janes wrote:
>>>
>>> Sorry, I got tunnel vision about the how the threshold was
>>> computed, and forgot about the thing it was compared to. There
>>> is a "secret" data point in the stats collector
>>> called changes_since_analyze. This is not exposed in the
>>> pg_stat_user_tables. But I think it should be as I often have
>>> wanted to see it.
>>>
>>>
>>
>> Sounds like a very good idea to me - any way I could help to make
>> such a thing happen?
>>
>>
>>
>> It should be fairly easy to implement because the other columns are
>> already there to show you the way, and if you want to try your hand at
>> hacking pgsql it would be a good introduction to doing so.
>>
>> Look at each instance in the code of n_dead_dup and
>> pg_stat_get_dead_tuples, and those are the places where
>> changes_since_analyze also need to be addressed, in an analogous
>> manner (assuming it is isn't already there.)
>>
>> git grep 'n_dead_tup'
>>
>> It looks like we would need to add an SQL function to retrieve the
>> data, then incorporate that function into the view definitions that
>> make up the pg_stat_user_tables etc. views. and of course update the
>> regression test and the documentation.
>>
>> Other than implementing it, we would need to convince other hackers
>> that this is desirable to have. I'm not sure how hard that would be.
>> I've looked in the archives to see if this idea was already considered
>> but rejected, but I don't see any indication that it was previously
>> considered.
>>
>> (http://www.postgresql.org/message-id/4823.1262132964@sss.pgh.pa.us).
>>
>> Cheers,
>>
>> Jeff
>
> Not being a developer, I am afraid, I will not be going to implement it
> myself - nor would anybody wish so ;-)
>
> I also searched the archives, but the closest I found is a discussion on
> the Admin List starting here:
> http://www.postgresql.org/message-id/626919622.7634700.1351695913466.JavaMail.root@alaloop.com
>
> On the other hand, there is quite a lot of discussion about making
> autoanalyze more (or less) aggressive - which seems a difficult task to
> me, when you cannot even check what's triggering your autoanalyze.
>
> Anybody else interested?
>

I was asked about this exact thing the other day - it would be very nice
to have the information visible. I may take a look at doing it (I've
done some hacking on the stats system previously). However don't let
that put anyone else off - as I'll have to find the time to start :-)

Regards

Mark