12.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.
Lock/unlock of the statistics	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	Exports the statistics (binary format).
Backup/restore using external files	Import	Imports the statistics, 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.

Preparation

The following preparation is required to use this feature.

Run CREATE EXTENSION for the database that will use this feature.
The target database is described as "postgres" here.
Use the psql command to connect to the "postgres" database.
Example
```
Postgres=# CREATE EXTENSION pg_dbms_stats;
CREATE EXTENSION
```
Information
Hereafter, also perform this preparatory task for the "template1" database, so that this feature can be used by default when creating a new database.
Set the postgresql.conf file parameter.
Add "pg_dbms_stats" to the "shared_preload_libraries" parameter.
Restart Symfoware Server.

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_schema_stats('schemaName.tableName')
Unlock	Database	dbms_stats.unlock_database_stats()
	Schema	dbms_stats.unlock_schema_stats('schemaName')
	Table	dbms_stats.unlock_schema_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-9.2.sql_sample" for information on the content of the COPY statement.

"export_effective_stats-9.2.sql_sample" is stored as follows:
symfowareServerInstallDir/share/extension/pg_dbms_stats

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

You must run the ANALYZE command once for the target tables of this feature. If the ANALYZE command is not run, the statistics cannot be locked.
Refer to "SQL Commands" in "Reference" in the PostgreSQL Documentation for information on the ANALYZE command.
To use this feature to delete an object that has locked the statistics, use the unlock feature to delete the object lock information first.
This feature does not specify the statistics value directly. It reproduces the status that has actually occurred. For this reason, if the text format is specified in the COPY statement when the export occurs, restore will not be possible. Always use the binary format when performing the export.