Thread: relkind='p' has no pg_stat_user_tables

I (finally) realized that my script for ANALYZEing parents of table hierarchies
every month or so was looping around the same parent tables every night due to
no stats for date of last last analysis.

I guess that's deliberate/known and maybe related to relkind='p' having no
relfilenode.

Is there any good workaround other than making stampfiles or making my own
"last analyzed" table?

Thanks,
Justin

On 05/03/2018 07:14 AM, Justin Pryzby wrote:
> I (finally) realized that my script for ANALYZEing parents of table hierarchies
> every month or so was looping around the same parent tables every night due to
> no stats for date of last last analysis.

Would help to see the script.

> 
> I guess that's deliberate/known and maybe related to relkind='p' having no
> relfilenode.

Not sure that is an issue as pg_stat_user uses relid not relfilenode:

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW

For reasons why they are often not the same:

https://www.postgresql.org/docs/10/static/storage-file-layout.html

"
Caution

Note that while a table's filenode often matches its OID, this is not 
necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER 
and some forms of ALTER TABLE, can change the filenode while preserving 
the OID. Avoid assuming that filenode and table OID are the same. Also, 
for certain system catalogs including pg_class itself, 
pg_class.relfilenode contains zero. The actual filenode number of these 
catalogs is stored in a lower-level data structure, and can be obtained 
using the pg_relation_filenode() function.
"

> 
> Is there any good workaround other than making stampfiles or making my own
> "last analyzed" table?
> 
> Thanks,
> Justin
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, May 03, 2018 at 07:44:24AM -0700, Adrian Klaver wrote:
> On 05/03/2018 07:14 AM, Justin Pryzby wrote:
> >I (finally) realized that my script for ANALYZEing parents of table hierarchies
> >every month or so was looping around the same parent tables every night due to
> >no stats for date of last last analysis.
> 
> Would help to see the script.

I reproduced it more simply than the 300 line script:

postgres=# CREATE TABLE t(i int)PARTITION BY RANGE(i);
postgres=# CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (9);
postgres=# INSERT INTO t1 VALUES(1),(2);
postgres=# ANALYZE VERBOSE t;
postgres=# SELECT * FROM pg_stat_user_tables WHERE relname='t';
(0 rows)
postgres=# SELECT 1 FROM pg_statistic WHERE starelid='t'::regclass;
?column? | 1

Justin

On 05/03/2018 08:45 AM, Justin Pryzby wrote:
> On Thu, May 03, 2018 at 07:44:24AM -0700, Adrian Klaver wrote:
>> On 05/03/2018 07:14 AM, Justin Pryzby wrote:
>>> I (finally) realized that my script for ANALYZEing parents of table hierarchies
>>> every month or so was looping around the same parent tables every night due to
>>> no stats for date of last last analysis.
>>
>> Would help to see the script.
> 
> I reproduced it more simply than the 300 line script:
> 
> postgres=# CREATE TABLE t(i int)PARTITION BY RANGE(i);
> postgres=# CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (9);
> postgres=# INSERT INTO t1 VALUES(1),(2);
> postgres=# ANALYZE VERBOSE t;

I would say the answer lies below from above command:

test_(postgres)# ANALYZE VERBOSE t;
INFO:  analyzing "public.t" inheritance tree
INFO:  "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead 
rows; 2 rows in sample, 2 estimated total rows
INFO:  analyzing "public.t1"
INFO:  "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead 
rows; 2 rows in sample, 2 estimated total rows
ANALYZE

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. ... "

test_(postgres)# select * from only t;
  i
---
(0 rows)

Table t is just a pointer to the child tables and only the bulk 
statistics as shown in pg_statistic are maintained.


> postgres=# SELECT * FROM pg_stat_user_tables WHERE relname='t';
> (0 rows)
> postgres=# SELECT 1 FROM pg_statistic WHERE starelid='t'::regclass;
> ?column? | 1
> 
> Justin
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.klaver@aklaver.com

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

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.klaver@aklaver.com

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

I am playing around with RDS PG and I am not able to understand the following:

1. The database name I created via RDS console is in upper case with no quotes. From the remote machine via psql,
     if I try to use lower case db name with the -d option it errors out "database not found".  Works only with upper
case.  
    This behavior does not exist in non RDS env.

2. Two RDS parameter group

    rds.force_ssl
    ssl
    have been set to 1 (true)

    yet from a remote machine I can connect to the database via psql without specifying any option for ssl.  How is it
doing? 

psql (10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)


thanks

On Thu, May 03, 2018 at 01:24:59PM -0500, Justin Pryzby wrote:
> 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.

When I read this thread, it seems to me that more user-friendly system
functions able to extract a sub-tree of child relations (by inheritance
and/or partition) is something which would help.  Now users are limited
to things like large WITH RECURSIVE queries when willing to extract a
full tree.  While that's easily done with a custom function, there is
room for an in-core function as well.  I recall that Amit Langote has
sent a patch which introduces a wrapper function on top of
find_all_inheritors, perhaps that would get into v12.
--
Michael