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
