On 09/16/2013 08:26 AM, Satoshi Nagayasu wrote:
> (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.
Thank you for your review.
> 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.
Looks good to me. Attached is a rebased patch with those tests added.
--
Vik
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***************
*** 979,984 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 979,989 ----
daemon</entry>
</row>
<row>
+ <entry><structfield>last_cluster</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Last time at which <command>CLUSTER</> was issued on this table</entry>
+ </row>
+ <row>
<entry><structfield>vacuum_count</></entry>
<entry><type>bigint</></entry>
<entry>Number of times this table has been manually vacuumed
***************
*** 1001,1006 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 1006,1016 ----
<entry>Number of times this table has been analyzed by the autovacuum
daemon</entry>
</row>
+ <row>
+ <entry><structfield>cluster_count</></entry>
+ <entry><type>bigint</></entry>
+ <entry>Number of times <command>CLUSTER</> has been issued on this table</entry>
+ </row>
</tbody>
</tgroup>
</table>
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 410,419 **** CREATE VIEW pg_stat_all_tables AS
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
! pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
--- 410,421 ----
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
+ pg_stat_get_last_cluster_time(C.oid) as last_cluster,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
! pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
! pg_stat_get_cluster_count(C.oid) AS cluster_count
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
*** a/src/backend/commands/cluster.c
--- b/src/backend/commands/cluster.c
***************
*** 35,40 ****
--- 35,41 ----
#include "commands/vacuum.h"
#include "miscadmin.h"
#include "optimizer/planner.h"
+ #include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
***************
*** 407,412 **** cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose,
--- 408,417 ----
verbose);
/* NB: rebuild_relation does heap_close() on OldHeap */
+
+ /* Report CLUSTER to the stats collector, but not VACUUM FULL */
+ if (indexOid != InvalidOid)
+ pgstat_report_cluster(OldHeap);
}
/*
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***************
*** 292,297 **** static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
--- 292,298 ----
static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+ static void pgstat_recv_cluster(PgStat_MsgCluster *msg, int len);
static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
***************
*** 1385,1390 **** pgstat_report_analyze(Relation rel,
--- 1386,1412 ----
}
/* --------
+ * pgstat_report_cluster() -
+ *
+ * Tell the collector about the table we just CLUSTERed.
+ * --------
+ */
+ void
+ pgstat_report_cluster(Relation rel)
+ {
+ PgStat_MsgCluster msg;
+
+ if (pgStatSock == PGINVALID_SOCKET)
+ return;
+
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_CLUSTER);
+ msg.m_databaseid = rel->rd_rel->relisshared ? InvalidOid : MyDatabaseId;
+ msg.m_tableoid = RelationGetRelid(rel);
+ msg.m_clustertime = GetCurrentTimestamp();
+ pgstat_send(&msg, sizeof(msg));
+ }
+
+ /* --------
* pgstat_report_recovery_conflict() -
*
* Tell the collector about a Hot Standby recovery conflict.
***************
*** 3266,3271 **** PgstatCollectorMain(int argc, char *argv[])
--- 3288,3297 ----
pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
break;
+ case PGSTAT_MTYPE_CLUSTER:
+ pgstat_recv_cluster((PgStat_MsgCluster *) &msg, len);
+ break;
+
case PGSTAT_MTYPE_BGWRITER:
pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
break;
***************
*** 3486,3491 **** pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
--- 3512,3519 ----
result->analyze_count = 0;
result->autovac_analyze_timestamp = 0;
result->autovac_analyze_count = 0;
+ result->cluster_timestamp = 0;
+ result->cluster_count = 0;
}
return result;
***************
*** 4558,4563 **** pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
--- 4586,4593 ----
tabentry->analyze_count = 0;
tabentry->autovac_analyze_timestamp = 0;
tabentry->autovac_analyze_count = 0;
+ tabentry->cluster_timestamp = 0;
+ tabentry->cluster_count = 0;
}
else
{
***************
*** 4857,4862 **** pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
--- 4887,4916 ----
/* ----------
+ * pgstat_recv_cluster() -
+ *
+ * Process a CLUSTER message.
+ * ----------
+ */
+ static void
+ pgstat_recv_cluster(PgStat_MsgCluster *msg, int len)
+ {
+ PgStat_StatDBEntry *dbentry;
+ PgStat_StatTabEntry *tabentry;
+
+ /*
+ * Store the data in the table's hashtable entry.
+ */
+ dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+ tabentry = pgstat_get_tab_entry(dbentry, msg->m_tableoid, true);
+
+ tabentry->cluster_timestamp = msg->m_clustertime;
+ tabentry->cluster_count++;
+ }
+
+
+ /* ----------
* pgstat_recv_bgwriter() -
*
* Process a BGWRITER message.
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 41,50 **** extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
--- 41,52 ----
extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_last_cluster_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_vacuum_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_analyze_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_cluster_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_calls(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_total_time(PG_FUNCTION_ARGS);
***************
*** 386,391 **** pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
--- 388,411 ----
}
Datum
+ pg_stat_get_last_cluster_time(PG_FUNCTION_ARGS)
+ {
+ Oid relid = PG_GETARG_OID(0);
+ TimestampTz result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = tabentry->cluster_timestamp;
+
+ if (result == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(result);
+ }
+
+ Datum
pg_stat_get_vacuum_count(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
***************
*** 446,451 **** pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS)
--- 466,486 ----
}
Datum
+ pg_stat_get_cluster_count(PG_FUNCTION_ARGS)
+ {
+ Oid relid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (tabentry->cluster_count);
+
+ PG_RETURN_INT64(result);
+ }
+
+ Datum
pg_stat_get_function_calls(PG_FUNCTION_ARGS)
{
Oid funcid = PG_GETARG_OID(0);
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2607,2612 **** DATA(insert OID = 2783 ( pg_stat_get_last_analyze_time PGNSP PGUID 12 1 0 0 0 f
--- 2607,2614 ----
DESCR("statistics: last manual analyze time for a table");
DATA(insert OID = 2784 ( pg_stat_get_last_autoanalyze_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_autoanalyze_time _null_ _null_ _null_ ));
DESCR("statistics: last auto analyze time for a table");
+ DATA(insert OID = 3178 ( pg_stat_get_last_cluster_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_cluster_time _null_ _null_ _null_ ));
+ DESCR("statistics: last CLUSTER time for a table");
DATA(insert OID = 3054 ( pg_stat_get_vacuum_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_vacuum_count _null_ _null_ _null_ ));
DESCR("statistics: number of manual vacuums for a table");
DATA(insert OID = 3055 ( pg_stat_get_autovacuum_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autovacuum_count _null_ _null_ _null_ ));
***************
*** 2615,2620 **** DATA(insert OID = 3056 ( pg_stat_get_analyze_count PGNSP PGUID 12 1 0 0 0 f f f
--- 2617,2624 ----
DESCR("statistics: number of manual analyzes for a table");
DATA(insert OID = 3057 ( pg_stat_get_autoanalyze_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autoanalyze_count _null_ _null_ _null_ ));
DESCR("statistics: number of auto analyzes for a table");
+ DATA(insert OID = 3179 ( pg_stat_get_cluster_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_cluster_count _null_ _null_ _null_ ));
+ DESCR("statistics: number of CLUSTERs for a table");
DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 0 f f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ ));
DESCR("statistics: currently active backend IDs");
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
***************
*** 44,49 **** typedef enum StatMsgType
--- 44,50 ----
PGSTAT_MTYPE_AUTOVAC_START,
PGSTAT_MTYPE_VACUUM,
PGSTAT_MTYPE_ANALYZE,
+ PGSTAT_MTYPE_CLUSTER,
PGSTAT_MTYPE_BGWRITER,
PGSTAT_MTYPE_FUNCSTAT,
PGSTAT_MTYPE_FUNCPURGE,
***************
*** 336,341 **** typedef struct PgStat_MsgVacuum
--- 337,355 ----
/* ----------
+ * PgStat_MsgCluster Sent by the backend after CLUSTER
+ * ----------
+ */
+ typedef struct PgStat_MsgCluster
+ {
+ PgStat_MsgHdr m_hdr;
+ Oid m_databaseid;
+ Oid m_tableoid;
+ TimestampTz m_clustertime;
+ } PgStat_MsgCluster;
+
+
+ /* ----------
* PgStat_MsgAnalyze Sent by the backend or autovacuum daemon
* after ANALYZE
* ----------
***************
*** 590,595 **** typedef struct PgStat_StatTabEntry
--- 604,611 ----
PgStat_Counter analyze_count;
TimestampTz autovac_analyze_timestamp; /* autovacuum initiated */
PgStat_Counter autovac_analyze_count;
+ TimestampTz cluster_timestamp;
+ PgStat_Counter cluster_count;
} PgStat_StatTabEntry;
***************
*** 776,781 **** extern void pgstat_report_vacuum(Oid tableoid, bool shared,
--- 792,798 ----
PgStat_Counter tuples);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples);
+ extern void pgstat_report_cluster(Relation rel);
extern void pgstat_report_recovery_conflict(int reason);
extern void pgstat_report_deadlock(void);
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1631,1640 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, +
| pg_stat_get_last_analyze_time(c.oid) AS last_analyze, +
| pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, +
| pg_stat_get_vacuum_count(c.oid) AS vacuum_count, +
| pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, +
| pg_stat_get_analyze_count(c.oid) AS analyze_count, +
! | pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count +
| FROM ((pg_class c +
| LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) +
| LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) +
--- 1631,1642 ----
| pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, +
| pg_stat_get_last_analyze_time(c.oid) AS last_analyze, +
| pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, +
+ | pg_stat_get_last_cluster_time(c.oid) AS last_cluster, +
| pg_stat_get_vacuum_count(c.oid) AS vacuum_count, +
| pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, +
| pg_stat_get_analyze_count(c.oid) AS analyze_count, +
! | pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count, +
! | pg_stat_get_cluster_count(c.oid) AS cluster_count +
| FROM ((pg_class c +
| LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) +
| LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) +
***************
*** 1726,1735 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, +
--- 1728,1739 ----
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
+ | pg_stat_all_tables.last_cluster, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count, +
! | pg_stat_all_tables.cluster_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, +
***************
*** 1769,1778 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
pg_stat_xact_all_tables | SELECT c.oid AS relid, +
--- 1773,1784 ----
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
+ | pg_stat_all_tables.last_cluster, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count, +
! | pg_stat_all_tables.cluster_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
pg_stat_xact_all_tables | SELECT c.oid AS relid, +
*** a/src/test/regress/expected/stats.out
--- b/src/test/regress/expected/stats.out
***************
*** 28,34 **** 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
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';
--- 28,40 ----
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,
! 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,114 **** SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
--- 117,143 ----
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
*** a/src/test/regress/sql/stats.sql
--- b/src/test/regress/sql/stats.sql
***************
*** 22,28 **** 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
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';
--- 22,34 ----
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,
! 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,84 **** SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
--- 87,106 ----
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
