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.

For example, you load timestamp data into a large table at regular intervals and query the table for the mostly recently loaded rows. The following scenarios influence how optimizer chooses a plan:

In this case, you can run ANALYZE_STATISTICS after loading new data on day 16. You can also look for statistics in the EXPLAIN-generated query plan. For example, when statistics are outside a histogram's boundaries, the query plan is annotated with a status.

Vertica provides a number of tools like ANALYZE_STATISTICS for analyzing and updating database statistics. For an overview, see Database Statistics Tools.

In This Section