Re: relkind='p' has no pg_stat_user_tables

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.

The behavior for 'r' tables has not changed:

https://www.postgresql.org/docs/9.0/static/sql-analyze.html

https://www.postgresql.org/docs/10/static/sql-analyze.html

The 'p' type table does not appear until version 10:

https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html

https://www.postgresql.org/docs/10/static/catalog-pg-class.html

so there is no past behavior to compare to.

>
> Justin
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com