JIT-aware DBMS planner configuration

Dynamic compilation of certain operator compositions might have a drastic impact on overall query performance, but not be considered during optimal query plan selection by DBMS planner due to lack of knowledge. To tackle this problem, we propose to extend cost model with criteria that make dynamic compilation overhead relevant. The necessity to set up the optimizer criteria is due to the fact that the properties of various execution models impose restrictions on the efficiency of query plan execution using certain operator nodes. For example, the push-model used in the dynamic compiler is advantageous when executing queries using sequential scan. So, dynamic compilation makes sequential scan more efficient than index scan. Using index nodes in such a plan makes the value of the dynamic compilation method diminishing. To overcome these problems, it is proposed to configure the DBMS optimizer, so that it evaluates and takes into account the efficiency of using certain types of nodes when building a query plan with its subsequent dynamic compilation. This paper discusses the modification of the PostgreSQL planner to select the most efficient query execution plan based on hardware characteristics and the execution model of operator nodes with interpretation or compilation.

1. Бучацкий Р.А., Шарыгин Е.Ю. и др. Динамическая компиляция SQL-запросов для СУБД PostgreSQL. Труды ИСП РАН, том 28, вып. 6, 2016, стр. 37-48 / Buchatskiy R.A., Sharygin E.Y. et al. Dynamic compilation of SQL queries for PostgreSQL. Trudy ISP RAN/Proc. ISP RAS, vol. 28, issue 6, 2016, pp. 37-48 (in Russian). DOI: 10.15514/ISPRAS-2016-28(6)-3.

2. Шарыгин Е.Ю., Бучацкий Р.А. и др. Динамическая компиляция выражений в SQL-запросах для СУБД PostgreSQL. Труды ИСП РАН, том 28, вып. 4, 2016 г., стр. 217-240 / Sharygin E.Y., Buchatskiy R.A. et al. Dynamic compilation of expressions in SQL queries for PostgreSQL. Trudy ISP RAN/Proc. ISP RAS, vol. 28, issue 4, 2016. pp. 217-240 (in Russian). DOI: 10.15514/ISPRAS-2016-28(4)- 13.

3. Sharygin E., Buchatskiy R., et al. Runtime specialization of PostgreSQL query executor. Lecture Notes in Computer Science, vol. 10742, 2018, pp. 375-386.

4. The LLVM Compiler Infrastructure. Available at: https://llvm.org/docs/, accessed 13.07.2022

5. Adaptive Query Optimization. Available at: https://github.com/postgrespro/aqo, accessed 13.07.2022

6. PostgreSQL 9.6.24 Documentation. Available at: https://postgrespro.com/docs/postgresql/9.6/index, accessed 13.07.2022

7. Using Explain Analyze in PostgreSQL. Available at: https://postgrespro.ru/docs/enterprise/9.6/using-explain#using-explain-analyze, acсessed 13.07.2022.

8. Graefe G. Volcano – an extensible and parallel query evaluation system. IEEE Transactions on Knowledge and Data Engineering, vol. 6, issue 1, 1994, pp. 120-135.

9. TPC-H, an ad-hoc, decision support benchmark. Available at: http://www.tpc.org/tpch/, acсessed 13.07.2022.

10. Ivanov O., Bartunov S. Adaptive Cardinality Estimation. arXiv:1711.08330, 2017, 12 p.

11. Zhang R., Debray S., Snodgrass R.T. Micro-Specialization: Dynamic Code Specialization of Database Management Systems. In Proc. of the Tenth International Symposium on Code Generation and Optimization, 2012, pp. 63-73.

12. Wu W., Chi Y. et al. Predicting query execution time: Are optimizer cost models really unusable? In Proc. of the IEEE 29th International Conference on Data Engineering (ICDE), 2013, pp. 1081-1092.