Analyzing Row Counts

Vertica lets you obtain row counts for projections and for external tables, through ANALYZE_ROW_COUNT and ANALYZE_EXTERNAL_ROW_COUNT, respectively.

Projection Row Count

ANALYZE_ROW_COUNT is a lightweight operation that automatically collects the number of rows in a projection every 60 seconds to collect a minimal set of statistics and aggregate row counts calculated during loads. You can invoke this function through the function DO_TM_TASK. For example:

=> SELECT DO_TM_TASK ('analyze_row_count', 'public.Emp_Dimension_b0');

To change the collection interval, set the configuration parameter AnalyzeRowCountInterval (see Configuration Parameters). For example, you can change the collection interval to 1 hour (3600 seconds) as follows:

=> ALTER DATABASE mydb SET AnalyzeRowCountInterval = 3600;

External Table Row Count

ANALYZE_EXTERNAL_ROW_COUNT calculates the exact number of rows in an external table. The optimizer uses this count to optimize for queries that access external tables. This is especially useful when an external table participates in a join. This function enables the optimizer to identify the smaller table to use as the inner input to the join, and facilitate better query performance.

The following query calculates the exact number of rows in the external table loader_rejects:

=> SELECT ANALYZE_EXTERNAL_ROW_COUNT('loader_rejects');
 ANALYZE_EXTERNAL_ROW_COUNT
----------------------------
                 0