Re: ANALYZE'ing table hierarchies

Hi,Nunks

I have a test just now,as follows:

Hope to help you!

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
music-> ;
 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
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)

---

gaoqiangdba@163.com

 

From: nunks

Date: 2016-02-19 10:02

To: pgsql-admin

Subject: [ADMIN] ANALYZE'ing table hierarchies

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