Обсуждение: analyze log question on parent/children tables

Hi,

I'm on 9.1.3. I set auto vacuum off for some tables. I noticed one
thing: when I run manual analyze on parent table, It seems the children
tables are also analyzed. Here is the analyze log:

INFO:  analyzing "public.table_parent"
INFO:  "table_parent": scanned 0 of 0 pages, containing 0 live rows and
0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "public.table_parent" inheritance tree
INFO:  "table_child1": scanned 11405 of 692945 pages, containing 105632
live rows and 103 dead rows; 11405 rows in sample, 10739605 estimated
total rows
INFO:  "table_child2": scanned 1858 of 112866 pages, containing 35818
live rows and 0 dead rows; 1858 rows in sample, 2178974 estimated total rows
....

But when I check the table status in pgAdmin, I noticed the (auto)
analyze time of children tables was not logged. So my question here is:
should I run a separate analyze on children tables?

Rural Hunter wrote:
> I'm on 9.1.3. I set auto vacuum off for some tables. I noticed one
> thing: when I run manual analyze on parent table, It seems the
children
> tables are also analyzed. Here is the analyze log:
>
> INFO:  analyzing "public.table_parent"
> INFO:  "table_parent": scanned 0 of 0 pages, containing 0 live rows
and
> 0 dead rows; 0 rows in sample, 0 estimated total rows
> INFO:  analyzing "public.table_parent" inheritance tree
> INFO:  "table_child1": scanned 11405 of 692945 pages, containing
105632
> live rows and 103 dead rows; 11405 rows in sample, 10739605 estimated
> total rows
> INFO:  "table_child2": scanned 1858 of 112866 pages, containing 35818
> live rows and 0 dead rows; 1858 rows in sample, 2178974 estimated
total rows
> ....
>
> But when I check the table status in pgAdmin, I noticed the (auto)
> analyze time of children tables was not logged. So my question here
is:
> should I run a separate analyze on children tables?

This ANALYZE will only collect statistics for the parent.
If you look in pg_stats, you might find two sets of entries
for the parent table, one with inherited=TRUE and one with
inherited=FALSE.
The hierarchy is scanned for the former values.

If you collect statistics manually, you should also run ANALYZE
on the child tables.

Of course, if you use autovacuum, the right things should happen
(except that autovacuum doesn't seem to update the inherited
statistics for the parent table).

Yours,
Laurenz Albe

Got it. Thanks.

于2012年11月8日 23:21:33,Albe Laurenz写到:
> Rural Hunter wrote:
>> I'm on 9.1.3. I set auto vacuum off for some tables. I noticed one
>> thing: when I run manual analyze on parent table, It seems the
> children
>> tables are also analyzed. Here is the analyze log:
>>
>> INFO:  analyzing "public.table_parent"
>> INFO:  "table_parent": scanned 0 of 0 pages, containing 0 live rows
> and
>> 0 dead rows; 0 rows in sample, 0 estimated total rows
>> INFO:  analyzing "public.table_parent" inheritance tree
>> INFO:  "table_child1": scanned 11405 of 692945 pages, containing
> 105632
>> live rows and 103 dead rows; 11405 rows in sample, 10739605 estimated
>> total rows
>> INFO:  "table_child2": scanned 1858 of 112866 pages, containing 35818
>> live rows and 0 dead rows; 1858 rows in sample, 2178974 estimated
> total rows
>> ....
>>
>> But when I check the table status in pgAdmin, I noticed the (auto)
>> analyze time of children tables was not logged. So my question here
> is:
>> should I run a separate analyze on children tables?
>
> This ANALYZE will only collect statistics for the parent.
> If you look in pg_stats, you might find two sets of entries
> for the parent table, one with inherited=TRUE and one with
> inherited=FALSE.
> The hierarchy is scanned for the former values.
>
> If you collect statistics manually, you should also run ANALYZE
> on the child tables.
>
> Of course, if you use autovacuum, the right things should happen
> (except that autovacuum doesn't seem to update the inherited
> statistics for the parent table).
>
> Yours,
> Laurenz Albe
>