Re: Improve automatic analyze messages for inheritance trees

I noticed that analyze messages shown by autovacuum don't discriminate
between non-inherited cases and inherited cases, as shown in the below
example:

LOG: automatic analyze of table "postgres.public.pt" system usage: CPU
0.00s/0.01u sec elapsed 0.06 sec
LOG: automatic analyze of table "postgres.public.pt" system usage: CPU
0.00s/0.02u sec elapsed 0.11 sec

(The first one is for table "postgres.public.pt" and the second one is
for table inheritance tree "postgres.public.pt".)

So, I'd like to propose improving the messages for inherited cases, in
order to easily distinguish such cases from non-inherited cases. Please
find attached a patch. I'll add this to the upcoming CF.

Thanks,

Best regards,
Etsuro Fujita

On 6 October 2014 11:07, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> I noticed that analyze messages shown by autovacuum don't discriminate
> between non-inherited cases and inherited cases, as shown in the below
> example:
>
> LOG: automatic analyze of table "postgres.public.pt" system usage: CPU
> 0.00s/0.01u sec elapsed 0.06 sec
> LOG: automatic analyze of table "postgres.public.pt" system usage: CPU
> 0.00s/0.02u sec elapsed 0.11 sec
>
> (The first one is for table "postgres.public.pt" and the second one is
> for table inheritance tree "postgres.public.pt".)
>
> So, I'd like to propose improving the messages for inherited cases, in
> order to easily distinguish such cases from non-inherited cases. Please
> find attached a patch. I'll add this to the upcoming CF.

Thanks for the suggestion. It seems like a useful addition.

Existing log analysis may wish to see the "automatic analyze of table"
on each row.
So it would be good to keep
automatic analyze of table \"%s.%s.%s\"

Can we add some words after this to indicate inheritance? (I have no
suggestions at present)
e.g.
automatic analyze of table \"%s.%s.%s\" (new words go here)

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

(2014/10/16 11:45), Simon Riggs wrote:
> On 6 October 2014 11:07, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> I noticed that analyze messages shown by autovacuum don't discriminate
>> between non-inherited cases and inherited cases, as shown in the below
>> example:
>>
>> LOG: automatic analyze of table "postgres.public.pt" system usage: CPU
>> 0.00s/0.01u sec elapsed 0.06 sec
>> LOG: automatic analyze of table "postgres.public.pt" system usage: CPU
>> 0.00s/0.02u sec elapsed 0.11 sec
>>
>> (The first one is for table "postgres.public.pt" and the second one is
>> for table inheritance tree "postgres.public.pt".)
>>
>> So, I'd like to propose improving the messages for inherited cases, in
>> order to easily distinguish such cases from non-inherited cases. Please
>> find attached a patch. I'll add this to the upcoming CF.
>
> Thanks for the suggestion. It seems like a useful addition.
>
> Existing log analysis may wish to see the "automatic analyze of table"
> on each row.
> So it would be good to keep
> automatic analyze of table \"%s.%s.%s\"

Agreed.

> Can we add some words after this to indicate inheritance? (I have no
> suggestions at present)
> e.g.
> automatic analyze of table \"%s.%s.%s\" (new words go here)

How about this?

automatic analyze of table \"%s.%s.%s\" as inheritance tree

Thank you for the comment.

Best regards,
Etsuro Fujita

On 16 October 2014 06:49, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:

> How about this?
>
> automatic analyze of table \"%s.%s.%s\" as inheritance tree
>
> Thank you for the comment.

Would it be useful to keep track of how many tables just got analyzed?

i.e. analyze of foo (including N inheritance children)

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

(2014/10/16 17:17), Simon Riggs wrote:
> On 16 October 2014 06:49, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> How about this?
>>
>> automatic analyze of table \"%s.%s.%s\" as inheritance tree
>>
>> Thank you for the comment.
>
> Would it be useful to keep track of how many tables just got analyzed?
>
> i.e. analyze of foo (including N inheritance children)

I think that's a good idea. So, I'll update the patch.

Thanks,

Best regards,
Etsuro Fujita

(2014/10/17 18:35), Etsuro Fujita wrote:
> (2014/10/16 17:17), Simon Riggs wrote:
>> Would it be useful to keep track of how many tables just got analyzed?
>>
>> i.e. analyze of foo (including N inheritance children)
>
> I think that's a good idea. So, I'll update the patch.

Done. Attached is an updated version of the patch.

Thanks for the comment!

Best regards,
Etsuro Fujita

On 30 October 2014 03:30, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> (2014/10/17 18:35), Etsuro Fujita wrote:
>>
>> (2014/10/16 17:17), Simon Riggs wrote:
>>>
>>> Would it be useful to keep track of how many tables just got analyzed?
>>>
>>> i.e. analyze of foo (including N inheritance children)
>>
>>
>> I think that's a good idea. So, I'll update the patch.
>
>
> Done. Attached is an updated version of the patch.
>
> Thanks for the comment!

The patch was kinda ok, but we have deeper problems.

If we have a 3 level hierarchy like foo->(p1, p2->(p4), p3)
then we still report this pretty strangely
LOG: automatic analyze of table "postgres.public.p1" system usage:
CPU 0.00s/0.00u sec elapsed 0.05 sec
LOG: automatic analyze of table "postgres.public.foo" system usage:
CPU 0.00s/0.00u sec elapsed 0.04 sec
LOG: automatic analyze of table "postgres.public.foo" (including 3
inheritance children) system usage: CPU 0.00s/0.01u sec elapsed 0.12
sec
LOG: automatic analyze of table "postgres.public.p4" system usage:
CPU 0.00s/0.00u sec elapsed 0.00 sec

notice that p4 is not included as an inheritance child, even though it
most surely is. Why is p4 reported, when p1, p2 and p3 are not?

and I notice psql reports this incorrectly also

postgres=# \d+ foo

Table "public.foo"
Column | Type | Modifiers | Storage | Stats target | Description

----------+---------+-----------+---------+--------------+-------------

?column? | integer | | plain | |

Child tables: p1,
p2,
p3

>>No mention of grandchildren...

Not your fault, but this patch doesn't sufficiently improve the
situation to commit it, yet.

Sorry, patch returned with feedback, for now.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services