Dotazy na tabulku PGSQLAutovacuumStats

Informace o používání těchto dotazů na webu Azure Portal najdete v kurzu služby Log Analytics. Informace o rozhraní REST API najdete v tématu Dotaz.

Bloat poměr

Souhrn aktuálního poměru bloat na databázi

// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,LiveRowsCount,DeadRowsCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalLiveRows=sum(LiveRowsCount), TotalDeadRows=sum(DeadRowsCount) by DatabaseName
| extend BloatRatio=toreal(TotalDeadRows)/toreal(TotalLiveRows)*100
| extend BloatRatio = iff( isnan(BloatRatio), 0.0, BloatRatio)
| project DatabaseName,TotalLiveRows,TotalDeadRows, BloatRatio
| order by BloatRatio desc 

Statistika vakua

Souhrn celkového počtu tabulek, které byly vysáty na databázi

// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,TablesCount,TablesVacuumedCount,TablesAutovacuumedCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalTables=sum(TablesCount), TablesVaccumed=sum(TablesVacuumedCount), TablesAutoVaccumed=sum(TablesAutovacuumedCount) by DatabaseName
| order by TotalTables desc 

Analýza statistik

Souhrn celkových tabulek, které byly analyzovány na databázi

// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,TablesCount,TablesAnalyzedCount,TablesAutoanalyzedCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalTables=sum(TablesCount), TablesAnalyzed=sum(TablesAnalyzedCount), TablesAutoAnalyzed=sum(TablesAutoanalyzedCount) by DatabaseName
| order by TotalTables desc