Thread: 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 >