Analyze Statement

analyze-statement:
  ANALYZE schema_name [ . table_name [ ( column_name [, column_name ] [, ... ] ) ] ]

Gather statistics on column(s) of table(s) in a specific schema and store or update them in the underlying structures. Later they can be retrieved via the sys.statistics view.

Starting on Jan2022 release, in order to mitigate the I/O required to update the 'statistics' table, this table is no longer persisted. Alternately, it was changed into a computed view every time when queried.

In the Jan2022 release, the performance of the ANALYZE operator has improved, therefore the MINMAX parameter is now ignored. At the same time, the statistics are now used in SQL query optimization, so the SAMPLE operator is also ignored.

Examples

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

-- Analyze every column from 'sys.store'
ANALYZE sys.store;

-- Retrieve statistics from every column in the database.
SELECT * FROM sys.statistics;

-- Retrieve statistics from table 'sys.store'
SELECT * FROM sys.statistics
 WHERE "schema" = 'sys'
   AND "table" = 'store';

-- Or more efficiently, the table UDF version can be used:
SELECT * FROM sys.statistics('sys', 'store');
/*
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
| column_id | schema | table | column  | type    | width | count | unique | nils  | minval | maxval | sorted | revsorted |
+===========+========+=======+=========+=========+=======+=======+========+=======+========+========+========+===========+
|      8412 | sys    | store | id      | int     |     4 |     3 | true   | false | 1      | 3      | true   | false     |
|      8413 | sys    | store | product | varchar |     1 |     3 | true   | false | apple  | orange | false  | false     |
|      8414 | sys    | store | price   | decimal |     4 |     3 | true   | false | 120    | 200    | false  | false     |
+-----------+--------+-------+---------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
*/

-- Meanwhile more inserts are performed...
INSERT INTO sys.store VALUES (4, 'pear', 1.2);

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

-- Retrive statistics from column 'price'
SELECT * FROM sys.statistics('sys', 'store', 'price');
/*
+-----------+--------+-------+--------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
| column_id | schema | table | column | type    | width | count | unique | nils  | minval | maxval | sorted | revsorted |
+===========+========+=======+========+=========+=======+=======+========+=======+========+========+========+===========+
|      8414 | sys    | store | price  | decimal |     4 |     4 | false  | false | 120    | 200    | false  | false     |
+-----------+--------+-------+--------+---------+-------+-------+--------+-------+--------+--------+--------+-----------+
*/

Note: The statistics information can be used by the query optimizer to choose the optimal execution plan. Rerun the ANALYZE command after many table manipulations (insert/update/delete).
For large tables this command can take some time.