Top
Enterprise Postgres 14 SP1 Application Development Guide
FUJITSU Software

9.1.2 Locked Statistics

This section explains the basic feature content for locked statistics (pg_dbms_stats).

Refer to the open-source software webpage for information on pg_dbms_stats.

Description

Locks the statistics.

By using this feature to lock the statistics for performance obtained in job load testing in an environment that simulates a production environment, performance degradation caused by changes to the query plan after go-live can be suppressed.

Additionally, by using the export and import features, statistics that were checked in the test environment can also be reproduced in the production environment.

List of Features

The main features that can be specified using this feature are as follows.

[Features]

Feature

Details

Description

Lock/unlock of the statistics

Lock

Locks the statistics so that the currently selected query plan remains selected.

Unlock

Unlocks the statistics.

Backup/restore of the statistics

Backup

Backs up the current statistics.

Restore

Restores the statistics to the point when they were backed up, and then locks them.

Purge

Deletes backups that are no longer necessary.

Backup/restore using external files

Export

Outputs the current statistics to an external file (binary format).

Import

Reads the statistics from an external file created by the export feature, and then locks them.


[Target object]

Target resource

Range of feature

Database

In the database

Schema

In the schema

Table

In the table

Column

ID column

Usage method

The use of this feature is explained below.


Method used to specify this feature

Specify this feature as an SQL function.

The methods used to specify the main features are shown in the table below.

Feature

Object

Function specified

Lock

Database

dbms_stats.lock_database_stats()

Schema

dbms_stats.lock_schema_stats('schemaName')

Table

dbms_stats.lock_table_stats('schemaName.tableName')

Unlock

Database

dbms_stats.unlock_database_stats()

Schema

dbms_stats.unlock_schema_stats('schemaName')

Table

dbms_stats.unlock_table_stats('schemaName.tableName')

Import

Database

dbms_stats.import_database_stats('fullPathOfExportedFile')

Backup

Database

dbms_stats.backup_database_stats('commentUsedForIdentification')

Restore

Database

[Format 1]
dbms_stats.restore_database_stats('timestamp')

[Timestamp]
Specify in the same format as the time column of the backup_history table. Backups earlier than the specified time will be restored.

[Format 2]
dbms_stats.restore_stats(backupId)

[Backup ID]
Specify a value in the id column of the backup_history table. The specified backup will be restored.

Purge

Backup

dbms_stats.purge_stats(backupId,flagUsedForDeletion)

[Backup ID]
Specify a value in the id column of the backup_history table.

[Flag used for deletion]
true: The target backup is forcibly deleted.
false: The target backup is deleted only when there are also backups for the entire database.

Remark 1: The export feature is executed using the COPY statement, not the SQL function.


Example

Example 1: Locking the statistics of the entire database

userdb=# SELECT dbms_stats.lock_database_stats();
  lock_database_stats  
-----------------------
  tbl1 
  tbl1_pkey

Note that the locked information can be referenced as follows:

userdb=# select relname  from dbms_stats.relation_stats_locked;
  relname
-----------------------
  tbl1 
  tbl1_pkey

Example 2: Unlocking the statistics of the entire database

userdb=# SELECT dbms_stats.unlock_database_stats();
  unlock_database_stats  
-----------------------
  tbl1 
  tbl1_pkey

Example 3: Backing up the statistics of the entire database

userdb=# SELECT dbms_stats.backup_database_stats('backup1');
  backup_database_stats 
-----------------------
                     1

Note that the backed up statistics can be referenced as follows:

userdb=# select id,comment,time,unit from dbms_stats.backup_history;
  id | comment  |             time              | unit
 ----+----------+-------------------------------+------
   1 | backup1  | 2014-03-04 11:08:40.315948+09 | d

The ID:1 backup "backup1" is obtained for each database at "2014-03-04 11:08:40.315948+09".
[Meaning of unit] d: database s: schema t: table c: column


Example 4: Exporting the statistics of the entire database

$ psql -d userdb -f export.sql
BEGIN
COMMIT

export.sql is the file in which the COPY statement is defined.
Refer to "export_effective_stats-<x>.sql_sample" for information on the content of the COPY statement. "<x>" indicates the product version.

"export_effective_stats-<x>.sql_sample" is stored as follows:
fujitsuEnterprisePostgresInstallDir/share/doc/extension


Example 5: Importing the statistics of the entire database

$ psql -d userdb -c  "SELECT dbms_stats.import_database_stats ('$PWD/export_stats.dmp')" 
 import_database_stats 
-----------------------
 
(1 row)

Usage notes