PostgreSQL Analyze分区表：主表与子表的统计信息问题
最近在PostgreSQL的Mail List中参与讨论了一个问题： ANALYZE'ing table hierarchies。
网友认为在Analyze分区表的时候，只能看到主表的analyze日期更新到最新，但是子表的日期没有变化，他认为analyze应该是在分析主表的时候会将与之相关的子表一起更新。
为此测试了一下，发现无论主表还是子表，需要单独的对其进行analyze才可以更新其最新的统计信息。
以下是实验部分：
music=> create table music(id int,name text,style text);
CREATE TABLE
music=> create table rock (check(style = 'rock')) inherits(music);
CREATE TABLE
music=> create table pop (check(style = 'pop')) inherits(music);
CREATE TABLE
music=> create table classic (check(style = 'classic')) inherits(music);
CREATE TABLE
music=> create table jazz (check(style = 'jazz')) inherits(music);
CREATE TABLE
music=> \dS+ music
Table "eric.music"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | text | | extended | |
style | text | | extended | |
Child tables: classic,
jazz,
pop,
rock
music=> create index music_pop_id on pop (id);
CREATE INDEX
music=> create index music_rock_id on rock (id);
CREATE INDEX
music=> create index music_jazz_id on jazz (id);
CREATE INDEX
music=> create index music_classic_id on classic (id);
CREATE INDEX
music=> CREATE OR REPLACE FUNCTION music_insert_trigger()
music-> RETURNS TRIGGER AS
music-> $$
music$> BEGIN
music$> IF (NEW.style = 'rock') THEN
music$> INSERT INTO rock VALUES (NEW.*);
music$> ELSEIF (NEW.style = 'pop') THEN
music$> INSERT INTO pop VALUES (NEW.*);
music$> ELSEIF (NEW.style = 'pop') THEN
music$> INSERT INTO pop VALUES (NEW.*);
music$> ELSEIF (NEW.style = 'jazz') THEN
music$> INSERT INTO jazz VALUES (NEW.*);
music$> ELSEIF (NEW.style = 'classic') THEN
music$> INSERT INTO classic VALUES (NEW.*);
music$> END IF;
music$> RETURN NULL;
music$> END;
music$> $$
music-> LANGUAGE plpgsql ;
CREATE FUNCTION
music=> CREATE TRIGGER insert_music_trigger
music-> BEFORE INSERT ON music
music-> FOR EACH row EXECUTE PROCEDURE music_insert_trigger() ;
CREATE TRIGGER
music=> insert into music values(2,'Have a Nice Day','pop');
INSERT 0 0
music=> insert into music values(1,'21 Gun','rock');
INSERT 0 0
music=> select * from music;
id | name | style
----+-----------------+-------
1 | 21 Gun | rock
2 | Have a Nice Day | pop
(2 rows)
music=> select * from pop;
id | name | style
----+-----------------+-------
2 | Have a Nice Day | pop
(1 row)
music=> select * from rock;
id | name | style
----+--------+-------
1 | 21 Gun | rock
(1 row)
music=> analyze verbose music ;
INFO: analyzing "eric.music"
INFO: "music": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO: analyzing "eric.music" inheritance tree
INFO: "rock": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO: "pop": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
ANALYZE
music=> select relname, last_analyze from pg_stat_user_tables where relname = 'music';
relname | last_analyze
---------+-------------------------------
music | 2016-02-18 22:29:56.528758-08
(1 row)
music=> select relname, last_analyze from pg_stat_user_tables where relname = 'pop';
relname | last_analyze
---------+--------------
pop |
(1 row)
music=> select relname, last_analyze from pg_stat_user_tables where relname = 'rock';
relname | last_analyze
---------+--------------
rock |
(1 row)
music=> analyze verbose pop;
INFO: analyzing "eric.pop"
INFO: "pop": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
ANALYZE
music=> select relname, last_analyze from pg_stat_user_tables where relname = 'rock';
relname | last_analyze
---------+--------------
rock |
(1 row)
music=> select relname, last_analyze from pg_stat_user_tables where relname = 'pop';
relname | last_analyze
---------+-------------------------------
pop | 2016-02-18 22:31:55.666556-08
(1 row)
music=> analyze verbose rock;
INFO: analyzing "eric.rock"
INFO: "rock": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
ANALYZE
music=> select relname, last_analyze from pg_stat_user_tables where relname = 'rock';
relname | last_analyze
---------+-------------------------------
rock | 2016-02-18 22:34:16.526558-08
(1 row)