Re: analyze log question on parent/children tables - Mailing list pgsql-admin

From Albe Laurenz
Subject Re: analyze log question on parent/children tables
Date
Msg-id D960CB61B694CF459DCFB4B0128514C208AEFFE6@exadv11.host.magwien.gv.at
Whole thread Raw
In response to analyze log question on parent/children tables  (Rural Hunter <ruralhunter@gmail.com>)
Responses Re: analyze log question on parent/children tables
List pgsql-admin
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


pgsql-admin by date:

Previous
From: Rural Hunter
Date:
Subject: analyze log question on parent/children tables
Next
From: Rural Hunter
Date:
Subject: Re: analyze log question on parent/children tables