1. Function
  2. Table
  3. View


pg_dbms_stats contains the following functions.

Discription of each function.

FeaturesFunctionArgumentObject UnitReturn Value
Backup dbms_stats.backup_database_stats comment Database int8
dbms_stats.backup_schema_stats schemaname, comment Schema
dbms_stats.backup_table_stats relname, comment Table
schemaname, tablename, comment
dbms_stats.backup_column_stats relname, attname, comment Column
schemaname, tablename, attname, comment
Restore dbms_stats.restore_database_stats timestamp Database regclass
dbms_stats.restore_schema_stats schemaname, timestamp Schema
dbms_stats.restore_table_stats relname, timestamp Table
schemaname, tablename, timestamp
dbms_stats.restore_column_stats relname, attname, timestamp Column
schemaname, tablename, attname, timestamp
dbms_stats.restore_stats backup_id Backup
Lock dbms_stats.lock_database_stats (Without) Database regclass
dbms_stats.lock_schema_stats schemaname Schema
dbms_stats.lock_table_stats relname Table
schemaname, tablename
dbms_stats.lock_column_stats relname, attname Column
schemaname, tablename, attname
Unlock dbms_stats.unlock_database_stats (Without) Database regclass
dbms_stats.unlock_schema_stats schemaname Schema
dbms_stats.unlock_table_stats relname Table
schemaname, tablename
dbms_stats.unlock_column_stats relname, attname Column
schemaname, tablename, attname
Import dbms_stats.import_database_stats src Database void
dbms_stats.import_schema_stats schemaname, src Schema
dbms_stats.import_table_stats relname, src Table
schemaname, tablename, src
dbms_stats.import_column_stats relname, attname, src Column
schemaname, tablename, attname, src
Purge dbms_stats.purge_stats backup_id, force Backup dbms_stats.backup_history

The arguments that are used in each function is as follows.

ArgumentData TypeDescription
schemaname text The schema name to be processed.
relname regclass This is the table name to be processed. However, It will be in the form of (schema name).(Table name).
tablename text This is the table name to be processed.
attname text This is the column name to be processed.
comment text comment to identify the backup.
as_of_timestamp timestamptz Is the timestamp when you want to restore to. Restore the latest Backup data before the timestamp. If the Backup does not exist, it does not statistics value.
src text The absolute path of the file to be imported.
backup_id bigint It is a backup ID to be purge and restore. It restore statistical information with matching ID passed in restore function. It Purge statistic with matching ID passed to purge function.
force bool When you purge, it is a variable that determines whether to forcibly remove the backup. If true, delete all Backup target range. If false, print warning message Database Backup data exists outside the target range. Default value is false.

Statistics export feature is also implemented in SQL file.
The meaning of each SQL file as follows. Furthermore, the default output file name is export_stats.dmp.

File NameStatistics TargetRemark
export_effective_stats..sql.sample Current Statistics planner is referring. -
export_plain_stats-.sql.sample Only for original statistics it can be used even pg_dbms_stats not installed


pg_dbms_stats contains the following table.

Table NameColumn NameData TypeDescription
dbms_stats.backup_history id int8 It is a backup ID, assigned at the time of the backup.
time timestamptz The time stamp at the time of backup.
unit char(1) Backup object.
d:Database, s:Schema, t:Table, c:Column
comment text It is a comment that you specified at the time of backup.


pg_dbms_stats includes following view.

View NameDescription
dbms_stats.relation_stats_effective Statistic for each table object currently planner referring. It corresponds to the PostgreSQL's pg_class catalog.
dbms_stats.column_stats_effective Statistic for each column, currently planner referring. It corresponds to the PostgreSQL's pg_statistic catalog.
dbms_stats.status Display the statistic for all column, planner is referring. It corresponds to the PostgreSQL's pg_stat catalog.

Related Item

psql, vacuumdb