Hello,
I have a question about running ANALYZE on table hierarchies.
The documentation page for partitioning mentions the need to manually issue VACUUM and ANALYZEs for each partition (http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS), while the page for ANALYZE (http://www.postgresql.org/docs/9.4/static/sql-analyze.html) mentions it does gather statistics for the master table and its children.
When I ANALYZE a master table PostgreSQL says it is working on the entire hierarchy and automatically ANALYZEs the child tables. However, the last_analyze column stays empty for the child tables on pg_stat_user_tables.
I find this somewhat confusing, what is the expected behavior? I figure the child tables have been properly ANALYZEd, but I can't find it registered anywhere. It would be nice to be able to parse pg_stat_user_tables for analyze statistics without having to worry about hierarchies, since each partition seems to have its own statistics for everything else.
nunks=# analyze verbose tb05;
INFO: analyzing "public.tb05"
INFO: "tb05": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO: analyzing "public.tb05" inheritance tree
INFO: "tb05_2016": scanned 165 of 165 pages, containing 7465 live rows and 455 dead rows; 7465 rows in sample, 7465 estimated total rows
INFO: "tb05_2015": scanned 381 of 381 pages, containing 16281 live rows and 1504 dead rows; 16281 rows in sample, 16281 estimated total rows
ANALYZE
nunks=# select relname, last_analyze from pg_stat_user_tables where relname like 'tb05%';
relname | last_analyze
-----------+-------------------------------
tb05 | 2016-02-18 22:47:32.770076-02
tb05_2016 |
tb05_2015 |
(3 rows)
nunks=# \d+ tb05;
Table "public.tb05"
Column | Type | Modifiers | Storage | Stats target | Description
---------------------+-----------------------------+-----------+----------+--------------+-------------
a | character varying(8) | not null | extended | |
b | date | not null | plain | |
c | timestamp without time zone | not null | plain | |
d | timestamp without time zone | not null | plain | |
e | bigint | not null | plain | |
f | bigint | not null | plain | |
g | bigint | not null | plain | |
h | bigint | not null | plain | |
i | bigint | not null | plain | |
j | bigint | not null | plain | |
k | bigint | not null | plain | |
l | bigint | not null | plain | |
m | bigint | not null | plain | |
n | bigint | not null | plain | |
o | bigint | not null | plain | |
p | bigint | not null | plain | |
q | bigint | not null | plain | |
r | bigint | not null | plain | |
Indexes:
"tb05_pkey" PRIMARY KEY, btree (a, b)
Triggers:
tr_partition_tb05 BEFORE INSERT ON tb05 FOR EACH ROW EXECUTE PROCEDURE fn_partition_tb05()
Child tables: tb05_2015,
tb05_2016
Thanks!
Nunks
