On 24/02/13 10:51, Mark Kirkwood wrote:
On 24/02/13 10:12, Stefan Andreatta wrote:
On 02/23/2013 09:30 PM, Jeff Janes wrote:
Moved discussion from General To Hackers.
On Sat, Feb 23, 2013 at 10:41 AM, Stefan Andreatta
<s.andrea...@synedra.com <mailto:s.andrea...@synedra.com>> wrote:
On 02/23/2013 05:10 PM, Jeff Janes wrote:
Sorry, I got tunnel vision about the how the threshold was
computed, and forgot about the thing it was compared to. There
is a "secret" data point in the stats collector
called changes_since_analyze. This is not exposed in the
pg_stat_user_tables. But I think it should be as I often have
wanted to see it.
Sounds like a very good idea to me - any way I could help to make
such a thing happen?
It should be fairly easy to implement because the other columns are
already there to show you the way, and if you want to try your hand at
hacking pgsql it would be a good introduction to doing so.
Look at each instance in the code of n_dead_dup and
pg_stat_get_dead_tuples, and those are the places where
changes_since_analyze also need to be addressed, in an analogous
manner (assuming it is isn't already there.)
git grep 'n_dead_tup'
It looks like we would need to add an SQL function to retrieve the
data, then incorporate that function into the view definitions that
make up the pg_stat_user_tables etc. views. and of course update the
regression test and the documentation.
Other than implementing it, we would need to convince other hackers
that this is desirable to have. I'm not sure how hard that would be.
I've looked in the archives to see if this idea was already considered
but rejected, but I don't see any indication that it was previously
considered.
(http://www.postgresql.org/message-id/4823.1262132...@sss.pgh.pa.us).
Cheers,
Jeff
Not being a developer, I am afraid, I will not be going to implement it
myself - nor would anybody wish so ;-)
I also searched the archives, but the closest I found is a discussion on
the Admin List starting here:
http://www.postgresql.org/message-id/626919622.7634700.1351695913466.javamail.r...@alaloop.com
On the other hand, there is quite a lot of discussion about making
autoanalyze more (or less) aggressive - which seems a difficult task to
me, when you cannot even check what's triggering your autoanalyze.
Anybody else interested?
I was asked about this exact thing the other day - it would be very
nice to have the information visible. I may take a look at doing it
(I've done some hacking on the stats system previously). However don't
let that put anyone else off - as I'll have to find the time to start :-)
I happened to be looking at the whole autovacuum/analyze setup in
another context - which reminded me about volunteering to take a look at
a patch for adding changes_since_analyze. So with probably impeccably
poor timing (smack in the middle of 9.3 beta), here is a patch that does
that (so it is probably an early 9.4 addition).
I've called the column "n_changes_since_analyze" - I can sense that
there might be discussion about how to maybe shorten that :-) , and
added a doc line for the view + updated the regression test expected input.
Regards
Mark
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b37b6c3..0ebce4e 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -951,6 +951,11 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
<entry>Estimated number of dead rows</entry>
</row>
<row>
+ <entry><structfield>n_changes_since_analyze</></entry>
+ <entry><type>bigint</></entry>
+ <entry>Estimated number of row changes (inserts + updates + deletes) since the last analyze</entry>
+ </row>
+ <row>
<entry><structfield>last_vacuum</></entry>
<entry><type>timestamp with time zone</></entry>
<entry>Last time at which this table was manually vacuumed
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index a03bfa6..05bba74 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -405,6 +405,7 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
pg_stat_get_live_tuples(C.oid) AS n_live_tup,
pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
+ pg_stat_get_changes_since_analyze(C.oid) AS n_changes_since_analyze,
pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8c1a767..8803996 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -34,6 +34,7 @@ extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_tuples_hot_updated(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_live_tuples(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_dead_tuples(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_changes_since_analyze(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_blocks_hit(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
@@ -266,6 +267,22 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
Datum
+pg_stat_get_changes_since_analyze(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->changes_since_analyze);
+
+ PG_RETURN_INT64(result);
+}
+
+
+Datum
pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 392649c..0a77fcb 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201305061
+#define CATALOG_VERSION_NO 201305151
#endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index feecbf9..a7b7adb 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2593,6 +2593,8 @@ DATA(insert OID = 2878 ( pg_stat_get_live_tuples PGNSP PGUID 12 1 0 0 0 f f f f
DESCR("statistics: number of live tuples");
DATA(insert OID = 2879 ( pg_stat_get_dead_tuples 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_dead_tuples _null_ _null_ _null_ ));
DESCR("statistics: number of dead tuples");
+DATA(insert OID = 3177 ( pg_stat_get_changes_since_analyze 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_changes_since_analyze _null_ _null_ _null_ ));
+DESCR("statistics: number of tuples changed since last analyze");
DATA(insert OID = 1934 ( pg_stat_get_blocks_fetched 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_blocks_fetched _null_ _null_ _null_ ));
DESCR("statistics: number of blocks fetched");
DATA(insert OID = 1935 ( pg_stat_get_blocks_hit 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_blocks_hit _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index dc3c8a8..d3e132a 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1626,6 +1626,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, +
| pg_stat_get_live_tuples(c.oid) AS n_live_tup, +
| pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, +
+ | pg_stat_get_changes_since_analyze(c.oid) AS n_changes_since_analyze, +
| pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, +
| pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, +
| pg_stat_get_last_analyze_time(c.oid) AS last_analyze, +
@@ -1720,6 +1721,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_all_tables.n_tup_hot_upd, +
| pg_stat_all_tables.n_live_tup, +
| pg_stat_all_tables.n_dead_tup, +
+ | pg_stat_all_tables.n_changes_since_analyze, +
| pg_stat_all_tables.last_vacuum, +
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
@@ -1762,6 +1764,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_all_tables.n_tup_hot_upd, +
| pg_stat_all_tables.n_live_tup, +
| pg_stat_all_tables.n_dead_tup, +
+ | pg_stat_all_tables.n_changes_since_analyze, +
| pg_stat_all_tables.last_vacuum, +
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
