PostgreSQL Analyze分区表:主表与子表的统计信息问题

  • 时间:
  • 浏览:1
  • 来源:uu快3玩法_uu快3新平台_棋牌

 id |      name       | style 

music$>           INSERT INTO pop VALUES (NEW.*);

music=> create index music_pop_id on pop (id);

music=> insert into music values(2,'Have a Nice Day','pop');

INFO:  "pop": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows

CREATE TABLE

(2 rows)

(1 row)

 id |      name       | style 

CREATE TABLE

(1 row)

---------+-------------------------------

(1 row)

  以下是实验帕累托图:

music-> FOR EACH row EXECUTE PROCEDURE music_insert_trigger() ;

INFO:  "pop": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows

CREATE INDEX

music$>       ELSEIF (NEW.style = 'pop') THEN

 relname | last_analyze 

music$>       RETURN NULL;

---------+-------------------------------

ANALYZE

 id |  name  | style 

music=> CREATE TRIGGER insert_music_trigger 

INSERT 0 0

  2 | Have a Nice Day | pop

music=> create index music_jazz_id on jazz (id);

              pop,

music=> select relname, last_analyze from pg_stat_user_tables where relname = 'music';

 relname | last_analyze 

music=>  create table jazz (check(style = 'jazz')) inherits(music);

music$>       ELSEIF (NEW.style = 'pop') THEN

----+-----------------+-------

ANALYZE

music$>       ELSEIF (NEW.style = 'jazz') THEN

music=> select relname, last_analyze from pg_stat_user_tables where relname = 'rock';

music$>           INSERT INTO jazz VALUES (NEW.*);

music$>           INSERT INTO rock VALUES (NEW.*);

music->  $$

(1 row)

 pop     | 2016-02-18 22:31:55.666556-08

                          Table "eric.music"

--------+---------+-----------+----------+--------------+-------------

  为此测试了一下,发现无论主表还是子表,要能 单独的对其进行analyze才可不要能 更新其最新的统计信息。

music=> create table rock (check(style = 'rock')) inherits(music);

  1 | 21 Gun          | rock

music=> analyze verbose music ;

----+--------+-------

INSERT 0 0

 name   | text    |           | extended |              | 

CREATE TABLE

INFO:  "rock": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows

----+-----------------+-------

music$>       END IF;

music=> analyze verbose rock;

music=> create table music(id int,name text,style text);

 pop     | 

CREATE TRIGGER

CREATE INDEX

music$>           INSERT INTO classic VALUES (NEW.*);

---------+--------------

---------+-------------------------------

music->  LANGUAGE plpgsql ;

  最近在PostgreSQL的Mail List中参与讨论了另4个 什么的大问题: ANALYZE'ing table hierarchies。

music$>  END;

music=> create table classic (check(style = 'classic')) inherits(music);

(1 row)

 relname |         last_analyze          

music$>           INSERT INTO pop VALUES (NEW.*);

music$>       IF (NEW.style = 'rock') THEN

music=> select relname, last_analyze from pg_stat_user_tables where relname = 'pop';

 style  | text    |           | extended |              | 

 Column |  Type   | Modifiers | Storage  | Stats target | Description 

music=> select * from rock;

CREATE TABLE

(1 row)

---------+--------------

music$>       ELSEIF (NEW.style = 'classic') THEN

music=> select * from pop;

INFO:  analyzing "eric.music" inheritance tree

music=> select relname, last_analyze from pg_stat_user_tables where relname = 'rock';

CREATE INDEX

              rock

INFO:  analyzing "eric.music"

 rock    | 

music=> analyze verbose pop;

---------+--------------

(1 row)

CREATE TABLE

music=> select relname, last_analyze from pg_stat_user_tables where relname = 'pop';

 music   | 2016-02-18 22:29:56.528758-08

INFO:  "music": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

  2 | Have a Nice Day | pop

 relname | last_analyze 

INFO:  "rock": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows

music=>  create index music_rock_id on rock (id);

 relname |         last_analyze          

music=> insert into music values(1,'21 Gun','rock');

music=> \dS+ music

INFO:  analyzing "eric.pop"

ANALYZE

INFO:  analyzing "eric.rock"

music=> select relname, last_analyze from pg_stat_user_tables where relname = 'rock';

Child tables: classic,

music=> select * from music;

 relname |         last_analyze          

 id     | integer |           | plain    |              | 

  老外视频认为在Analyze分区表的并且 ,不到看过主表的analyze日期更新到最新,否则子表的日期这样变化,他认为analyze应该是在分析主表的并且 会将与之相关的子表同去更新。

              jazz,

CREATE FUNCTION

music=> create table pop (check(style = 'pop')) inherits(music);

music$>  $$

(1 row)

 rock    | 

 rock    | 2016-02-18 22:34:16.526558-08

music=>  CREATE OR REPLACE FUNCTION music_insert_trigger()

music=> create index music_classic_id on classic (id);

  1 | 21 Gun | rock

music-> BEFORE INSERT ON  music

CREATE INDEX

music->  RETURNS TRIGGER AS 

music$>  BEGIN