Divo: Learning a Stable and Effective Query Optimizer with a Diverse Workload

Learned query optimizers (LQOs) have shown remarkable progress in recent years. Despite achieving competitive performance compared with traditional methods, current LQOs struggle to retain stability and generate efficient plans when exposed to workloads with substantial diversity, presenting a major challenge in real-world deployment. Divo is a learned query optimizer designed to overcome performance degradation observed in training on diverse workloads. First, Divo proposes a template-evolving query generator to provide diverse queries for sufficient training. By modifying and combining query templates, the query generator synthesizes 3,000 informative new queries with fidelity to existing workloads. Second, Divo establishes a two-phase model training pipeline to enhance RL training with numerous plans collected in advance. We collected over 100,000 plans from public workloads and diverse generated queries to form static experiences, which are effectively leveraged on various training workload configurations to enhance Divo's plan generation through augmenting auxiliary components. Third, Divo proposes a diversity-aware loss function to learn from diversified input queries stably. By learning to predict probability distributions of latencies, Divo flexibly handles various queries and tolerates inconsistent ground truth latencies from training queries. We evaluate Divo's performance on PostgreSQL using a complicated mixed workload of JOB, DSB, Extended JOB, Stack, and generated queries, with three different training and testing workload configurations split by template. Experiments demonstrate Divo's advantage on total execution latency with a 1.3× speedup relative to PostgreSQL, which is 14.9× better than six existing LQOs on average.