Can Phoenix CBO Prune Columns Without Statistics?
Yes, the Phoenix Cost-Based Optimizer (CBO) can still prune column families even when specific column statistics are missing, provided the query predicates reference indexed columns. The CBO relies on index cardinality and metadata to determine the access path, allowing it to avoid loading unnecessary column families from HBase regardless of whether general table statistics are current.
Likely Explanation vs. Confirmed Behavior
While the CBO can prune columns based on the SELECT list and index usage, missing statistics impact the selectivity estimation. In Phoenix 5.x, if the optimizer cannot find statistics for a column used in a predicate, it typically falls back to a default selectivity estimate (often 10%).
- Confirmed: Bucket pruning for high-cardinality indexed columns remains effective because bucket metadata is stored separately from the general column statistics used for cost estimation.
- Likely: If a predicate involves a non-indexed column and statistics are missing, the CBO cannot estimate a cost reduction and will likely trigger a full region scan.
Verification and Optimization Steps
To determine if your queries are pruning effectively or falling back to full scans, use the following steps:
- Analyze the Execution Plan: Run the
EXPLAIN command to verify which columns are being projected and whether the scan is restricted to specific regions.
EXPLAIN SELECT col1, col2 FROM my_table WHERE indexed_col = 'value';
- Check Statistics Presence: Verify which statistics are actually available for the table.
SYSTEM STATISTICS SHOW TABLE 'my_table';
- Toggle Statistics: If you suspect the CBO is making poor decisions due to stale data, test the plan with
phoenix.statistics.enabled set to false to see if the rule-based fallback performs better.
Diagnostic Requirement
To provide a more precise recommendation, please specify if the phoenix.statistics.enabled property is currently set to true or false in your environment, as this determines whether the optimizer attempts to use the cost model at all.
phoenix