PGXC_STAT_TABLE_DIRTY
PGXC_STAT_TABLE_DIRTY displays statistics about all the tables on all the CNs and DNs in the current cluster, and the dirty page rate of tables on a single CN or DN. This view is supported only by clusters of version 8.1.3 or later.
![](https://support.huaweicloud.com/eu/devg-dws/public_sys-resources/note_3.0-en-us.png)
The statistics of this view depend on the ANALYZE operation. To obtain the most accurate information, perform the ANALYZE operation on the table first.
Name |
Type |
Description |
---|---|---|
nodename |
text |
Node name |
schema |
name |
Schema name of the table |
tablename |
name |
Table name |
partname |
name |
Partition name of the partitioned table |
last_vacuum |
timestampwith time zone |
Time of the last manual VACUUM |
last_autovacuum |
timestampwith time zone |
Time of the last AUTOVACUUM |
last_analyze |
timestampwith time zone |
Time of the last manual ANALYZE |
last_antoanalyze |
timestampwith time zone |
Time of the last AUTOANALYZE |
vacuum_count |
bigint |
Number of times VACUUM operations |
autovacuum_count |
bigint |
Number of AUTOVACUUM operations |
analyze_count |
bigint |
Number of ANALYZE operations |
autoanalyze_count |
bigint |
Number of AUTOANALYZE_COUNT operations |
n_tup_ins |
bigint |
Number of rows inserted |
n_tup_upd |
bigint |
Number of rows updated |
n_tup_del |
bigint |
Number of rows deleted |
n_tup_hot_upd |
bigint |
Number of rows with HOT updates |
n_tup_change |
bigint |
Number of changed rows after ANALYZE |
n_live_tup |
bigint |
Estimated number of live rows |
n_dead_tup |
bigint |
Estimated number of dead rows |
dirty_rate |
bigint |
Dirty page rate of a single CN or DN |
last_data_changed |
timestampwith time zone |
Time when a table was last modified |
Suggestion
- Before running VACUUM FULL on a system catalog with a high dirty page rate, ensure that no user is performing operations on it.
- You are advised to run VACUUM FULL to tables (excluding system catalogs) whose dirty page rate exceeds 80% or run it based on service scenarios.
Scenarios
- Query the overall dirty page rate of all the user tables in a database.
- Query the overall dirty page rate of all the tables (user tables and system catalogs) in a database.
- Query all system catalogs in a database.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.