AnalyticDB for PostgreSQL provides a Key Metrics page for the diagnostics and optimization feature. You can view data bloat, data skew, and index statistics.

Prerequisites

The instance is in elastic storage mode and runs the minor engine version of V6.3.10.1 or later. For information about how to view and update the minor engine version of an instance, see View the minor engine version and Update the minor engine version.

Procedure

  1. Log on to the AnalyticDB for PostgreSQL console.
  2. In the upper-left corner of the console, select the region where the instance resides.
  3. Find the instance that you want to manage and click its ID.
  4. In the left-side navigation pane, choose Diagnostics and Optimization > Key Metrics.
    The Key Metrics page consists of three tabs: Data Bloat, Data Skew, and Index Statistics.

    • Data Bloat

      Note The Data Bloat tab lists tables that are larger than 1 GB in size.
      Parameter Description
      Database Name The name of the database.
      Schema Name The name of the schema.
      Partitioned Table Name The name of the partitioned table.
      Table Type The storage type of the table, such as heap table or append-optimized (AO) table.
      Expected Table Size The expected size of the table. Unit: bytes.

      The expected table size indicates the size of the table that has no data bloat.

      Actual Table Size The actual size of the table. Unit: bytes.
      Table Bloat Size The bloat size of the table. It indicates the amount of space that can be released. Unit: bytes.
      Bloat Coefficient The coefficient of data bloat. It is calculated by using the following formula:

      Bloat coefficient = Number of dead rows/Number of active rows.

      Note UPDATE and DELETE operations may cause dead rows because AnalyticDB for PostgreSQL uses the multiversion concurrency control (MVCC) policy.
      Last Diagnosed At The time when the last diagnostic task was initiated.
      Note The intelligent diagnostics feature automatically diagnoses all database tables at the beginning of each hour in the background.

    • Data Skew

      Note The Data Skew tab lists tables that are larger than 1 GB in size.
      Parameter Description
      Database Name The name of the database.
      Schema Name The name of the schema.
      Partitioned Table Name The name of the partitioned table.
      Owner The owner of the table.
      Rows The total number of rows in the table.
      Skew Ratio The skew ratio of the table, which is written as a percentage. A greater value indicates more severe data skew. A less value indicates less impact on query performance. A value of 0 indicates that no data skew exists. The skew ratio can be calculated by using the following formula:

      Skew ratio = 1 - (Average table size among all nodes/Maximum table size among all nodes).

      Distribution Key The distribution key of the table.
      Last Diagnosed At The time when the last diagnostic task was initiated.
      Note The intelligent diagnostics feature automatically diagnoses all database tables at the beginning of each hour in the background.

    • Index Statistics

      Note The Index Statistics tab displays indexes that are larger than 100 MB in size.
      Parameter Description
      Database Name The name of the database.
      Schema Name The name of the schema.
      Partitioned Table Name The name of the partitioned table.
      Whether Partitioned Table Indicates whether the table is partitioned.
      Table Name The name of the table.
      Index Size The size of the index. Unit: bytes.
      Index Scans The total number of index scans.
      Index-scanned Rows The total number of index-scanned rows.
      Index Name The name of the index.
      Last Diagnosed At The time when the last diagnostic task was initiated.
      Note The intelligent diagnostics feature automatically diagnoses all database tables at the beginning of each hour in the background.
      Index Definition The DDL statement used to manage the index.