F.36. online_analyze — update statistics after INSERT, UPDATE, DELETE, or SELECT INTO operations #

The online_analyze module provides a set of features that immediately update statistics after INSERT, UPDATE, DELETE, or SELECT INTO operations for the affected tables.

Note

This extension cannot be used together with autonomous transactions or while built-in connection pooling is enabled.

F.36.1. Module Loading #

To use online_analyze module, load the shared library:

LOAD 'online_analyze';

F.36.2. Module Configuration #

You can configure online_analyze using the following custom variables (default values are shown):

  • online_analyze.enable = on

    Enables online_analyze.

  • online_analyze.verbose = on

    Executes ANALYZE VERBOSE.

    Note

    Since verbose is a reserved SQL key word, this parameter has to be double-quoted when used in SQL queries. For example:

    ALTER SYSTEM SET "online_analyze.verbose" = 'off';

  • online_analyze.scale_factor = 0.1

    Fraction of table size to start online analysis (similar to autovacuum_analyze_scale_factor).

  • online_analyze.threshold = 50

    Minimum number of row updates before starting online analysis (similar to autovacuum_analyze_threshold).

  • online_analyze.min_interval = 10000

    Minimum time interval between ANALYZE calls per table, in milliseconds.

  • online_analyze.table_type = "all"

    Type(s) of tables for online analysis. Possible values are: all, persistent, temporary, none.

  • online_analyze.exclude_tables = ""

    List of tables to exclude from online analysis.

  • online_analyze.include_tables = ""

    List of tables to include in online analysis (online_analyze.include_tables overrides online_analyze.exclude_tables).

  • online_analyze.local_tracking = off

    Enables per-backend tracking for temporary tables by online_analyze. When this variable is set to off, online_analyze uses the default system statistics for temporary tables.

  • online_analyze.lower_limit = 0

    Minimum number of rows in a table required to trigger online_analyze.

  • online_analyze.capacity_threshold = 100000

    Maximum number of temporary tables to store in local cache.

F.36.3. Authors #


      Teodor Sigaev