Automatic statistics collection

How it works

The autovacuum process monitors tables for insert, update, and delete activity. When the following condition is met for a table, AnalyticDB for PostgreSQL runs ANALYZE automatically:

changed rows > (table rows × autovacuum_analyze_scale_factor) + autovacuum_analyze_threshold

Where:

• changed rows — rows affected by insert, update, or delete since the last ANALYZE

• table rows — total row count from the last ANALYZE

• autovacuum_analyze_scale_factor — proportion threshold; default 10%

• autovacuum_analyze_threshold — minimum row count before the proportion is evaluated; default 50 rows

Lock behavior

Asynchronous collection holds a 4-level lock on the table. If a business process requests a conflicting lock, the auto-analyze process releases its lock so the business process can proceed.

Partitioned tables

For partitioned tables, AnalyticDB for PostgreSQL runs ANALYZE on each subpartition and merges the results into the parent partition. This ensures the optimizer has accurate statistics across the full partition hierarchy.

Monitor the auto-analyze process

Asynchronous collection is scheduled by the autovacuum process. Use the following queries to check its status.

• In AnalyticDB for PostgreSQL V6.0:

  SELECT * FROM pg_stat_activity WHERE query LIKE 'autovacuum%';

• In AnalyticDB for PostgreSQL V7.0:

  SELECT * FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';

• List tables that need ANALYZE (with autovacuum_analyze_scale_factor = 10% and autovacuum_analyze_threshold = 50):

  SELECT
      schemaname,
      c.relname,
      c.reltuples,
      n_mod_since_analyze,
      last_autoanalyze,
      (n_mod_since_analyze::float / c.reltuples::float) AS analyze_ratio
  FROM pg_stat_all_tables s, pg_class c
  WHERE c.oid = s.relid
    AND c.reltuples > 50
    AND (n_mod_since_analyze::float > 50 + c.reltuples::float * 0.1)
  ORDER BY (n_mod_since_analyze::float / c.reltuples::float) DESC;

Instance-level parameters

Most instances work well with the default values. To modify an instance-level parameter, submit a ticket.

Table-level parameters

Override instance-level defaults for specific tables using ALTER TABLE.

ALTER TABLE <table_name> SET (<parameter> = <value>);

ALTER TABLE <table_name> RESET (<parameter>);

ALTER TABLE test RESET (autovacuum_analyze_scale_factor);

How it works

Configure synchronous collection using the gp_autostats_mode parameter, which supports three modes:

• `none` — disables synchronous collection. Statistics are updated only by asynchronous collection or manual ANALYZE.

• `on_no_stats` (default) — runs ANALYZE the first time a table without statistics is modified by any of the following statements: After the first analysis, subsequent updates rely on asynchronous or manual collection.

  • CREATE TABLE AS SELECT

  • INSERT

  • COPY

• `on_change` — runs ANALYZE after any of the following statements change more rows than gp_autostats_on_change_threshold:

  • CREATE TABLE AS SELECT

  • INSERT

  • UPDATE

  • DELETE

  • COPY

Parameters

Most instances work well with the default values. To fine-tune synchronous collection, configure the parameters at the session level rather than modifying instance-level settings.

Session-level configuration

Configure synchronous collection for the current session without modifying instance-level settings.

• Enable on_no_stats mode for functions and stored procedures in the current session:

  SET gp_autostats_mode_in_functions = on_no_stats;

• Disable synchronous collection for the current session:

  SET gp_autostats_mode_in_functions = on_no_stats;
  SET gp_autostats_mode = NONE;

• Enable on_change mode with a threshold of 100,000 rows for the current session:

  SET gp_autostats_mode = on_change;
  SET gp_autostats_on_change_threshold = 100000;