Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL ANALYZE

The `ANALYZE` command in PostgreSQL collects statistics about the contents of tables in the database. These statistics are used by the query planner to generate efficient execution plans for queries.

Usage

The `ANALYZE` command is used to update statistics in the database to improve the performance of query execution. It can be run on specific tables, specific columns, or the entire database.

ANALYZE [VERBOSE] [table_name [ (column_name [, ...] ) ] ];

In this syntax, `ANALYZE table_name` updates statistics for the specified table, while `ANALYZE` without arguments processes all tables in the current database. The `VERBOSE` option provides detailed output.

Note: The `ANALYZE` command is automatically executed by the autovacuum daemon, which is enabled by default in PostgreSQL. This reduces the need for manual execution of `ANALYZE` under normal circumstances.

Examples

1. Analyzing the Entire Database

ANALYZE;

This command updates statistics for all tables in the current database, ensuring the query planner has up-to-date information for optimization.

2. Analyzing a Specific Table

ANALYZE employees;

Here, the statistics for the `employees` table are updated, which can lead to more efficient query execution involving this table.

3. Analyzing Specific Columns

ANALYZE customers (first_name, last_name);

This example updates statistics only for the `first_name` and `last_name` columns in the `customers` table, which is useful if queries often filter or sort by these columns.

Tips and Best Practices

  • Regular Maintenance. Run `ANALYZE` regularly, especially after significant data changes, to maintain optimal query performance. However, if autovacuum is enabled, manual execution may be redundant.
  • Use with `VACUUM`. Combine `ANALYZE` with `VACUUM` to reclaim storage space and update statistics in a single maintenance operation.
  • Automate with Autovacuum. Enable the autovacuum daemon to automatically perform `ANALYZE` tasks on tables, reducing the need for manual intervention.
  • Target High-impact Areas. Focus on tables and columns that are frequently queried or have undergone substantial modifications for targeted performance improvements.
  • Monitor Statistics. Use the `pg_stats` view to check current statistics for a table or column, providing insight into the data used by the query planner.
Performance Note: Running `ANALYZE` can increase I/O load, which might impact performance temporarily during execution, especially on large tables. Plan execution during off-peak hours if necessary.