Chinaunix首页 | 论坛 | 认证专区 | 博客 登录 | 注册

Eric Gao's Bloggaoqiang.blog.chinaunix.net

Oracle MySQL PostgreSQL AIX Linux Storage

  • 博客访问: 1866256
  • 博文数量: 196
  • 博客积分: 3409
  • 博客等级: 中校
  • 技术积分: 3858
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-24 10:21
  • 认证徽章:
个人简介

● ITPUB名人堂嘉宾 ● ChinaUnix社区博客专家 ● ChinaUnix社区Oracle板块版主 ● 优酷网认证音乐牛人:EricGuitar ● SDOUG 核心成员 ●E-mail:gaoqiangdba@163.com

文章分类

全部博文(196)

文章存档

2016年(5)

2015年(39)

2014年(27)

2013年(18)

2012年(24)

2011年(63)

2010年(20)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Mysql/postgreSQL





  最近在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)


阅读(1463) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册