stat_dump

The stat_dump extension provides functions that allow dumping and restoring the contents of the pg_statistic table. The extension allows avoiding executing ANALYZE when restoring data or deploying an instance that duplicates an existing one, thereby reducing deployment time, since ANALYZE takes a long time to analyze large databases.

The dump_statistic function issues INSERT statements that can then be applied to a compatible database. In order to successfully restore statistical data, this extension must be installed on both the source and target servers, as these statements will use functions implemented in the extension.

WARNING!
The definition of the pg_statistic table may change in newer versions, causing older exports to be incompatible.

Installation

CREATE EXTENSION stat_dump;

Functions

Basic Functions

dump_statistic() returns setof text

Dumps the contents of the system catalog pg_statistic, issuing an INSERT for each tuple in pg_statistic except those containing statistics about tables in the schemas information_schema and pg_catalog.

The INSERT statement takes the following form:

WITH upsert as (
  UPDATE pg_catalog.pg_statistic SET [column_name1 = value1, ...]
  WHERE starelid = '{t_relname}'::regclass
    AND to_attname('{t_relname}'::regclass, staattnum) = '{t_attname}'
    AND to_atttype('{t_relname}'::regclass, staattnum) = '{t_atttype}'
    AND stainherit = {t_stainherit}
  RETURNING *),
ins as (
  SELECT [value1, ...]
  WHERE NOT EXISTS (SELECT * FROM upsert)
    AND to_attnum('{t_relname}', '{t_attname}') IS NOT NULL
    AND to_atttype('{t_relname}'::regclass, '{t_attname}') = '{t_atttype}')
INSERT INTO pg_catalog.pg_statistic SELECT * FROM ins;

/*
     where valueN can be represented in one of the following variants:
     - value::type_name
     - array_in(array_text, type_name::regtype::oid, -1)

     and instead of t_relname, t_attname and t_atttype specific values are
     substituted
*/

To save the issued operators, you need to direct the psql output to a file, using the standard psql features. For example, to save the database db_name statistics to the file dump_stat.sql, you need to execute:

psql -XAtq -c "SELECT dump_statistic()" имя_базы > dump_stat.sql

Supporting Functions

to_schema_qualified_type(typeid oid) returns text

Returns the schema-qualified name of the type for the given typeid.

to_schema_qualified_relation(relid oid) returns text

Returns the schema-qualified relation name for the given relid.

to_attname(relation regclass, colnum int2) returns text

For the given relation and colnum, returns the column name as a text value.

to_attnum(relation regclass, column text) returns int2

For the given relation and column, returns the column number as an int2 value.

to_atttype(relation regclass, column text) returns text

For the given relation and column, returns the

to_atttype(relation regclass, colnum int2) returns text

For the given relation and colnum, returns the schema-qualified name of the column type as a text value.

Limits

You cannot export separately: schemas, schemas and tables, relations.

The extension is not compatible with the qss_encrypt_statistic parameter (both export and import do not work). Exporting statistics is considered unsafe when encryption is enabled.

Implementation Notes

In UPDATE and INSERT, valueN are the raw values that are inserted into the pg_statistic table. In UPDATE and INSERT, they are the same, i.e. duplicate each other (except that in UPDATE the notation is "name = value", and in INSERT it is simply "value"), due to the fact that the decision whether UPDATE or INSERT occurs is made at import time, not at export time.
The to_attname, to_atttype and to_attnum functions are used to avoid using table and field IDs, since they may differ in the database being imported into. Thus, these functions take a human-readable name and convert it to the OIDs of the current instance.
The logic behind using UPDATE and INSERT is that UPDATE is attempted first if statistics for the corresponding rows already exist in pg_statistic, otherwise INSERT is attempted.
The array_in function creates a value of anyarray type, accepting a string of a certain format (e.g. '{1,2,3}') and the array element type. Example: select array_in('{1,2,3}', 'int4'::regtype, -1);.