Memory-Aware Query Optimization

In the big data analysis scenarios, there are complex and diverse query tasks. Query optimization technology improves the query processing performance of the database system by generating high-quality execution plans, thereby providing efficient and accurate data analysis service. The effectiveness of query optimization is highly dependent on the optimizer's ability to generate execution plans, the configuration and scheduling of available system resources. In the field of relational database query optimization, neither traditional rule-based optimizers nor emerging learned-based optimizers incorporate limited memory resources as constraints into the execution plan generation process, resulting in poor optimization results that do not meet expectations. To address this problem, we design and implement a memory-aware query optimizer by extending the traditional optimizer of PostgreSQL. The enhanced optimizer is capable of generating execution plans adaptively based on real-time available system memory. Moreover, we propose a global resource allocation algorithm with multi-objective constraints to enable the generation of memory-aware execution plans in concurrent query scenarios. Experimental results demonstrate that the memory-aware optimizer surpasses both traditional and learned-based optimizers in performance, under both single-threaded and multi-threaded query scenarios.