Analyze Statement

The analyze statement will analyze and update column data statistics of persisted column(s) of table(s) in a specific schema.

The statistics information is used by the SQL query optimizers, so it is recommended to (re)run the ANALYZE statement on tables after many table inserts/updates/deletes are done.

analyze-statement:
  ANALYZE schemaname [ '.' tablename [ '(' columnname [, columnname ] [, ... ] ')' ] ]

The system administrator or owner of a table can update statistics for
a) all tables in a schema (specify only the schemaname) or
b) all columns of a specific table (specify schemaname and tablename) or
c) a list of specific columns of one table.

Examples

ANALYZE sch2;

ANALYZE sch2."mytable";

ANALYZE "sch2"."mytable" ("col1", col2, "col9");

Analyze will access and profile all data of selected columns and update the statistics. This may take some time when the tables are very large or you analyze all tables in a schema.

Querying statistics

The column data statistics information can be queried from system view: sys.statistics. This view shows statistics information of columns of user tables only, so excludes system tables for convenience.

Instead of the view you can also query any of the following table producing functions:
sys.statistics() or
sys.statistics('myschema') or
sys.statistics('myschema','mytable') or
sys.statistics('myschema','mytable','mycolumn').
The last 3 functions with input arguments are faster than querying the system view.
These 4 table producing functions will also return statistics information on columns of system tables.

Query statistics examples

-- create a test table
CREATE TABLE test.store (id INT, product VARCHAR(32), price DECIMAL(8,2));
INSERT INTO test.store VALUES (1, 'apple', 1.2), (2, 'orange', 2.0), (3, 'banana', 1.5);

-- Retrieve statistics for all user tables in the database.
SELECT * FROM sys.statistics ORDER BY column_id;
/*
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
| column_id | schema | table | column  | type    | width | count | unique | nils  | minval | maxval | sorted | revsorted |
+===========+========+=======+=========+=========+=======+=======+========+=======+========+========+========+===========+
|      8412 | test   | store | id      | int     |     4 |     3 | false  | true  | 1      | 3      | false  | false     |
|      8413 | test   | store | product | varchar |     1 |     3 | false  | true  | apple  | orange | false  | false     |
|      8414 | test   | store | price   | decimal |     4 |     3 | false  | true  | 120    | 200    | false  | false     |
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
*/

-- Analyze every column from table 'test.store' and update the column statistics
ANALYZE test.store;

-- Retrieve statistics for table 'store' in schema 'test'
SELECT * FROM sys.statistics
 WHERE "schema" = 'test'
   AND "table" = 'store'
 ORDER BY column_id;

-- Or alternatively use the faster table returning function:
SELECT * FROM sys.statistics('test', 'store');
/*
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
| column_id | schema | table | column  | type    | width | count | unique | nils  | minval | maxval | sorted | revsorted |
+===========+========+=======+=========+=========+=======+=======+========+=======+========+========+========+===========+
|      8412 | test   | store | id      | int     |     4 |     3 | true   | false | 1      | 3      | true   | false     |
|      8413 | test   | store | product | varchar |     1 |     3 | true   | false | apple  | orange | false  | false     |
|      8414 | test   | store | price   | decimal |     4 |     3 | true   | false | 120    | 200    | false  | false     |
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
*/
-- Notice that the values of "unique", "nils" and "sorted" have been changed after the analyze statement.


-- Meanwhile more inserts are performed...
INSERT INTO test.store VALUES (4, 'pear', 1.2);
INSERT INTO test.store VALUES (5, 'kiwi', 1.9);

-- Analyze columns 'product' and 'price' from 'test.store'
ANALYZE test.store ("product", "price");

-- Retrieve statistics for columns 'product' and 'price'
SELECT * FROM sys.statistics('test', 'store', 'product')
UNION ALL
SELECT * FROM sys.statistics('test', 'store', 'price');
/*
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
| column_id | schema | table | column  | type    | width | count | unique | nils  | minval | maxval | sorted | revsorted |
+===========+========+=======+=========+=========+=======+=======+========+=======+========+========+========+===========+
|      8413 | test   | store | product | varchar |     1 |     5 | true   | false | apple  | pear   | false  | false     |
|      8414 | test   | store | price   | decimal |     4 |     5 | false  | false | 120    | 200    | false  | false     |
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
*/

TRUNCATE TABLE test.store;

SELECT * FROM sys.statistics('test', 'store');
/*
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
| column_id | schema | table | column  | type    | width | count | unique | nils  | minval | maxval | sorted | revsorted |
+===========+========+=======+=========+=========+=======+=======+========+=======+========+========+========+===========+
|      8412 | test   | store | id      | int     |     4 |     0 | false  | false | null   | null   | true   | true      |
|      8413 | test   | store | product | varchar |     1 |     0 | false  | false | null   | null   | true   | true      |
|      8414 | test   | store | price   | decimal |     4 |     0 | false  | false | null   | null   | true   | true      |
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
*/