SQL Keywords

SQL ANALYZE

What is SQL ANALYZE used for?

Collects table and column statistics for the query planner to optimize execution plans.
Sign up to get up to date news on SQL keywords
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ANALYZE: PostgreSQL, Amazon Redshift, Greenplum, MySQL (as ANALYZE TABLE), MariaDB, SQLite (ANALYZE), SQL Server (UPDATE STATISTICS), Oracle (DBMS_STATS.GATHER_TABLE_STATS)

SQL ANALYZE Full Explanation

ANALYZE scans specified tables (or all tables in the current database if none are named) and builds statistical metadata that the optimizer uses to choose efficient query plans. Statistics include histogram bounds, most-common values, distinct counts, and correlation metrics. Running ANALYZE does not modify user data; it updates rows in the system catalog (such as pg_statistic in PostgreSQL). The command can be executed manually or automatically by a database autovacuum daemon. Frequent data changes, massive inserts, or after bulk loads usually warrant a manual ANALYZE to keep statistics accurate. VERBOSE output shows progress details. Sampling is used rather than a full scan to reduce overhead, but the sample size scales with table size to maintain accuracy. ANALYZE requires appropriate privileges (typically the table owner or a superuser).

SQL ANALYZE Syntax

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

SQL ANALYZE Parameters

  • table_name (identifier) - Name of the table to analyze (optional, analyzes all tables if omitted)
  • column_name (identifier) - One or more columns to analyze selectively (optional)
  • VERBOSE (keyword) - Prints progress messages while running (optional)

Example Queries Using SQL ANALYZE

-- Analyze every table in the current database
ANALYZE;

-- Analyze a single table
ANALYZE public.orders;

-- Analyze specific columns only
ANALYZE VERBOSE public.orders (order_date, total_amount);

Expected Output Using SQL ANALYZE

  • System catalog statistics are updated
  • No resultset is returned unless VERBOSE is specified, in which case informational messages appear
  • Subsequent queries benefit from better execution plans

Use Cases with SQL ANALYZE

  • After bulk inserts, updates, or deletes to refresh statistics
  • Before benchmarking a query to ensure planner accuracy
  • Troubleshooting poor query plans
  • Part of maintenance jobs alongside VACUUM

Common Mistakes with SQL ANALYZE

  • Forgetting to run ANALYZE after large data changes, leading to suboptimal plans
  • Assuming ANALYZE locks tables; it only takes a lightweight ShareUpdateExclusive lock
  • Running ANALYZE on read replicas where stats are unused
  • Expecting ANALYZE to reclaim disk space (use VACUUM for that)

Related Topics

VACUUM, EXPLAIN, VACUUM ANALYZE, STATISTICS, AUTOVACUUM, UPDATE STATISTICS

First Introduced In

PostgreSQL 6.5 (1999)

Frequently Asked Questions

How long does ANALYZE take?

Execution time depends on table size and sampling rate. It is generally fast because it samples rows rather than scanning every row.

Does ANALYZE affect data?

No. It only writes to system catalogs, leaving user data untouched.

Is ANALYZE the same as VACUUM?

No. VACUUM reclaims dead tuples and can optionally analyze. ANALYZE only gathers statistics.

Can I automate ANALYZE?

Yes. In PostgreSQL the autovacuum process runs ANALYZE automatically based on insert and update thresholds.

Sign up to get up to date news on SQL keywords
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.
Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo

Check out other commonly used SQL Keywords!