Re: relkind='p' has no pg_stat_user_tables

On 05/03/2018 09:20 AM, Alvaro Herrera wrote:
> Adrian Klaver wrote:
>
>> and from here:
>>
>> 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.

Using psql -E and \d+ on table t I got the following to find the children:

test_(aklaver)> SELECT c.oid::pg_catalog.regclass,
pg_catalog.pg_get_expr(c.relpartbound, c.oid) FROM pg_catalog.pg_class
c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
'1417272' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;

oid | pg_get_expr
-----+----------------------------
t1 | FOR VALUES FROM (1) TO (9)

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