Re: relkind='p' has no pg_stat_user_tables

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 ?

Justin