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