Leveraging Query Logs and Machine Learning for Parametric Query Optimization

Download PDF

Abstract

Parametric query optimization (PQO) must address two problems: finding a relatively small number of plans to cache for a parameterized query, and for every incoming instance of the parameterized query selecting the best cached plan to use for execution. We describe an approach based on exploiting query logs and use of machine learning (ML) models to select one of the cached plans for an incoming query instance. We also describe how to leverage query logs to identify plans to cache. We conduct extensive experiments using complex parameterized queries from benchmarks and real workloads. Compared to prior techniques, our ML model based technique is consistently faster in tail latency by two to four orders of magnitude, while also achieving significant improvements in tail sub-optimality of plan selection ranging from 1.1x to 25x.