Skip to main content
Last updated on

Postgres planner statistics

The Postgres query planner uses planner statistics to estimate how many rows each step of a query returns and to choose an execution plan. This page describes when Postgres generates planner statistics automatically, how to find tables that are missing them, and how to change when they are generated for a table. For how Lakebase preserves statistics across compute restarts and scale to zero, see Statistics collection.

When does ANALYZE run on a table?​

Autovacuum generates planner statistics automatically by running ANALYZE on a table after enough rows change. A table is analyzed when the number of rows inserted, updated, or deleted since the last analyze exceeds:

Text
autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × number of rows in the table

With the default settings (autovacuum_analyze_threshold = 50 and autovacuum_analyze_scale_factor = 0.1), a new table is analyzed after more than 50 rows are inserted, updated, or deleted, and an existing table after more than 50 rows plus 10% of its rows change. As a result, a table that is loaded one time with 50 or fewer rows and never modified is never analyzed by autovacuum. Without planner statistics, the planner relies on default estimates, which can lead to inefficient query plans when that table is joined with larger tables.

Databricks recommends running ANALYZE on new tables after the initial data load, and on tables with 50 or fewer rows (the default autoanalyze threshold), so that accurate planner statistics are in place before the tables are queried.

Analyze partitioned tables​

Autovacuum analyzes each partition of a partitioned table as rows in that partition change, but it never analyzes the partitioned table itself (the parent). Statistics for the parent describe the table as a whole, and they are generated only when you run ANALYZE on the parent. Without them, queries that reference the partitioned table can get inefficient plans. Run ANALYZE on the parent after you first load data into it, and again whenever the distribution of data across its partitions changes significantly:

PostgreSQL
ANALYZE my_schema.my_partitioned_table;

When you run ANALYZE on the parent, Postgres samples rows from all partitions to build the parent's statistics. By default, it also analyzes each partition and updates the partition's statistics, which can take a long time on a large table.

In Postgres 18 and above, use ANALYZE ONLY to update the parent's statistics without analyzing each partition. Postgres still samples rows from all partitions, and autovacuum continues to analyze the partitions as their rows change.

PostgreSQL
ANALYZE ONLY my_schema.my_partitioned_table;

In Postgres 16 and 17, ANALYZE on a partitioned table always analyzes every partition. In any version, to update the statistics for one partition, run ANALYZE on that partition.

Find tables without planner statistics​

To find tables that have no planner statistics, look for tables with no rows in the pg_stats view. Planner statistics are stored durably, so this check is reliable even after cumulative statistics are reset. The query excludes system schemas and the schemas that Lakebase uses internally for the Data API and synced tables.

PostgreSQL
SELECT t.schemaname, t.tablename
FROM pg_tables t
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema', '__db_system', 'pgrst')
  AND t.schemaname NOT LIKE '\_\_db\_user\_%'
  AND NOT EXISTS (
    SELECT 1 FROM pg_stats s
    WHERE s.schemaname = t.schemaname AND s.tablename = t.tablename
  )
ORDER BY 1, 2;

Run the query as a member of the databricks_superuser role or as the table owner. The pg_stats view shows statistics only for columns that the current role can read. For a table with row-level security enabled, it also hides statistics unless the role can bypass row-level security. A table that the current role cannot read, or that has row-level security enabled, can appear in the results even if it has statistics.

Tables that have never contained data also appear in the results, because ANALYZE does not generate statistics for an empty table. Focus on tables that contain data and are used in queries.

To generate planner statistics for a table returned by the query, run ANALYZE:

PostgreSQL
ANALYZE my_schema.my_table;

To analyze every table in the current database, run ANALYZE without a table name.

Customize autovacuum settings for a table​

If a table changes too rarely for the default thresholds, you can lower the analyze threshold for that table only. For example, to analyze a small reference table after more than 10 row modifications regardless of its size:

PostgreSQL
ALTER TABLE my_schema.my_table SET (
  autovacuum_analyze_threshold = 10,
  autovacuum_analyze_scale_factor = 0
);

To view the custom settings on a table, query pg_class.reloptions:

PostgreSQL
SELECT relname, reloptions
FROM pg_class
WHERE oid = 'my_schema.my_table'::regclass;

To restore the default behavior, reset the settings:

PostgreSQL
ALTER TABLE my_schema.my_table RESET (
  autovacuum_analyze_threshold,
  autovacuum_analyze_scale_factor
);

Even with a lower threshold, autovacuum does not analyze a table if the number of rows loaded does not exceed the threshold and the table is never modified afterward. For static tables, run ANALYZE after loading data.

For more information, see the Postgres documentation:

On this page