Analyze Statistics at the Schema Level (Part 1): Quick Tip

[Deleted User] · June 2018

Jim Knicely wrote this tip.

The ANALYZE_STATISTICS function collects and aggregates data samples and storage information from all nodes that store projections associated with the specified table. The function accepts a table/projection/column name as input.

What if you wanted to get stats for all of the tables in a schema?

One option is to have Vertica build SQL statements for you that execute the ANALYZE_STATISTICS function, one for each table in a given schema.

Example:

dbadmin=> CREATE SCHEMA test_stats;
CREATE SCHEMA

dbadmin=> \d test_stats.*
                                  List of Fields by Tables
   Schema   | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
------------+-------+--------+------+------+---------+----------+-------------+-------------
 test_stats | t1    | c      | int  |    8 |         | f        | f           |
 test_stats | t2    | c      | int  |    8 |         | f        | f           |
 test_stats | t3    | c      | int  |    8 |         | f        | f           |
(3 rows)

dbadmin=> \q

[dbadmin@s18384357]$ vsql -Atc "SELECT 'SELECT analyze_statistics(''' || table_schema || '.' || table_name || ''');' FROM v_catalog.tables WHERE table_schema = 'test_stats';" | vsql -e
SELECT analyze_statistics('test_stats.t1');
analyze_statistics
--------------------
                  0
(1 row)

SELECT analyze_statistics('test_stats.t2');
analyze_statistics
--------------------
                  0
(1 row)

SELECT analyze_statistics('test_stats.t3');
analyze_statistics
--------------------
                  0
(1 row)