Data bloat, data skew, and index statistics

Procedure

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

   • Data bloat

     Note The Data Bloat tab displays only tables that are larger than 1 GB.

     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 a heap table or an append-optimized (AO) table.
     Expected Table Size	The estimated size of the table in bytes if no data bloat existed. The expected table size is the size of a table with no bloat, as calculated by AnalyticDB for PostgreSQL.
     Actual Table Size	The actual size of the table, in bytes.
     Table Bloat Size	The amount of bloated space in the table that can be reclaimed, in bytes.
     Bloat Coefficient	Indicates the degree of data bloat. Calculated as: Number of dead rows / Number of active rows. Note Because AnalyticDB for PostgreSQL uses the multiversion concurrency control (MVCC) mechanism, UPDATE and DELETE operations create dead rows.
     Last Diagnosed At	The time when the last diagnostic check was initiated. Note The intelligent diagnostics feature automatically diagnoses all tables in the database at the top of every hour.

   • Data skew

     Note The Data Skew tab displays only tables that are larger than 1 GB.

     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 degree of data skew, expressed as a percentage from 0% to 100%. A higher value indicates more severe data skew, while a lower value improves query performance. A value of 0 indicates no data skew. Calculated as: 1 - (Average table size across all nodes / Maximum table size on any single node)
     Distribution Key	The distribution key of the table.
     Last Diagnosed At	The time when the last diagnostic check was initiated. Note The intelligent diagnostics feature automatically diagnoses all tables in the database at the top of every hour.

   • Index statistics

     Note The Index Statistics tab displays only indexes that are larger than 100 MB.

     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 a partitioned table.
     Table Name	The name of the table.
     Index Size	The size of the index, in 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 check was initiated. Note The intelligent diagnostics feature automatically diagnoses all tables in the database at the top of every hour.
     Index Definition	The Data Definition Language (DDL) statement for the index.