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@xxxxxxxxxxx
