Attached is a new version of the bgwriter stats patch. Per previous
discussion, now uses the stats system only. Introduces a new stats message
for bgwriter, and also introduces a "global stats" part of the stats file
for statistics not bound to a database or table.

I've included a couple of more counters per ideas from Greg Smith in his
logging patch.

Unless there are objections, I'll go ahead and apply this version of the
patch (once the documentation is written, of course).

//Magnus

Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.36
diff -c -r1.36 system_views.sql
*** src/backend/catalog/system_views.sql        16 Mar 2007 17:57:36 -0000      
1.36
--- src/backend/catalog/system_views.sql        29 Mar 2007 13:21:54 -0000
***************
*** 364,366 ****
--- 364,376 ----
              pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
              pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted
      FROM pg_database D;
+ 
+ CREATE VIEW pg_stat_bgwriter AS
+       SELECT
+                       pg_stat_get_bgwriter_timed_checkpoints() AS 
checkpoints_timed,
+                       pg_stat_get_bgwriter_requested_checkpoints() AS 
checkpoints_req,
+                       pg_stat_get_bgwriter_buf_written_checkpoints() AS 
buffers_checkpoint,
+                       pg_stat_get_bgwriter_buf_written_lru() AS buffers_lru,
+                       pg_stat_get_bgwriter_buf_written_all() AS buffers_all,
+                       pg_stat_get_bgwriter_maxwritten_lru() AS maxwritten_lru,
+                       pg_stat_get_bgwriter_maxwritten_all() AS maxwritten_all;
Index: src/backend/postmaster/bgwriter.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/bgwriter.c,v
retrieving revision 1.36
diff -c -r1.36 bgwriter.c
*** src/backend/postmaster/bgwriter.c   17 Jan 2007 16:25:01 -0000      1.36
--- src/backend/postmaster/bgwriter.c   29 Mar 2007 13:21:57 -0000
***************
*** 50,55 ****
--- 50,56 ----
  #include "access/xlog_internal.h"
  #include "libpq/pqsignal.h"
  #include "miscadmin.h"
+ #include "pgstat.h"
  #include "postmaster/bgwriter.h"
  #include "storage/fd.h"
  #include "storage/freespace.h"
***************
*** 125,130 ****
--- 126,138 ----
  static BgWriterShmemStruct *BgWriterShmem;
  
  /*
+  * BgWriter statistic counters, as sent to the stats collector
+  * Stored directly in a stats message structure so it can be sent
+  * without nedeing to copy things around.
+  */
+ PgStat_MsgBgWriter BgWriterStats;
+ 
+ /*
   * GUC parameters
   */
  int                   BgWriterDelay = 200;
***************
*** 243,248 ****
--- 251,261 ----
        MemoryContextSwitchTo(bgwriter_context);
  
        /*
+        * Initialize statistics counters to zero
+        */
+       memset(&BgWriterStats, 0, sizeof(BgWriterStats));
+ 
+       /*
         * If an exception is encountered, processing resumes here.
         *
         * See notes in postgres.c about the design of this coding.
***************
*** 354,359 ****
--- 367,373 ----
                        checkpoint_requested = false;
                        do_checkpoint = true;
                        force_checkpoint = true;
+                       BgWriterStats.m_requested_checkpoints++;
                }
                if (shutdown_requested)
                {
***************
*** 376,382 ****
--- 390,400 ----
                now = time(NULL);
                elapsed_secs = now - last_checkpoint_time;
                if (elapsed_secs >= CheckPointTimeout)
+               {
                        do_checkpoint = true;
+                       if (!force_checkpoint)
+                               BgWriterStats.m_timed_checkpoints++;
+               }
  
                /*
                 * Do a checkpoint if requested, otherwise do one cycle of
***************
*** 474,479 ****
--- 492,502 ----
                }
  
                /*
+                * Send of activity statistics to the stats collector
+                */
+               pgstat_send_bgwriter();
+ 
+               /*
                 * Nap for the configured time, or sleep for 10 seconds if 
there is no
                 * bgwriter activity configured.
                 *
***************
*** 789,791 ****
--- 812,815 ----
  
        END_CRIT_SECTION();
  }
+ 
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.151
diff -c -r1.151 pgstat.c
*** src/backend/postmaster/pgstat.c     28 Mar 2007 22:17:12 -0000      1.151
--- src/backend/postmaster/pgstat.c     29 Mar 2007 13:22:00 -0000
***************
*** 135,140 ****
--- 135,150 ----
  static PgBackendStatus *localBackendStatusTable = NULL;
  static int    localNumBackends = 0;
  
+ /*
+  * BgWriter global statistics counters
+  */
+ extern PgStat_MsgBgWriter BgWriterStats;
+ 
+ /*
+  * Cluster wide statistics, kept in the stats process
+  */
+ static PgStat_GlobalStats globalStats;
+ 
  static volatile bool need_exit = false;
  static volatile bool need_statwrite = false;
  
***************
*** 171,176 ****
--- 181,187 ----
  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_bgwriter(PgStat_MsgBgWriter *msg, int len);
  
  
  /* ------------------------------------------------------------
***************
*** 1288,1293 ****
--- 1299,1320 ----
        return localNumBackends;
  }
  
+ /*
+  * ---------
+  * pgstat_fetch_global() -
+  *
+  *  Support function for the SQL-callable pgstat* functions. Returns
+  *  a pointer to the global statistics struct.
+  * ---------
+  */
+ PgStat_GlobalStats *
+ pgstat_fetch_global(void)
+ {
+       backend_read_statsfile();
+ 
+       return &globalStats;
+ }
+ 
  
  /* ------------------------------------------------------------
   * Functions for management of the shared-memory PgBackendStatus array
***************
*** 1646,1651 ****
--- 1673,1713 ----
  #endif
  }
  
+ /* ----------
+  * pgstat_send_bgwriter() -
+  *
+  * Send bgwriter statistics
+  */
+ void
+ pgstat_send_bgwriter(void)
+ {
+       /*
+        * This function can be called even if nothing at all happende.
+        * In this case, avoid sending a completely empty message to
+        * the stats collector.
+        */
+       if (BgWriterStats.m_timed_checkpoints == 0 &&
+               BgWriterStats.m_requested_checkpoints == 0 &&
+               BgWriterStats.m_buf_written_checkpoints == 0 &&
+               BgWriterStats.m_buf_written_lru == 0 &&
+               BgWriterStats.m_buf_written_all == 0 &&
+               BgWriterStats.m_maxwritten_lru == 0 &&
+               BgWriterStats.m_maxwritten_all == 0)
+               return;
+ 
+       /*
+        * Prepare and send off the message
+        */
+       pgstat_setheader(&BgWriterStats.m_hdr, PGSTAT_MTYPE_BGWRITER);
+       pgstat_send(&BgWriterStats, sizeof(BgWriterStats));
+ 
+       /*
+        * Clear out the bgwriter statistics buffer, so it can be
+        * re-used.
+        */
+       memset(&BgWriterStats, 0, sizeof(BgWriterStats));
+ }
+ 
  
  /* ----------
   * PgstatCollectorMain() -
***************
*** 1892,1897 ****
--- 1954,1963 ----
                                        pgstat_recv_analyze((PgStat_MsgAnalyze 
*) &msg, len);
                                        break;
  
+                               case PGSTAT_MTYPE_BGWRITER:
+                                       
pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
+                                       break;
+ 
                                default:
                                        break;
                        }
***************
*** 2031,2036 ****
--- 2097,2107 ----
        fwrite(&format_id, sizeof(format_id), 1, fpout);
  
        /*
+        * Write global stats
+        */
+       fwrite(&globalStats, sizeof(globalStats), 1, fpout);
+ 
+       /*
         * Walk through the database table.
         */
        hash_seq_init(&hstat, pgStatDBHash);
***************
*** 2133,2138 ****
--- 2204,2215 ----
                                                 HASH_ELEM | HASH_FUNCTION | 
HASH_CONTEXT);
  
        /*
+        * Clear out global statistics in case they can't be loaded from an 
existing
+        * statsfile
+        */
+       memset(&globalStats, 0, sizeof(globalStats));
+ 
+       /*
         * Try to open the status file. If it doesn't exist, the backends simply
         * return zero for anything and the collector simply starts from scratch
         * with empty counters.
***************
*** 2152,2157 ****
--- 2229,2244 ----
        }
  
        /*
+        * Read global stats
+        */
+       if (fread(&globalStats, 1, sizeof(globalStats), fpin) != 
sizeof(globalStats))
+       {
+               ereport(pgStatRunningInCollector ? LOG : WARNING,
+                               (errmsg("corrupted pgstat.stat file")));
+               goto done;
+       }
+ 
+       /*
         * We found an existing collector stats file. Read it and put all the
         * hashtable entries into place.
         */
***************
*** 2656,2658 ****
--- 2743,2764 ----
        tabentry->n_dead_tuples = msg->m_dead_tuples;
        tabentry->last_anl_tuples = msg->m_live_tuples + msg->m_dead_tuples;
  }
+ 
+ 
+ /* ----------
+  * pgstat_recv_bgwriter() -
+  *
+  *    Process a BGWRITER message.
+  * ----------
+  */
+ static void
+ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
+ {
+       globalStats.timed_checkpoints += msg->m_timed_checkpoints;
+       globalStats.requested_checkpoints += msg->m_requested_checkpoints;
+       globalStats.buf_written_checkpoints += msg->m_buf_written_checkpoints;
+       globalStats.buf_written_lru += msg->m_buf_written_lru;
+       globalStats.buf_written_all += msg->m_buf_written_all;
+       globalStats.maxwritten_lru += msg->m_maxwritten_lru;
+       globalStats.maxwritten_all += msg->m_maxwritten_all;
+ }
Index: src/backend/storage/buffer/bufmgr.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/storage/buffer/bufmgr.c,v
retrieving revision 1.215
diff -c -r1.215 bufmgr.c
*** src/backend/storage/buffer/bufmgr.c 1 Feb 2007 19:10:27 -0000       1.215
--- src/backend/storage/buffer/bufmgr.c 29 Mar 2007 13:22:02 -0000
***************
*** 72,78 ****
                                                                 * bufmgr */
  long          NDirectFileWrite;       /* e.g., I/O in psort and hashjoin. */
  
- 
  /* local state for StartBufferIO and related functions */
  static volatile BufferDesc *InProgressBuf = NULL;
  static bool IsForInput;
--- 72,77 ----
***************
*** 80,85 ****
--- 79,88 ----
  /* local state for LockBufferForCleanup */
  static volatile BufferDesc *PinCountWaitBuf = NULL;
  
+ /* statistics for bgwriter. The contents of this variable only makes
+  * sense in the bgwriter */
+ extern PgStat_MsgBgWriter BgWriterStats;
+ 
  
  static bool PinBuffer(volatile BufferDesc *buf);
  static void PinBuffer_Locked(volatile BufferDesc *buf);
***************
*** 964,969 ****
--- 968,975 ----
        {
                if (SyncOneBuffer(buf_id, false))
                {
+                       BgWriterStats.m_buf_written_checkpoints++;
+ 
                        /*
                         * If in bgwriter, absorb pending fsync requests after 
each
                         * WRITES_PER_ABSORB write operations, to prevent 
overflow of the
***************
*** 1027,1035 ****
--- 1033,1045 ----
                        if (SyncOneBuffer(buf_id1, false))
                        {
                                if (++num_written >= bgwriter_all_maxpages)
+                               {
+                                       BgWriterStats.m_maxwritten_all++;
                                        break;
+                               }
                        }
                }
+               BgWriterStats.m_buf_written_all += num_written;
        }
  
        /*
***************
*** 1048,1058 ****
--- 1058,1072 ----
                        if (SyncOneBuffer(buf_id2, true))
                        {
                                if (++num_written >= bgwriter_lru_maxpages)
+                               {
+                                       BgWriterStats.m_maxwritten_lru++;
                                        break;
+                               }
                        }
                        if (++buf_id2 >= NBuffers)
                                buf_id2 = 0;
                }
+               BgWriterStats.m_buf_written_lru += num_written;
        }
  }
  
Index: src/backend/utils/adt/pgstatfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/pgstatfuncs.c,v
retrieving revision 1.40
diff -c -r1.40 pgstatfuncs.c
*** src/backend/utils/adt/pgstatfuncs.c 16 Mar 2007 17:57:36 -0000      1.40
--- src/backend/utils/adt/pgstatfuncs.c 29 Mar 2007 13:22:03 -0000
***************
*** 61,69 ****
--- 61,79 ----
  extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
  
+ extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_buf_written_lru(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_buf_written_all(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_maxwritten_lru(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_maxwritten_all(PG_FUNCTION_ARGS);
+ 
  extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS);
  extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
  
+ /* globals defined in bgwriter.c */
+ extern PgStat_MsgBgWriter bgwriterStats;
  
  Datum
  pg_stat_get_numscans(PG_FUNCTION_ARGS)
***************
*** 756,761 ****
--- 766,813 ----
        PG_RETURN_INT64(result);
  }
  
+ Datum
+ pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
+ {
+       PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints);
+ }
+ 
+ Datum
+ pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS)
+ {
+       PG_RETURN_INT64(pgstat_fetch_global()->requested_checkpoints);
+ }
+ 
+ Datum
+ pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
+ {
+       PG_RETURN_INT64(pgstat_fetch_global()->buf_written_checkpoints);
+ }
+ 
+ Datum
+ pg_stat_get_bgwriter_buf_written_lru(PG_FUNCTION_ARGS)
+ {
+       PG_RETURN_INT64(pgstat_fetch_global()->buf_written_lru);
+ }
+ 
+ Datum
+ pg_stat_get_bgwriter_buf_written_all(PG_FUNCTION_ARGS)
+ {
+       PG_RETURN_INT64(pgstat_fetch_global()->buf_written_all);
+ }
+ 
+ Datum
+ pg_stat_get_bgwriter_maxwritten_lru(PG_FUNCTION_ARGS)
+ {
+       PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_lru);
+ }
+ 
+ Datum
+ pg_stat_get_bgwriter_maxwritten_all(PG_FUNCTION_ARGS)
+ {
+       PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_all);
+ }
+ 
  
  /* Discard the active statistics snapshot */
  Datum
Index: src/include/pgstat.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/pgstat.h,v
retrieving revision 1.56
diff -c -r1.56 pgstat.h
*** src/include/pgstat.h        22 Mar 2007 19:53:31 -0000      1.56
--- src/include/pgstat.h        29 Mar 2007 13:22:05 -0000
***************
*** 30,36 ****
        PGSTAT_MTYPE_RESETCOUNTER,
        PGSTAT_MTYPE_AUTOVAC_START,
        PGSTAT_MTYPE_VACUUM,
!       PGSTAT_MTYPE_ANALYZE
  } StatMsgType;
  
  /* ----------
--- 30,37 ----
        PGSTAT_MTYPE_RESETCOUNTER,
        PGSTAT_MTYPE_AUTOVAC_START,
        PGSTAT_MTYPE_VACUUM,
!       PGSTAT_MTYPE_ANALYZE,
!       PGSTAT_MTYPE_BGWRITER
  } StatMsgType;
  
  /* ----------
***************
*** 213,218 ****
--- 214,237 ----
  
  
  /* ----------
+  * PgStat_MsgBgWriter           Sent by the bgwriter to update statistics.
+  * ----------
+  */
+ typedef struct PgStat_MsgBgWriter
+ {
+       PgStat_MsgHdr m_hdr;
+ 
+       PgStat_Counter  m_timed_checkpoints;
+       PgStat_Counter  m_requested_checkpoints;
+       PgStat_Counter  m_buf_written_checkpoints;
+       PgStat_Counter  m_buf_written_lru;
+       PgStat_Counter  m_buf_written_all;
+       PgStat_Counter  m_maxwritten_lru;
+       PgStat_Counter  m_maxwritten_all;
+ } PgStat_MsgBgWriter;
+ 
+ 
+ /* ----------
   * PgStat_Msg                                 Union over all possible 
messages.
   * ----------
   */
***************
*** 227,232 ****
--- 246,252 ----
        PgStat_MsgAutovacStart msg_autovacuum;
        PgStat_MsgVacuum msg_vacuum;
        PgStat_MsgAnalyze msg_analyze;
+       PgStat_MsgBgWriter msg_bgwriter;
  } PgStat_Msg;
  
  
***************
*** 297,302 ****
--- 317,337 ----
  } PgStat_StatTabEntry;
  
  
+ /*
+  * Global statistics kept in the stats collector
+  */
+ typedef struct PgStat_GlobalStats
+ {
+       PgStat_Counter  timed_checkpoints;
+       PgStat_Counter  requested_checkpoints;
+       PgStat_Counter  buf_written_checkpoints;
+       PgStat_Counter  buf_written_lru;
+       PgStat_Counter  buf_written_all;
+       PgStat_Counter  maxwritten_lru;
+       PgStat_Counter  maxwritten_all;
+ } PgStat_GlobalStats;
+ 
+ 
  /* ----------
   * Shared-memory data structures
   * ----------
***************
*** 478,483 ****
--- 513,519 ----
  
  extern void pgstat_count_xact_commit(void);
  extern void pgstat_count_xact_rollback(void);
+ extern void pgstat_send_bgwriter(void);
  
  /* ----------
   * Support functions for the SQL-callable functions to
***************
*** 488,492 ****
--- 524,529 ----
  extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
  extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
  extern int    pgstat_fetch_stat_numbackends(void);
+ extern PgStat_GlobalStats *pgstat_fetch_global(void);
  
  #endif   /* PGSTAT_H */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.451
diff -c -r1.451 pg_proc.h
*** src/include/catalog/pg_proc.h       27 Mar 2007 23:21:11 -0000      1.451
--- src/include/catalog/pg_proc.h       29 Mar 2007 13:22:10 -0000
***************
*** 2987,2992 ****
--- 2987,3006 ----
  DESCR("Statistics: Tuples updated in database");
  DATA(insert OID = 2762 (  pg_stat_get_db_tuples_deleted PGNSP PGUID 12 1 0 f 
f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_db_tuples_deleted - _null_ 
));
  DESCR("Statistics: Tuples deleted in database");
+ DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 
12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ 
pg_stat_get_bgwriter_timed_checkpoints - _null_ ));
+ DESCR("Statistics: Number of timed checkpoints started by the bgwriter");
+ DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP 
PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ 
pg_stat_get_bgwriter_requested_checkpoints - _null_ ));
+ DESCR("Statistics: Number of backend requested checkpoints started by the 
bgwriter");
+ DATA(insert OID = 2771 ( pg_stat_get_bgwriter_buf_written_checkpoints PGNSP 
PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ 
pg_stat_get_bgwriter_buf_written_checkpoints - _null_ ));
+ DESCR("Statistics: Number of buffers written by the bgwriter during 
checkpoints");
+ DATA(insert OID = 2772 ( pg_stat_get_bgwriter_buf_written_lru PGNSP PGUID 12 
1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_buf_written_lru 
- _null_ ));
+ DESCR("Statistics: Number of buffers written by the bgwriter during LRU 
scans");
+ DATA(insert OID = 2773 ( pg_stat_get_bgwriter_buf_written_all PGNSP PGUID 12 
1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_buf_written_all 
- _null_ ));
+ DESCR("Statistics: Number of buffers written by the bgwriter during 
all-buffer scans");
+ DATA(insert OID = 2774 ( pg_stat_get_bgwriter_maxwritten_lru PGNSP PGUID 12 1 
0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_maxwritten_lru - 
_null_ ));
+ DESCR("Statistics: Number of times the bgwriter stopped processing when it 
had written too many buffers during LRU scans");
+ DATA(insert OID = 2775 ( pg_stat_get_bgwriter_maxwritten_all PGNSP PGUID 12 1 
0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_maxwritten_all - 
_null_ ));
+ DESCR("Statistics: Number of times the bgwriter stopped processing when it 
had written too many buffers during all-buffer scans");
  DATA(insert OID = 2230 (  pg_stat_clear_snapshot              PGNSP PGUID 12 
1 0 f f f f v 0 2278  "" _null_ _null_ _null_    pg_stat_clear_snapshot - 
_null_ ));
  DESCR("Statistics: Discard current transaction's statistics snapshot");
  DATA(insert OID = 2274 (  pg_stat_reset                                       
PGNSP PGUID 12 1 0 f f f f v 0 2278  "" _null_ _null_ _null_    pg_stat_reset - 
_null_ ));
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.126
diff -c -r1.126 rules.out
*** src/test/regress/expected/rules.out 16 Mar 2007 17:57:36 -0000      1.126
--- src/test/regress/expected/rules.out 29 Mar 2007 13:22:12 -0000
***************
*** 1292,1297 ****
--- 1292,1298 ----
   pg_stat_activity         | SELECT d.oid AS datid, d.datname, 
pg_stat_get_backend_pid(s.backendid) AS procpid, 
pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, 
pg_stat_get_backend_activity(s.backendid) AS current_query, 
pg_stat_get_backend_waiting(s.backendid) AS waiting, 
pg_stat_get_backend_txn_start(s.backendid) AS txn_start, 
pg_stat_get_backend_activity_start(s.backendid) AS query_start, 
pg_stat_get_backend_start(s.backendid) AS backend_start, 
pg_stat_get_backend_client_addr(s.backendid) AS client_addr, 
pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, 
(SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE 
((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND 
(pg_stat_get_backend_userid(s.backendid) = u.oid));
   pg_stat_all_indexes      | SELECT c.oid AS relid, i.oid AS indexrelid, 
n.nspname AS schemaname, c.relname, i.relname AS indexrelname, 
pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS 
idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM 
(((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON 
((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = 
c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
   pg_stat_all_tables       | SELECT c.oid AS relid, n.nspname AS schemaname, 
c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, 
pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, 
(sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, 
((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + 
pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, 
pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, 
pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, 
pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, 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_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, 
pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c 
LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON 
((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 
't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
+  pg_stat_bgwriter         | SELECT pg_stat_get_bgwriter_timed_checkpoints() 
AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS 
checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS 
buffers_checkpoint, pg_stat_get_bgwriter_buf_written_lru() AS buffers_lru, 
pg_stat_get_bgwriter_buf_written_all() AS buffers_all, 
pg_stat_get_bgwriter_maxwritten_lru() AS maxwritten_lru, 
pg_stat_get_bgwriter_maxwritten_all() AS maxwritten_all;
   pg_stat_database         | SELECT d.oid AS datid, d.datname, 
pg_stat_get_db_numbackends(d.oid) AS numbackends, 
pg_stat_get_db_xact_commit(d.oid) AS xact_commit, 
pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, 
(pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS 
blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, 
pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, 
pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, 
pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, 
pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, 
pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted FROM pg_database d;
   pg_stat_sys_indexes      | SELECT pg_stat_all_indexes.relid, 
pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, 
pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, 
pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, 
pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE 
(pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::"name", 
'pg_toast'::"name", 'information_schema'::"name"]));
   pg_stat_sys_tables       | SELECT pg_stat_all_tables.relid, 
pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, 
pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, 
pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, 
pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, 
pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, 
pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, 
pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, 
pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE 
(pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::"name", 
'pg_toast'::"name", 'information_schema'::"name"]));
***************
*** 1326,1332 ****
   shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, 
shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM 
shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor 
= shoelace.sl_color))));
   street                   | SELECT r."name", r.thepath, c.cname FROM ONLY 
road r, real_city c WHERE (c.outline ## r.thepath);
   toyemp                   | SELECT emp."name", emp.age, emp."location", (12 * 
emp.salary) AS annualsal FROM emp;
! (48 rows)
  
  SELECT tablename, rulename, definition FROM pg_rules 
        ORDER BY tablename, rulename;
--- 1327,1333 ----
   shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, 
shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM 
shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor 
= shoelace.sl_color))));
   street                   | SELECT r."name", r.thepath, c.cname FROM ONLY 
road r, real_city c WHERE (c.outline ## r.thepath);
   toyemp                   | SELECT emp."name", emp.age, emp."location", (12 * 
emp.salary) AS annualsal FROM emp;
! (49 rows)
  
  SELECT tablename, rulename, definition FROM pg_rules 
        ORDER BY tablename, rulename;
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to