A package query returns a package -- a multiset of tuples -- that maximizes or minimizes a linear objective function subject to linear constraints, thereby enabling in-database decision support. Prior work has established the equivalence of package queries to Integer Linear Programs (ILPs) and developed the SketchRefine algorithm for package query processing. While this algorithm was an important first step toward supporting prescriptive analytics scalably inside a relational database, it struggles when the data size grows beyond a few hundred million tuples or when the constraints become very tight. In this paper, we present Progressive Shading, a novel algorithm for processing package queries that can scale efficiently to billions of tuples and gracefully handle tight constraints. Progressive Shading solves a sequence of optimization problems over a hierarchy of relations, each resulting from an ever-finer partitioning of the original tuples into homogeneous groups until the original relation is obtained. This strategy avoids the premature discarding of high-quality tuples that can occur with SketchRefine. Our novel partitioning scheme, Dynamic Low Variance, can handle very large relations with multiple attributes and can dynamically adapt to both concentrated and spread-out sets of attribute values, provably outperforming traditional partitioning schemes such as KD-Tree. We further optimize our system by replacing our off-the-shelf optimization software with customized ILP and LP solvers, called Dual Reducer and Parallel Dual Simplex respectively, that are highly accurate and orders of magnitude faster.

翻译：包查询返回一个包（即元组的多重集），在满足线性约束的条件下最大化或最小化线性目标函数，从而支持数据库内的决策分析。先前研究已证明包查询与整数线性规划问题的等价性，并提出了SketchRefine算法用于包查询处理。虽然该算法是在关系数据库中可扩展地支持规范性分析的重要第一步，但当数据规模超过数亿个元组或约束条件变得非常严格时，其性能会显著下降。本文提出Progressive Shading算法，这是一种能高效扩展到数十亿元组规模并优雅处理严格约束的新型包查询处理算法。该算法通过求解基于关系层次结构的优化问题序列来实现，每个层次关系由原始元组按不断细化的分区策略划分为同质组直至恢复原始关系。这种策略避免了SketchRefine算法可能过早丢弃高质量元组的问题。我们提出的新型分区方案动态低方差分区能够处理包含多属性的大规模关系，并可根据属性值的集中或分散分布动态调整，在理论上优于KD-Tree等传统分区方案。我们进一步优化系统，将商用优化软件替换为定制的整数线性规划求解器Dual Reducer与线性规划求解器Parallel Dual Simplex，这些求解器具有高精度且运行速度提升数个数量级。