LAPO: LLM-enhanced adaptive pathway optimization for complex query tasks on key-value stores

LSM-tree-based KV-stores have become the core storage engine of databases due to their write performance and scalability in the era of big data. However, the multi-layered structure and KV paradigm lead to substantial performance bottlenecks during read operations, particularly when processing complex query workloads. To address the critical limitations of MyRocks — especially its suboptimal query path execution strategies and inflexible parameter configurations in complex query processing — we propose an LLM-Enhanced Adaptive Pathway Optimization (LAPO) framework that significantly enhances query performance through a multi-phase collaborative optimization approach. First, during the query parsing phase, a multi-branch path matching (MPM) technique is introduced to dynamically generate multiple sets of optimized parameter configurations to expand the search space of the query optimizer. It effectively circumvents the local optimum limitations inherent in traditional optimizers. Second, in the feature extraction phase, a dependency chain regulation (DCR) strategy is proposed to analyze the implicit dependencies between query logic and underlying KV operations, construct a parameter-path-operation association matrix, and filter high-score optimization candidates, thereby minimizing ineffective search efforts. In addition, an adaptive parameter adjustment (APA) model is developed to take path features as input and utilizes a pre-trained network to predict the optimal execution plan, enabling dynamic decision-making and enhancing resource utilization efficiency. Finally, the framework incorporates an Unnesting SQL Optimization (USO) layer, which rewrites complex nested queries into unnested forms using a fine-tuned large language model (LLM). This external unnesting transformation reduces redundant computations caused by nested queries, thereby improving query performance. The experiments utilize three real-world datasets IMDB, Chinook, and OpenFlights, to evaluate LAPO in comparison to native MyRocks. The results demonstrate that LAPO substantially improves query efficiency by significantly reducing execution time and latency in complex join query scenarios.