用预执行信号预测云数据仓库查询耗时，提升成本估算精度。
Pre-Execution Query Slot-Time Prediction in Cloud Data Warehouses: A Feature-Scoped Machine Learning Approach
- 基于SQL结构、数据量和查询文本构建复杂度特征，预测执行前的资源耗时。
- 在主流查询上实现1.17分钟的平均误差，显著优于基准方法30%以上。
- 特别适合预算敏感型查询，对长尾超时查询效果有限但可定位改进方向。
云数据仓库按槽位时间计费。在共享多租户环境下，查询成本波动大且难以预估，导致预算超支和调度恶化。静态查询规划启发式方法无法捕捉复杂SQL结构、数据倾斜和负载竞争。本文提出一种特征聚焦的机器学习方法，仅利用提交前可观测信号预测BigQuery槽位时间：基于SQL算子开销的结构化查询复杂度评分、来自规划器估算的数据量特征及工作负载元数据，以及来自查询文本的文本特征。刻意排除运行时因素（槽位池利用率、缓存状态、实际倾斜）。模型采用直方图梯度提升回归器，对槽位时间取对数后训练，并将TF-IDF与512维截断SVD文本管道与数值和类别特征融合。在7个部署环境共749个查询上训练，于两个保留环境共746个查询上进行分布外评估，全工作负载上达到1.17分钟的平均绝对误差（MAE）、4.71的均方根误差（RMSE）和74%的解释方差。对成本敏感查询（≥0.01分钟，共282个）的MAE为3.10，优于预测均值基线（4.95）和中位数基线（4.54），降低30-37%。对长尾查询（≥20分钟，共22个）未超越简单基线，符合假设：长尾查询受当前特征范围外的未观测运行时因素主导。文中描述了基于复杂度的双模型架构作为实用优化，未来工作包括缩小长尾差距。
原文摘要 · Abstract (English)
Cloud data warehouses bill compute based on slot-time consumed. In shared multi-tenant environments, query cost is highly variable and hard to estimate before execution, causing budget overruns and degraded scheduling. Static query-planner heuristics fail to capture complex SQL structure, data skew, and workload contention. We present a feature-scoped machine learning approach that predicts BigQuery slot-time before execution using only pre-execution observable signals: a structured query complexity score derived from SQL operator costs, data volume features from planner estimates and workload metadata, and textual features from query text. We deliberately exclude runtime factors (slot-pool utilization, cache state, realized skew) unknowable at submission. The model uses a HistGradientBoostingRegressor trained on log-transformed slot-time, with a TF-IDF + TruncatedSVD-512 text pipeline fused with numeric and categorical features. Trained on 749 queries across seven deployment environments and evaluated out-of-distribution on 746 queries from two held-out environments, the model achieves MAE 1.17 slot-minutes, RMSE 4.71, and 74% explained variance on the full workload. On cost-significant queries (slot-time >= 0.01 min, N=282) the model achieves MAE 3.10 versus 4.95 for a predict-mean baseline and 4.54 for predict-median, a 30-37% reduction. On long-tail queries (>= 20 min, N=22) the model does not outperform trivial baselines, consistent with the hypothesis that long-tail queries are dominated by unobserved runtime factors outside the current feature scope. A complexity-routed dual-model architecture is described as a practical refinement, and directions for closing the long-tail gap are identified as future work.
Thank you to arXiv for use of its open access interoperability. PaperDance 不是 arXiv 官方产品；中文卡片由大模型生成，请以原文为准。