Re: analyze log question on parent/children tables

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Rural Hunter *EXTERN*" <ruralhunter(at)gmail(dot)com>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: analyze log question on parent/children tables
Date: 2012-11-08 15:21:33
Message-ID: D960CB61B694CF459DCFB4B0128514C208AEFFE6@exadv11.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Rural Hunter 2012-11-08 15:25:00 Re: analyze log question on parent/children tables
Previous Message Rural Hunter 2012-11-08 14:07:44 analyze log question on parent/children tables