Phoenix secondary index statistics refresh cadence after bulk data changes

Answer to the Core Question

When to refresh statistics? Most production deployments refresh Phoenix statistics either:

• Immediately after every bulk load (INSERT/UPDATE/DELETE batch), or
• When the number of rows modified in the last 24 h exceeds roughly 5–10 % of the table’s total rows.

This 5–10 % rule is a community‑derived heuristic; Phoenix itself does not publish a staleness threshold.

Why 5–10 %?

Statistics become stale when the distribution of column values changes enough to alter the cost estimates used by the optimizer. Empirical observation shows that a bulk load that alters few rows relative to the table size rarely changes the optimal plan, whereas a load that touches a sizable fraction of rows often forces the optimizer to fall back to a full table scan. The 5–10 % window balances two opposing costs:

• Too frequent updates increase write amplification on both global and local indexes.
• Too infrequent updates risk plan regression and higher query latency.

How to Verify the Cadence

Follow these steps to confirm that your chosen cadence keeps plans stable without excessive overhead:

1. Baseline capture

   -- 1. Run a representative query set
   SELECT * FROM t WHERE col = 'value';
   -- 2. Record the EXPLAIN plan
   EXPLAIN SELECT * FROM t WHERE col = 'value';
   -- 3. Note query latency (e.g., via client metrics or JMX)
   

2. Bulk load execution

   -- Example: load 10 M rows into t
   INSERT INTO t SELECT ...;
   

3. Refresh statistics

   UPDATE STATISTICS t;
   

4. Post‑refresh capture

   EXPLAIN SELECT * FROM t WHERE col = 'value';
   -- Re‑run the query and capture latency
   

5. Compare plans and latency
   • If the plan changed from a full scan to an index scan and latency dropped, the refresh was effective.
   • If latency increased or remained unchanged, consider adjusting the threshold or refresh frequency.
6. Monitor write impact

   -- Query Phoenix JMX for write latency/throughput
   jmx:phoenix.write.latency
   jmx:phoenix.write.throughput
   

   Keep an eye on write amplification metrics; if they spike after frequent updates, you may be over‑refreshing.

Using the STATISTICS System Table

Phoenix exposes a STATISTICS table that records the last refresh time and row count. Query it to verify that your refresh runs as expected:

SELECT TABLE_NAME, ROW_COUNT, LAST_REFRESH FROM SYSTEM.STATISTICS WHERE TABLE_NAME = 't';

Heuristic for Estimating Decay Rate

Because Phoenix has no built‑in decay‑rate model, operators typically estimate staleness by:

• Computing the ratio modified_rows / total_rows after each bulk load.
• If the ratio exceeds the chosen threshold (5–10 %), trigger a refresh.
• Optionally, track query plan changes over time to fine‑tune the threshold.

What Missing Detail Could Change the Recommendation?

To refine the 5–10 % rule, we need to know the size of your daily bulk load relative to the total table size. If your daily load is consistently above 20 % of the table, you might adopt a stricter threshold or refresh after every load. Conversely, a <1 % load may allow a longer cadence.

Summary

In short: refresh statistics after each bulk load or when modified rows exceed ~5–10 % of the table. Validate by comparing EXPLAIN plans and latency, monitor write metrics for overhead, and adjust the threshold based on your specific bulk‑load volume.