Revise pgstats stuff to fix the problems with not counting accesses

generated by bitmap index scans.  Along the way, simplify and speed up
the code for counting sequential and index scans; it was both confusing
and inefficient to be taking care of that in the per-tuple loops, IMHO.
initdb forced because of internal changes in pg_stat view definitions.

1 files changed, 107 insertions, 72 deletions

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a5f9b41d926..9ddfc28ff49 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.28 2005/05/09 11:31:32 neilc Exp $
+$PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.29 2005/10/06 02:29:06 tgl Exp $
 -->
 
 <chapter id="monitoring">
@@ -185,11 +185,12 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
    Each individual server process transmits new block and row access counts to
    the collector just before going idle; so a query or transaction still in
    progress does not affect the displayed totals.  Also, the collector itself
-   emits a new report at most once per <varname>pgstat_stat_interval</varname>
-   milliseconds (500 by default).  So the displayed information lags behind
-   actual activity.  Current-query information is reported to the collector
-   immediately, but is still subject to the
-   <varname>pgstat_stat_interval</varname> delay before it becomes visible.
+   emits a new report at most once per <varname>PGSTAT_STAT_INTERVAL</varname>
+   milliseconds (500 unless altered while building the server).  So the
+   displayed information lags behind actual activity.  Current-query
+   information is reported to the collector immediately, but is still subject
+   to the <varname>PGSTAT_STAT_INTERVAL</varname> delay before it becomes
+   visible.
   </para>
 
   <para>
@@ -220,10 +221,10 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
     <tbody>
      <row>
       <entry><structname>pg_stat_activity</></entry>
-      <entry>One row per server process, showing process
-      <acronym>ID</>, database, user, current query, the time at which
-      the current query began execution, the time at which the backend
-      was started and the client address and port number.  The columns
+      <entry>One row per server process, showing database OID, database name,
+      process <acronym>ID</>, user OID, user name, current query, time at
+      which the current query began execution, time at which the process
+      was started, and client's address and port number.  The columns
       that report data on the current query are only available if the
       parameter <varname>stats_command_string</varname> has been
       turned on.  Furthermore, these columns read as null unless the
@@ -235,114 +236,122 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
 
      <row>
       <entry><structname>pg_stat_database</></entry>
-      <entry>One row per database, showing the number of active backend server processes,
-      total transactions committed and total rolled back in that database,
-      total disk blocks read, and total number of buffer hits (i.e., block
+      <entry>One row per database, showing database OID, database name,
+      number of active server processes connected to that database,
+      number of transactions committed and rolled back in that database,
+      total disk blocks read, and total buffer hits (i.e., block
       read requests avoided by finding the block already in buffer cache).
      </entry>
      </row>
 
      <row>
       <entry><structname>pg_stat_all_tables</></entry>
-      <entry>For each table in the current database, total numbers of
-      sequential and index scans, total numbers of rows returned by
-      each type of scan, and totals of row insertions, updates,
-      and deletions.</entry>
+      <entry>For each table in the current database (including TOAST tables),
+      the table OID, schema and table name, number of sequential
+      scans initiated, number of live rows fetched by sequential
+      scans, number of index scans initiated (over all indexes
+      belonging to the table), number of live rows fetched by index
+      scans,
+      and numbers of row insertions, updates, and deletions.</entry>
      </row>
 
      <row>
       <entry><structname>pg_stat_sys_tables</></entry>
-      <entry>Same as <structname>pg_stat_all_tables</>, except that only system tables
-      are shown.</entry>
+      <entry>Same as <structname>pg_stat_all_tables</>, except that only
+      system tables are shown.</entry>
      </row>
 
      <row>
       <entry><structname>pg_stat_user_tables</></entry>
-      <entry>Same as <structname>pg_stat_all_tables</>, except that only user tables
-      are shown.</entry>
+      <entry>Same as <structname>pg_stat_all_tables</>, except that only user
+      tables are shown.</entry>
      </row>
 
      <row>
       <entry><structname>pg_stat_all_indexes</></entry>
-      <entry>For each index in the current database, the total number
-      of index scans that have used that index, the number of index rows
-      read, and the number of successfully fetched heap rows. (This may
-      be less when there are index entries pointing to expired heap rows.)
+      <entry>For each index in the current database,
+      the table and index OID, schema, table and index name,
+      number of index scans initiated on that index, number of
+      index entries returned by index scans, and number of live table rows
+      fetched by simple index scans using that index.
       </entry>
      </row>
 
      <row>
       <entry><structname>pg_stat_sys_indexes</></entry>
-      <entry>Same as <structname>pg_stat_all_indexes</>, except that only indexes on
-      system tables are shown.</entry>
+      <entry>Same as <structname>pg_stat_all_indexes</>, except that only
+      indexes on system tables are shown.</entry>
      </row>
 
      <row>
       <entry><structname>pg_stat_user_indexes</></entry>
-      <entry>Same as <structname>pg_stat_all_indexes</>, except that only indexes on
-      user tables are shown.</entry>
+      <entry>Same as <structname>pg_stat_all_indexes</>, except that only
+      indexes on user tables are shown.</entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_all_tables</></entry>
-      <entry>For each table in the current database, the total number of disk
-      blocks read from that table, the number of buffer hits, the numbers of
-      disk blocks read and buffer hits in all the indexes of that table,
-      the numbers of disk blocks read and buffer hits from the table's
-      auxiliary TOAST table (if any), and the numbers of disk blocks read
+      <entry>For each table in the current database (including TOAST tables),
+      the table OID, schema and table name, number of disk
+      blocks read from that table, number of buffer hits, numbers of
+      disk blocks read and buffer hits in all indexes of that table,
+      numbers of disk blocks read and buffer hits from that table's
+      auxiliary TOAST table (if any), and numbers of disk blocks read
       and buffer hits for the TOAST table's index.
       </entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_sys_tables</></entry>
-      <entry>Same as <structname>pg_statio_all_tables</>, except that only system tables
-      are shown.</entry>
+      <entry>Same as <structname>pg_statio_all_tables</>, except that only
+      system tables are shown.</entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_user_tables</></entry>
-      <entry>Same as <structname>pg_statio_all_tables</>, except that only user tables
-      are shown.</entry>
+      <entry>Same as <structname>pg_statio_all_tables</>, except that only
+      user tables are shown.</entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_all_indexes</></entry>
-      <entry>For each index in the current database, the numbers of
-      disk blocks read and buffer hits in that index.
+      <entry>For each index in the current database,
+      the table and index OID, schema, table and index name,
+      numbers of disk blocks read and buffer hits in that index.
       </entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_sys_indexes</></entry>
-      <entry>Same as <structname>pg_statio_all_indexes</>, except that only indexes on
-      system tables are shown.</entry>
+      <entry>Same as <structname>pg_statio_all_indexes</>, except that only
+      indexes on system tables are shown.</entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_user_indexes</></entry>
-      <entry>Same as <structname>pg_statio_all_indexes</>, except that only indexes on
-      user tables are shown.</entry>
+      <entry>Same as <structname>pg_statio_all_indexes</>, except that only
+      indexes on user tables are shown.</entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_all_sequences</></entry>
-      <entry>For each sequence object in the current database, the numbers
-      of disk blocks read and buffer hits in that sequence.
+      <entry>For each sequence object in the current database,
+      the sequence OID, schema and sequence name,
+      numbers of disk blocks read and buffer hits in that sequence.
       </entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_sys_sequences</></entry>
-      <entry>Same as <structname>pg_statio_all_sequences</>, except that only system
-      sequences are shown.  (Presently, no system sequences are defined,
+      <entry>Same as <structname>pg_statio_all_sequences</>, except that only
+      system sequences are shown.  (Presently, no system sequences are defined,
       so this view is always empty.)</entry>
      </row>
 
      <row>
       <entry><structname>pg_statio_user_sequences</></entry>
-      <entry>Same as <structname>pg_statio_all_sequences</>, except that only user
-      sequences are shown.</entry>
+      <entry>Same as <structname>pg_statio_all_sequences</>, except that only
+      user sequences are shown.</entry>
      </row>
     </tbody>
    </tgroup>
@@ -354,6 +363,32 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
   </para>
 
   <para>
+   Beginning in <productname>PostgreSQL</productname> 8.1, indexes can be
+   used either directly or via <quote>bitmap scans</>.  In a bitmap scan
+   the output of several indexes can be combined via AND or OR rules;
+   so it is difficult to associate individual heap row fetches 
+   with specific indexes when a bitmap scan is used.  Therefore, a bitmap
+   scan increments the
+   <structname>pg_stat_all_indexes</>.<structfield>idx_tup_read</>
+   count(s) for the index(es) it uses, and it increments the
+   <structname>pg_stat_all_tables</>.<structfield>idx_tup_fetch</>
+   count for the table, but it does not affect
+   <structname>pg_stat_all_indexes</>.<structfield>idx_tup_fetch</>.
+  </para>
+
+  <note>
+   <para>
+    Before <productname>PostgreSQL</productname> 8.1, the
+    <structfield>idx_tup_read</> and <structfield>idx_tup_fetch</> counts
+    were essentially always equal.  Now they can be different even without
+    considering bitmap scans, because <structfield>idx_tup_read</> counts
+    index entries retrieved from the index while <structfield>idx_tup_fetch</>
+    counts live rows fetched from the table; the latter will be less if any
+    dead or not-yet-committed rows are fetched using the index.
+   </para>
+  </note>
+
+  <para>
    The <structname>pg_statio_</> views are primarily useful to
    determine the effectiveness of the buffer cache.  When the number
    of actual disk reads is much smaller than the number of buffer
@@ -379,9 +414,9 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
    database to report on.  The per-table and per-index functions take
    a table or index OID.  (Note that only tables and indexes in the
    current database can be seen with these functions.)  The
-   per-backend process access functions take a backend process ID
+   per-server-process access functions take a server process
    number, which ranges from one to the number of currently active
-   backend processes.
+   server processes.
   </para>
 
   <table id="monitoring-stats-funcs-table">
@@ -401,7 +436,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_stat_get_db_numbackends</function>(<type>oid</type>)</literal></entry>
       <entry><type>integer</type></entry>
       <entry>
-       Number of active backend processes for database
+       Number of active server processes for database
       </entry>
      </row>
 
@@ -451,7 +486,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><type>bigint</type></entry>
       <entry>
        Number of rows read by sequential scans when argument is a table,
-       or number of index rows read when argument is an index
+       or number of index entries returned when argument is an index
       </entry>
      </row>
 
@@ -459,8 +494,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_stat_get_tuples_fetched</function>(<type>oid</type>)</literal></entry>
       <entry><type>bigint</type></entry>
       <entry>
-       Number of valid (unexpired) table rows fetched by sequential scans
-       when argument is a table, or fetched by index scans using this index
+       Number of table rows fetched by bitmap scans when argument is a table,
+       or table rows fetched by simple index scans using the index
        when argument is an index
       </entry>
      </row>
@@ -507,10 +542,10 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
 
      <row>
       <entry><literal><function>pg_stat_get_backend_idset</function>()</literal></entry>
-      <entry><type>set of integer</type></entry>
+      <entry><type>setof integer</type></entry>
       <entry>
-       Set of currently active backend process IDs (from 1 to the
-       number of active backend processes).  See usage example in the text
+       Set of currently active server process numbers (from 1 to the
+       number of active server processes).  See usage example in the text
       </entry>
      </row>
 
@@ -518,7 +553,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_backend_pid</function>()</literal></entry>
       <entry><type>integer</type></entry>
       <entry>
-       Process ID of the backend process attached to the current session
+       Process ID of the server process attached to the current session
       </entry>
      </row>
 
@@ -526,7 +561,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_stat_get_backend_pid</function>(<type>integer</type>)</literal></entry>
       <entry><type>integer</type></entry>
       <entry>
-       Process ID of the given backend process
+       Process ID of the given server process
       </entry>
      </row>
 
@@ -534,7 +569,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_stat_get_backend_dbid</function>(<type>integer</type>)</literal></entry>
       <entry><type>oid</type></entry>
       <entry>
-       Database ID of the given backend process
+       Database ID of the given server process
       </entry>
      </row>
 
@@ -542,7 +577,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_stat_get_backend_userid</function>(<type>integer</type>)</literal></entry>
       <entry><type>oid</type></entry>
       <entry>
-       User ID of the given backend process
+       User ID of the given server process
       </entry>
      </row>
 
@@ -550,7 +585,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_stat_get_backend_activity</function>(<type>integer</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
-       Active command of the given backend process (null if the
+       Active command of the given server process (null if the
        current user is not a superuser nor the same user as that of
        the session being queried, or
        <varname>stats_command_string</varname> is not on)
@@ -561,7 +596,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_stat_get_backend_activity_start</function>(<type>integer</type>)</literal></entry>
       <entry><type>timestamp with time zone</type></entry>
       <entry>
-       The time at which the given backend process' currently
+       The time at which the given server process' currently
        executing query was started (null if the
        current user is not a superuser nor the same user as that of
        the session being queried, or
@@ -573,7 +608,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><literal><function>pg_stat_get_backend_start</function>(<type>integer</type>)</literal></entry>
       <entry><type>timestamp with time zone</type></entry>
       <entry>
-       The time at which the given backend process was started, or
+       The time at which the given server process was started, or
        null if the current user is not a superuser nor the same user
        as that of the session being queried
       </entry>
@@ -584,7 +619,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><type>inet</type></entry>
       <entry>
        The IP address of the client connected to the given
-       backend. Null if the connection is over a Unix domain
+       server process. Null if the connection is over a Unix domain
        socket. Also null if the current user is not a superuser nor
        the same user as that of the session being queried
       </entry>
@@ -595,7 +630,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><type>integer</type></entry>
       <entry>
        The IP port number of the client connected to the given
-       backend.  -1 if the connection is over a Unix domain
+       server process.  -1 if the connection is over a Unix domain
        socket. Null if the current user is not a superuser nor the
        same user as that of the session being queried
       </entry>
@@ -614,8 +649,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
 
    <note>
     <para>
-     <function>pg_stat_get_db_blocks_fetched</function> minus
-     <function>pg_stat_get_db_blocks_hit</function> gives the number of kernel
+     <function>blocks_fetched</function> minus
+     <function>blocks_hit</function> gives the number of kernel
      <function>read()</> calls issued for the table, index, or
      database; but the actual number of physical reads is usually
      lower due to kernel-level buffering.
@@ -624,8 +659,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
 
   <para>
    The function <function>pg_stat_get_backend_idset</function> provides
-   a convenient way to generate one row for each active backend process.  For
-   example, to show the <acronym>PID</>s and current queries of all backend processes:
+   a convenient way to generate one row for each active server process.  For
+   example, to show the <acronym>PID</>s and current queries of all server processes:
 
 <programlisting>
 SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,