ANALYZE TECH PREVIEW

Synopsis

ANALYZE collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. These statistics help the query planner to determine the most efficient execution plans for queries.

The statistics are also used by the YugabyteDB cost-based optimizer (CBO) to create optimal execution plans for queries. When run on up-to-date statistics, CBO provides performance improvements and can reduce or eliminate the need to use hints or modify queries to optimize query execution.

The YugabyteDB implementation is based on the framework provided by PostgreSQL, which requires the storage layer to provide a random sample of rows of a predefined size. The size is calculated based on a number of factors, such as the included columns' data types.

Syntax

• Grammar
• Diagram

analyze ::= ANALYZE [ VERBOSE ] [ table_and_columns [ , ... ] ]

table_and_columns ::= table_name [ ( column_name [ , ... ] ) ]

Semantics

VERBOSE

Enable display of progress messages.

table_name

Table name to be analyzed; may be schema-qualified. Optional. Omit to analyze all regular tables in the current database.

column_name

List of columns to be analyzed. Optional. Omit to analyze all columns of the table.

Examples

Analyze a single table

yugabyte=# ANALYZE some_table;

ANALYZE

Analyze specific columns

yugabyte=# ANALYZE some_table(col1, col3);

ANALYZE

Analyze multiple tables verbosely

yugabyte=# ANALYZE VERBOSE some_table, other_table;

INFO:  analyzing "public.some_table"
INFO:  "some_table": scanned, 3 rows in sample, 3 estimated total rows
INFO:  analyzing "public.other_table"
INFO:  "other_table": scanned, 3 rows in sample, 3 estimated total rows
ANALYZE

Analyze affects query plans

This example demonstrates how statistics affect the optimizer.

Let's create a new table...

yugabyte=# CREATE TABLE test(a int primary key, b int);

CREATE TABLE

... and populate it.

yugabyte=# INSERT INTO test VALUES (1, 1), (2, 2), (3, 3);

INSERT 0 3

In the absence of statistics, the optimizer uses hard-coded defaults, such as 1000 for the number of rows in the table.

yugabyte=# EXPLAIN select * from test where b = 1;

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on test  (cost=0.00..102.50 rows=1000 width=8)
   Filter: (b = 1)
(2 rows)

Now run the ANALYZE command to collect statistics.

yugabyte=# ANALYZE test;

ANALYZE

After ANALYZE number of rows is accurate.

yugabyte=# EXPLAIN select * from test where b = 1;

                     QUERY PLAN
----------------------------------------------------
 Seq Scan on test  (cost=0.00..0.31 rows=3 width=8)
   Filter: (b = 1)
(2 rows)

Once the optimizer has better idea about data in the tables, it is able to create better performing query plans.