pg9.4 relpages of child tables

I believe there's been a behavior change, and not sure if it's deliberate. I
don't think there's a negative consequence for our production use, but it
confused me while summing relpages for analysis purposes, as our 9.4 customers
behaved differently.

Documentation indicates that in pg9.0, ANALYZE of a parent table included
statistics of its children.

Under both pg9.3 and 9.4, this returns no stats rows, after the parent table is
analyzed. It returns stats if the child is analyzed.
SELECT * FROM pg_statistic WHERE starelid='.._2014_01'::regclass

However, in pg9.4, the child's pg_class.relpages is 0 (apparently, 1 for
indices) even after the parent is analyzed (and is an approximate number of
pages if the child is analyzed).

On pg93:
pryzbyj=# create table t (i int);
pryzbyj=# create table t2 (like t) inherits(t);
pryzbyj=# insert into t2(SELECT generate_series(1,100000) ORDER BY RANDOM());
pryzbyj=# select relpages from pg_class where relname='t2';
=> 0
pryzbyj=# analyze t;
pryzbyj=# select relpages from pg_class where relname='t2';
=> 885

On pg94:
ts=# create table t (i int);
ts=# create table t2 (like t) inherits(t);
ts=# insert into t2(SELECT generate_series(1,100000) ORDER BY RANDOM());
ts=# select relpages from pg_class where relname='t2';
=> 0
ts=# analyze t;
ts=# select relpages from pg_class where relname='t2'; -- this changed
=> 0
ts=# analyze t2;
ts=# select relpages from pg_class where relname='t2';
=> 443

Is that a deliberate change, and if so, is there any documentation of it? I'd
prefer to avoid analyzing all our child tables, as all queries hit the parents,
which include statistics on the children.

Thanks,
Justin

Justin Pryzby <pryzby(at)telsasoft(dot)com> writes:
> I believe there's been a behavior change, and not sure if it's deliberate. I
> don't think there's a negative consequence for our production use, but it
> confused me while summing relpages for analysis purposes, as our 9.4 customers
> behaved differently.

I don't see any difference in the behavior of HEAD and 9.0 on this point.

> Documentation indicates that in pg9.0, ANALYZE of a parent table included
> statistics of its children.

Well, ANALYZE on a parent table will collect statistics for that table
as well as some "whole tree" statistics that cover parent plus children;
but the latter are just data distribution stats entered into pg_statistic.
I don't see any indication that any PG version will update the childrens'
relpages values while doing that.

I suspect that you're getting confused because autovacuum kicked in on the
child and updated those stats behind your back. For me, the child's
relpages reads as zero even after the ANALYZE, but if I wait a minute or
so, it changes to a nonzero value because the autovacuum daemon updated
it.

See also the "future directions" thread nearby.

regards, tom lane

On Wed, Mar 18, 2015 at 12:11:22PM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby(at)telsasoft(dot)com> writes:
> > I believe there's been a behavior change, and not sure if it's deliberate. I
> > don't think there's a negative consequence for our production use, but it
> > confused me while summing relpages for analysis purposes, as our 9.4 customers
> > behaved differently.
>
> I suspect that you're getting confused because autovacuum kicked in on the

It seems you're right. I was additionally confused because the autovacuum had
processed the most recent tables (triggered by insertion; partitioned by date),
but had not processed some of last month's tables (which I was querying for
relpages, since it's a complete month), since this DB was upgraded to pg9.4
last month.

Thanks,
Justin