Re: relkind='p' has no pg_stat_user_tables

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