Home Explore Blog CI



postgresql

10th chunk of `doc/src/sgml/monitoring.sgml`
07b4421aa5265472f1968f16130a89bdc7a1c1e99e66116e0000000100000fa5
   <!-- all "statio" for schema objects, by "importance" -->

     <row>
      <entry><structname>pg_statio_all_tables</structname><indexterm><primary>pg_statio_all_tables</primary></indexterm></entry>
      <entry>
       One row for each table in the current database, showing statistics
       about I/O on that specific table.
       See <link linkend="monitoring-pg-statio-all-tables-view">
       <structname>pg_statio_all_tables</structname></link> for details.
      </entry>
     </row>

     <row>
      <entry><structname>pg_statio_sys_tables</structname><indexterm><primary>pg_statio_sys_tables</primary></indexterm></entry>
      <entry>Same as <structname>pg_statio_all_tables</structname>, except that only
      system tables are shown.</entry>
     </row>

     <row>
      <entry><structname>pg_statio_user_tables</structname><indexterm><primary>pg_statio_user_tables</primary></indexterm></entry>
      <entry>Same as <structname>pg_statio_all_tables</structname>, except that only
      user tables are shown.</entry>
     </row>

     <row>
      <entry><structname>pg_statio_all_indexes</structname><indexterm><primary>pg_statio_all_indexes</primary></indexterm></entry>
      <entry>
       One row for each index in the current database,
       showing statistics about I/O on that specific index.
       See <link linkend="monitoring-pg-statio-all-indexes-view">
       <structname>pg_statio_all_indexes</structname></link> for details.
      </entry>
     </row>

     <row>
      <entry><structname>pg_statio_sys_indexes</structname><indexterm><primary>pg_statio_sys_indexes</primary></indexterm></entry>
      <entry>Same as <structname>pg_statio_all_indexes</structname>, except that only
      indexes on system tables are shown.</entry>
     </row>

     <row>
      <entry><structname>pg_statio_user_indexes</structname><indexterm><primary>pg_statio_user_indexes</primary></indexterm></entry>
      <entry>Same as <structname>pg_statio_all_indexes</structname>, except that only
      indexes on user tables are shown.</entry>
     </row>

     <row>
      <entry><structname>pg_statio_all_sequences</structname><indexterm><primary>pg_statio_all_sequences</primary></indexterm></entry>
     <entry>
       One row for each sequence in the current database,
       showing statistics about I/O on that specific sequence.
       See <link linkend="monitoring-pg-statio-all-sequences-view">
       <structname>pg_statio_all_sequences</structname></link> for details.
     </entry>
     </row>

     <row>
      <entry><structname>pg_statio_sys_sequences</structname><indexterm><primary>pg_statio_sys_sequences</primary></indexterm></entry>
      <entry>Same as <structname>pg_statio_all_sequences</structname>, 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</structname><indexterm><primary>pg_statio_user_sequences</primary></indexterm></entry>
      <entry>Same as <structname>pg_statio_all_sequences</structname>, except that only
      user sequences are shown.</entry>
     </row>

    </tbody>
   </tgroup>
  </table>

  <para>
   The per-index statistics are particularly useful to determine which
   indexes are being used and how effective they are.
  </para>

  <para>
   The <structname>pg_stat_io</structname> and
   <structname>pg_statio_</structname> set of views are useful for determining
   the effectiveness of the buffer cache. They can be used to calculate a cache
   hit ratio. Note that while <productname>PostgreSQL</productname>'s I/O
   statistics capture most instances in which the kernel was invoked in order
   to perform I/O, they do not differentiate between data which had to be
   fetched from disk and that which already resided in the kernel page cache.
   Users are advised to use the <productname>PostgreSQL</productname>
   statistics views in combination with operating

Title: I/O Statistics Views: Tables, Indexes, and Sequences
Summary
This section describes the I/O statistics views in PostgreSQL, including pg_statio_all_tables, pg_statio_sys_tables, pg_statio_user_tables, pg_statio_all_indexes, pg_statio_sys_indexes, pg_statio_user_indexes, pg_statio_all_sequences, pg_statio_sys_sequences, and pg_statio_user_sequences. These views provide statistics about I/O activity on tables, indexes, and sequences, broken down by all objects, system objects, and user objects. The per-index statistics are particularly useful for determining index usage and effectiveness. These views help assess the buffer cache's effectiveness by calculating the cache hit ratio.