(2013/08/08 20:52), Vik Fearing wrote:
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.
I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help appreciated.
I have reviewed the patch.
Succeeded to build with the latest HEAD, and passed the regression
tests.
Looks good enough, and I'd like to add a test case here, not only
for the view definition, but also working correctly.
Please take a look at attached one.
Regards,
--
Satoshi Nagayasu <sn...@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
diff --git a/src/test/regress/expected/stats.out
b/src/test/regress/expected/stats.out
index 56bace1..ba614b2 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -28,7 +28,13 @@ SELECT pg_sleep(2.0);
CREATE TEMP TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
- (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+ (b.idx_blks_read + b.idx_blks_hit) AS idx_blks,
+ coalesce(t.last_vacuum, now()) AS last_vacuum,
+ coalesce(t.last_analyze, now()) AS last_analyze,
+ coalesce(t.last_cluster, now()) AS last_cluster,
+ t.vacuum_count,
+ t.analyze_count,
+ t.cluster_count
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
@@ -111,4 +117,27 @@ SELECT st.heap_blks_read + st.heap_blks_hit >=
pr.heap_blks + cl.relpages,
t | t
(1 row)
+-- table maintenance stats
+ANALYZE tenk2;
+VACUUM tenk2;
+CLUSTER tenk2 USING tenk2_unique1;
+SELECT pg_sleep(1.0);
+ pg_sleep
+----------
+
+(1 row)
+
+SELECT st.last_vacuum > pr.last_vacuum,
+ st.last_analyze > pr.last_analyze,
+ st.last_cluster > pr.last_cluster,
+ st.vacuum_count > pr.vacuum_count,
+ st.analyze_count > pr.analyze_count,
+ st.cluster_count > pr.cluster_count
+ FROM pg_stat_user_tables AS st, prevstats pr
+ WHERE st.relname='tenk2';
+ ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------+----------+----------
+ t | t | t | t | t | t
+(1 row)
+
-- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index bb349b2..71e5e27 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -22,7 +22,13 @@ SELECT pg_sleep(2.0);
CREATE TEMP TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
- (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+ (b.idx_blks_read + b.idx_blks_hit) AS idx_blks,
+ coalesce(t.last_vacuum, now()) AS last_vacuum,
+ coalesce(t.last_analyze, now()) AS last_analyze,
+ coalesce(t.last_cluster, now()) AS last_cluster,
+ t.vacuum_count,
+ t.analyze_count,
+ t.cluster_count
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
@@ -81,4 +87,20 @@ SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks
+ cl.relpages,
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
+-- table maintenance stats
+ANALYZE tenk2;
+VACUUM tenk2;
+CLUSTER tenk2 USING tenk2_unique1;
+
+SELECT pg_sleep(1.0);
+
+SELECT st.last_vacuum > pr.last_vacuum,
+ st.last_analyze > pr.last_analyze,
+ st.last_cluster > pr.last_cluster,
+ st.vacuum_count > pr.vacuum_count,
+ st.analyze_count > pr.analyze_count,
+ st.cluster_count > pr.cluster_count
+ FROM pg_stat_user_tables AS st, prevstats pr
+ WHERE st.relname='tenk2';
+
-- End of Stats Test
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
