From: | nunks <nunks(dot)lol(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | ANALYZE'ing table hierarchies |
Date: | 2016-02-19 02:02:30 |
Message-ID: | CACq6szTXye=LzyFHnfYe-zZFZ-=TTG45uECs_o5_qOArfB=RZQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | ALEXANDER JOSE | 2016-02-19 02:39:17 | Status DISCARD ALL |
Previous Message | drum.lucas@gmail.com | 2016-02-19 01:48:31 | Re: [TIPS] Tuning PostgreSQL 9.2 |