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

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) Have fun!