Query Optimizer Using Machine Learning

Abstract

Relational databases must turn SQL into efficient execution plans quickly and reliably. Classic optimizers do this with statistics and hand-written rules, which work well on simple, stable data but often struggle with correlations, skew, and changing workloads. This thesis studies when and how machine learning (ML) can help without adding risk or slowing planning. We organize recent work into five stages of the pipeline: estimating result sizes, predicting and ordering plan costs, choosing join orders, selecting among candidate plans, and adapting during execution. For each stage we compare methods by accuracy, planning overhead, worst-case slowdowns, ability to handle drift, and ease of integrating with existing systems, and we summarize the evidence in compact tables. The main finding is pragmatic: hybrids that add learned signals to the existing optimizer are more dependable than attempts to replace it end-to-end. We provide a simple deployment playbookstart with better result-size estimates, add a lightweight re-ranking/selection step, consider guarded search guidance and modest runtime feedback only with fallbacks and limits, and monitor a few clear metrics. Finally, we outline evaluation and reporting practices that focus on overall optimizer impact rather than isolated model accuracy.****

Les bases de donn´ees relationnelles doivent transformer des requˆetes SQL en plans dex´ecution efficaces, rapidement et de fa¸con fiable. Les optimiseurs classiques sappuient sur des statistiques et des r`egles ´ecrites `a la main; cela fonctionne bien lorsque les donn´ees sont simples et stables, mais devient fragile face aux corr´elations, aux d´es´equilibres et aux charges qui ´evoluent. Ce m´emoire examine quand et comment lapprentissage automatique peut aider sans rallonger le temps de planification ni augmenter le risque. Nous structurons les travaux r´ecents en cinq ´etapes du pipeline: estimer la taille des r´esultats, comparer et ordonner les plans, choisir lordre des jointures, s´electionner parmi plusieurs plans propos´es, et sadapter pendant lex´ecution. Pour chaque ´etape, nous discutons la pr´ecision, le surcoˆut, le risque de contre-performance, la capacit´e `a sadapter aux changements et la facilit´e dint´egration, et nous synth´etisons ces ´el´ements dans des tableaux compacts. La conclusion est pragmatique: les approches hybrides qui ajoutent des signaux appris `a loptimiseur existant sont plus fiables que les tentatives de le remplacer enti` erement. Nous proposons un guide simple de d´eploiement (mieux estimer, reclasser l´eg`erement les plans, nactiver le guidage de recherche et ladaptation `a lex´ecution quavec des garde-fous) et recommandons une ´evaluation centr´ee sur limpact de bout en bout plutˆot que sur des m´etriques de mod`ele isol´ees.