Update Statistics
Accurate statistics are essential for good query performance. By running the ANALYZE command, you update table statistics, enabling the query optimizer to generate optimal execution plans. When SynxDB analyzes a table, it stores relevant statistics in system catalog tables. If these statistics become outdated, the optimizer might produce inefficient plans.
Checking whether statistics are up to date
To check whether a table’s statistics are current, use the pg_stat_all_tables system view. The last_analyze column shows the last time the table was manually analyzed, while last_autoanalyze shows the last automatic analyze time. Running an ANALYZE command updates only last_analyze; last_autoanalyze is updated by autovacuum.
Note
The pg_stat_* views are updated with a delay. If you query these columns immediately after ANALYZE, they might still appear empty; wait a moment and query again.
For example, to check the statistics status of the test_analyze table, run:
SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE relname = 'test_analyze';
Generate statistics selectively
Running ANALYZE without parameters updates statistics for all tables in the database, which can be very time-consuming and is generally not recommended. It’s better to selectively analyze tables that have changed or to use the analyzedb utility.
For large tables, ANALYZE can take a long time. If you cannot analyze all columns, you can generate statistics for specific columns using ANALYZE table(column, ...). Be sure to include columns used in joins, WHERE clauses, SORT, GROUP BY, or HAVING clauses.
For partitioned tables, analyze only the partitions that have changed, for example after you add a new partition. Note that ANALYZE can be run on either the root partitioned table or on individual leaf partitions (which store actual data and statistics), and the two are independent: analyzing a leaf partition updates only that partition’s statistics, leaving the root table and the other partitions untouched. See When to run ANALYZE below. You can use the pg_partition_tree() function to list the names of all leaf partitions:
SELECT * FROM pg_partition_tree('parent_table');
Skip unmodified relations
In large databases with many static tables, running ANALYZE on every relation can introduce significant overhead, even when most tables have not changed since the last analyze. To reduce this unnecessary work, you can enable the analyze_only_modified_relations parameter:
SET analyze_only_modified_relations = on;
When this parameter is enabled, ANALYZE checks the changes_since_analyze counter in pgstat for each relation before analyzing it. If the counter is zero, meaning that no INSERT, UPDATE, or DELETE operations have occurred since the last analyze, ANALYZE skips the relation.
When you run ANALYZE VERBOSE, each skipped relation produces the following message:
INFO: skipping analyze relation "<relation_name>", no data modifications since last analyze
Note
This message appears only with the VERBOSE option. Under a plain ANALYZE it is logged at DEBUG2 level and is therefore hidden by default.
This optimization works with:
Regular tables: A table with no modifications since its last analyze is skipped entirely.
Partitioned tables: Each leaf partition is checked individually. Only partitions that have been modified are re-analyzed, while unchanged partitions are skipped.
Inheritance tables: When you run
ANALYZEon individual child tables, each table in the inheritance hierarchy is checked independently. Note that runningANALYZEon the parent table does not individually check each child table; instead, it analyzes the parent and the inheritance tree as a whole.VACUUM (ANALYZE): The optimization also applies when running
VACUUMwith theANALYZEoption.
This parameter defaults to off. You can enable it at the session level using SET, or globally in the postgresql.conf file.
Tip
Enabling analyze_only_modified_relations is especially beneficial for scheduled maintenance operations (such as running ANALYZE on the entire database) in environments with many tables where only a small fraction change regularly.
Improve statistics quality
There is a trade-off between the time spent generating statistics and the quality or accuracy of those statistics. You need to find a balance that fits your workload.
To analyze large tables within a reasonable time, ANALYZE performs random sampling instead of scanning every row. You can increase the number of sample values collected for all columns in a table by adjusting the default_statistics_target configuration parameter. The valid range for this parameter is 1 to 10000, and the default is 100.
By default, default_statistics_target applies to all columns and determines how many values are stored in the most-common-values list. A higher value might improve the optimizer’s estimation accuracy, especially for columns with skewed data distributions.
You can set default_statistics_target at the session level using the SET command. To apply it globally, set it in the postgresql.conf file and reload the configuration.
When to run ANALYZE
It is recommended to run ANALYZE in the following situations:
After loading data
After executing a
CREATE INDEXcommandAfter
INSERT,UPDATE, orDELETEoperations that significantly change the data
ANALYZE only requires a read lock on the table, so it can run in parallel with other database operations. However, for performance reasons, it is not recommended to run ANALYZE at the same time as INSERT, UPDATE, DELETE, CREATE INDEX, or data loading operations.
Note
SynxDB improves the behavior of ANALYZE on partitioned tables. When you explicitly run statistics collection on a leaf partition (for example, ANALYZE sales_1_prt_p2023), the system no longer updates statistics for the root or other partitions. Only when ANALYZE is run on the root table (for example, ANALYZE sales), will statistics for the entire table, including all child partitions, be refreshed.
This change gives you finer control over statistics maintenance and avoids unnecessary updates. In practice, it is recommended to selectively analyze specific partitions or the entire table based on data change patterns.
Collect statistics for Iceberg tables
ANALYZE collects column statistics for Iceberg tables the same way it does for other table types: it samples rows from the table and computes the number of distinct values, the most common values, and a histogram for each column. Run it as usual:
ANALYZE my_iceberg_table;
You can analyze Iceberg tables and regular tables in one statement, or analyze an entire database:
ANALYZE my_iceberg_table, my_heap_table;
ANALYZE;
Without statistics, the optimizer estimates a single row from every Iceberg scan, which on a large table can produce a plan that spills heavily. Analyze an Iceberg table after you load it, and again after writes that change the data distribution.
ANALYZE also refreshes pg_class.reltuples to the table’s live row count. Rows that an UPDATE or DELETE superseded do not count toward that figure, so the value stays consistent with SELECT count(*).
Note
Sampling reads the table’s data files, so ANALYZE on a large Iceberg table takes as long as a full scan of the underlying object storage. Plan it accordingly for very large tables.
When every table you name is an Iceberg table, ANALYZE collects a larger sample than default_statistics_target alone would produce. datalake.iceberg_analyze_statistics_target sets that floor, and its default is 1000. Because sampling reads the whole table anyway, the larger sample costs little and gives steadier estimates for high-cardinality columns. The effective target is the larger of the two parameters, so raising default_statistics_target for the session still wins:
-- Effective target 1000: collects up to 1000 most-common values per column
ANALYZE my_iceberg_table;
-- Effective target 2000: the session setting is higher, so it takes precedence
SET default_statistics_target = 2000;
ANALYZE my_iceberg_table;
A statement that also names a regular table keeps standard default_statistics_target semantics, as does a database-wide ANALYZE:
-- Effective target 100 (the default_statistics_target default)
ANALYZE my_iceberg_table, my_heap_table;
To skip sampling, turn off datalake.enable_iceberg_analyze_sampling (default on). ANALYZE then only refreshes pg_class.reltuples and pg_class.relpages from the table’s Iceberg metadata and collects no column statistics:
SET datalake.enable_iceberg_analyze_sampling = off;
ANALYZE my_iceberg_table;
Use this only when you need the row count refreshed quickly and the optimizer does not depend on column statistics for the table. Column statistics from an earlier ANALYZE stay in place.
Note
ANALYZE on an Iceberg table reports a NOTICE stating that it refreshed pg_class.reltuples and pg_class.relpages from Iceberg metadata. This message appears whether or not sampling ran. To confirm that column statistics exist, query pg_stats:
SELECT attname, n_distinct FROM pg_stats WHERE tablename = 'my_iceberg_table';
Configure automatic statistics collection
The configuration parameters gp_autostats_mode and gp_autostats_on_change_threshold determine when automatic statistics collection is triggered. When triggered, the optimizer will include an ANALYZE step during query execution.
By default, gp_autostats_mode is set to none. If you set it to on_no_stats, statistics will be automatically collected when the table owner performs CREATE TABLE AS SELECT, INSERT, or COPY operations on a table that currently has no statistics.
If gp_autostats_mode is set to on_change, statistics will only be collected when the number of affected rows exceeds the threshold defined by gp_autostats_on_change_threshold. The default threshold is 2147483647. In this mode, when the table owner performs CREATE TABLE AS SELECT, UPDATE, DELETE, INSERT, or COPY, and the affected row count exceeds the threshold, automatic statistics collection will be triggered.
In addition, if the server parameter gp_autostats_allow_nonowner is set to true, SynxDB will collect statistics even when a non-owner user is the first to perform an INSERT or COPY operation, provided that:
gp_autostats_modeis set toon_no_stats.
Setting gp_autostats_mode to none disables automatic statistics collection entirely.
For partitioned tables, inserting data into the top-level parent table does not trigger automatic statistics collection. However, inserting directly into a leaf partition (which physically stores the data) triggers automatic statistics collection.