Updating Database Statistics

[JimKnicely]

Vertica recommends we update the statistics for a database in the following situations:

• 1. After an initial load is performed
  2. If the data has changed more than 50% since the last time the statistics were updated
  3. If the query plan or results change over time (this may indicate that the statistics have become stale)

Updating the statistics for a table is easy with the ANALYZE_STATISTICS function.

Example:

Code: Select all

dbadmin=> select analyze_statistics('v_datamart.date_dim');
analyze_statistics
--------------------
                  0
(1 row)

The ANALYZE_STATISTICS function will return 0 if the operation was successful.

We can update the statics for all tables in the database by calling the ANALYZE_STATISTICS function without a table name.

Example:

Code: Select all

dbadmin=> select analyze_statistics('');
analyze_statistics
--------------------
                  0
(1 row)

Warning: Analyzing all the tables can take a long time!