Re: relkind='p' has no pg_stat_user_tables

On Thu, May 03, 2018 at 11:15:19AM -0700, Adrian Klaver wrote:
> On 05/03/2018 10:38 AM, Justin Pryzby wrote:
> >On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote:
> >>On 05/03/2018 09:20 AM, Alvaro Herrera wrote:
> >>>>https://www.postgresql.org/docs/10/static/sql-createtable.html
> >>>>
> >>>>"A partitioned table is divided into sub-tables (called partitions), which
> >>>>are created using separate CREATE TABLE commands. The partitioned table is
> >>>>itself empty. A data row inserted into the table is routed to a partition
> >>>>based on the value of columns or expressions in the partition key. ... "
> >>>
> >>>Yeah, but I think Justin has a valid question from the POV of the user:
> >>>how can we figure out if we need to re-run analyze on a partitioned
> >>>table, if the time of last analyze is not stored anywhere?
> >>
> >>I agree. The only thing I can think of is, that knowing :
> >>
> >>ANALYZE VERBOSE t;
> >>
> >>walks the inheritance tree, look at the pg_stat_user_tables for one of the
> >>children for the last time analyzed.
> >
> >I think I can make this work for my purposes:
> >
> >SELECT MIN(GREATEST(last_analyze,last_autoanalyze))
> >FROM pg_stat_user_tables psut
> >JOIN pg_inherits i
> >ON i.inhrelid=psut.relid
> >WHERE i.inhparent=...
> >
> >I was about to say that it's perhaps more correct for relkind='r' parents, too.
> >
> >But actually, it looks like for relkind='p', ANALYZE populates stats on child
> >tables in addition to the parent. For relkind='r', the behavior (introduced in
> >PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent
> >(both "inherited" stats including children, and "ONLY" stats for the
> >potentially-nonempty parent).
> >
> >I guess ability to update child tables' stats is a nice feature, but I'm
> >surprised. I wonder if that was a deliberate/documented change ?
>
> I was with you until I got to the above. You seem to be comparing apples and
> oranges unless I am missing something.

Yes, I was surprised about the difference between ANALYZE relkind_p
and relkind_r.

But I see that's a documented behavior I'd missed until now:

https://www.postgresql.org/docs/current/static/sql-analyze.html
|If the specified table is a partitioned table, both the inheritance statistics
|of the partitioned table as a whole and statistics of the individual partitions
|are updated.

Thanks,
Justin