analyze log question on parent/children tables

[Rural Hunter]

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?

[Albe Laurenz]

Post by Rural Hunter
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

Post by Rural Hunter
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)
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

[Rural Hunter]

Got it. Thanks.

Post by Rural Hunter

Post by Rural Hunter
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

Post by Rural Hunter
INFO: analyzing "public.table_parent"
INFO: "table_parent": scanned 0 of 0 pages, containing 0 live rows

and

Post by Rural Hunter
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

Post by Rural Hunter
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

Post by Rural Hunter
....
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
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