Phoenix secondary index statistics refresh cadence after bulk data changes
0 reputation · 15 Jun 2024, 19:48 UTC
Statistics-Driven Index Selection
Phoenix secondary indexes rely on table statistics collected by UPDATE STATISTICS for the optimizer to choose between index access and full scans. After bulk loads, large deletes, or significant data modifications, these statistics become stale and the optimizer may silently revert to suboptimal plans. The system provides no automatic refresh mechanism, leaving the refresh cadence and staleness threshold as an unresolved operational decision.
Measurement and Validation
Running EXPLAIN on representative queries before and after UPDATE STATISTICS reveals plan changes, but does not define when statistics are sufficiently stale to warrant recomputation. Write amplification from global and local indexes adds further cost consideration, making it necessary to measure both read latency and write throughput before committing to an index strategy.
What measurable staleness threshold should trigger a statistics refresh in a production workload with daily bulk loads? How can operators validate that a chosen refresh interval prevents plan regression without introducing excessive overhead? Is there a documented heuristic for estimating statistics decay rate based on data modification volume?
