Collecting Database Statistics

The Vertica cost-based query optimizer relies on data statistics to produce query plans. If statistics are incomplete or out-of-date, the optimizer is liable to use a sub-optimal plan to execute a query.

Vertica provides two functions that generate up-to-date statistics on table data: ANALYZE_STATISTICS and ANALYZE_STATISTICS_PARTITION collect table-level and partition-level statistics, respectively. After computing statistics, the functions store them in the database catalog.

Both functions perform the following operations:

• Collect statistics using historical queries (at epoch latest) without any locks.
• Perform fast data sampling, which expedites analysis of relatively small tables with a large number of columns.
• Include data from WOS.
• Recognize deleted data instead of ignoring delete markers.

Vertica also provides several functions that help you management database statistics—for example, to export and import statistics, validate statistics, and drop statistics.

After you collect the desired statistics, you can run Workload Analyzer to retrieve hints about under-performing queries and their root causes, and obtain tuning recommendations.