title: ÐÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½Ð°Ñ ÑÐ¸ÑÑÐµÐ¼Ð° Ð´Ð»Ñ ÑÐ¾ÐºÑÐ°ÑÐµÐ½Ð¸Ñ Ð²ÑÐµÐ¼ÐµÐ½Ð¸ Ð²ÑÐ¿Ð¾Ð»Ð½ÐµÐ½Ð¸Ñ ÑÐµÑÑÑÑÐ¾ÐµÐ¼ÐºÐ¸Ñ Ð·Ð°Ð¿ÑÐ¾ÑÐ¾Ð² Ðº Ð¡Ð£ÐÐ PostgreSQL
title: Ð²ÑÐ¿ÑÑÐºÐ½Ð°Ñ ÐºÐ²Ð°Ð»Ð¸ÑÐ¸ÐºÐ°ÑÐ¸Ð¾Ð½Ð½Ð°Ñ ÑÐ°Ð±Ð¾ÑÐ° Ð¼Ð°Ð³Ð¸ÑÑÑÐ°
titleType: Subtitle
title: A software system to reduce the execution time of resource-intensive queries to the PostgreSQL database
Creators: name: Ð¨Ð°Ð¿Ð¾Ð²Ð°Ð»Ð¾Ð²Ð°, ÐÑÐ¸Ð½Ð°
Publisher: name: Ð¡Ð°Ð½ÐºÑ-ÐÐµÑÐµÑÐ±ÑÑÐ³ÑÐºÐ¸Ð¹ Ð¿Ð¾Ð»Ð¸ÑÐµÑÐ½Ð¸ÑÐµÑÐºÐ¸Ð¹ ÑÐ½Ð¸Ð²ÐµÑÑÐ¸ÑÐµÑ ÐÐµÑÑÐ° ÐÐµÐ»Ð¸ÐºÐ¾Ð³Ð¾
Year: 2023
Subjects: Ð±Ð°Ð·Ñ Ð´Ð°Ð½Ð½ÑÑ
Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ñ
Ð¸Ð½Ð´ÐµÐºÑÑ
Ð¾Ð±ÑÑÐµÐ½Ð¸Ðµ Ñ Ð¿Ð¾Ð´ÐºÑÐµÐ¿Ð»ÐµÐ½Ð¸ÐµÐ¼
PPO
PostgreSQL
Python
database
optimization
index
reinforcement learning

description: ÐÑÐ¿ÑÑÐºÐ½Ð°Ñ ÐºÐ²Ð°Ð»Ð¸ÑÐ¸ÐºÐ°ÑÐ¸Ð¾Ð½Ð½Ð°Ñ ÑÐ°Ð±Ð¾ÑÐ° Ð¼Ð°Ð³Ð¸ÑÑÑÐ° Ð¿Ð¾ÑÐ²ÑÑÐµÐ½Ð° ÑÐµÐ¼Ð° ÑÐ¾ÐºÑÐ°ÑÐµÐ½Ð¸Ñ Ð²ÑÐµÐ¼ÐµÐ½Ð¸ Ð²ÑÐ¿Ð¾Ð»Ð½ÐµÐ½Ð¸Ñ ÑÐµÑÑÑÑÐ¾ÐµÐ¼ÐºÐ¸Ñ Ð·Ð°Ð¿ÑÐ¾ÑÐ¾Ð² Ðº Ð¡Ð£ÐÐ PostgreSQL. ÐÐ¿Ð¸ÑÐ°Ð½Ñ Ð¿Ð¾Ð´ÑÐ¾Ð´Ñ Ðº Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ Ð·Ð° ÑÑÐµÑ ÑÑÐµÐ¼Ñ. Ð ÑÐµÐ·ÑÐ»ÑÑÐ°ÑÐµ Ð¸Ñ Ð°Ð½Ð°Ð»Ð¸Ð·Ð° Ð² Ð¾ÑÐ½Ð¾Ð²Ñ Ð°Ð»Ð³Ð¾ÑÐ¸ÑÐ¼Ð° ÑÐ°Ð±Ð¾ÑÑ Ð»ÐµÐ³Ð»Ð° Ð·Ð°Ð´Ð°ÑÐ° Ð²ÑÐ±Ð¾ÑÐ° Ð¾Ð¿ÑÐ¸Ð¼Ð°Ð»ÑÐ½Ð¾Ð³Ð¾ Ð½Ð°Ð±Ð¾ÑÐ° Ð¸Ð½Ð´ÐµÐºÑÐ¾Ð². ÐÐ·ÑÑÐµÐ½Ñ ÑÐ¾Ð²ÑÐµÐ¼ÐµÐ½Ð½ÑÐµ ÑÑÐ°ÑÑÐ¸ Ð¸ Ð¸Ð½ÑÑÑÑÐ¼ÐµÐ½ÑÑ, ÑÐ²ÑÐ·Ð°Ð½Ð½ÑÐµ Ñ ÑÐµÑÐµÐ½Ð¸ÐµÐ¼ Ð¾Ð±Ð¾Ð·Ð½Ð°ÑÐµÐ½Ð½Ð¾Ð¹ Ð¿ÑÐ¾Ð±Ð»ÐµÐ¼Ñ, Ð¿ÑÐ¾Ð²ÐµÐ´ÐµÐ½Ð¾ Ð¸Ñ ÑÑÐ°Ð²Ð½ÐµÐ½Ð¸Ðµ. ÐÐµÑÑÐ¿ÐµÐºÑÐ¸Ð²Ð½ÑÐ¼ Ð½Ð°Ð¿ÑÐ°Ð²Ð»ÐµÐ½Ð¸ÐµÐ¼ ÑÐ²Ð»ÑÐµÑÑÑ Ð¸ÑÑÐ»ÐµÐ´Ð¾Ð²Ð°Ð½Ð¸Ðµ Ð¿ÑÐ¸Ð¼ÐµÐ½ÐµÐ½Ð¸Ñ Ð¾Ð±ÑÑÐµÐ½Ð¸Ñ Ñ Ð¿Ð¾Ð´ÐºÑÐµÐ¿Ð»ÐµÐ½Ð¸ÐµÐ¼ Ð´Ð»Ñ ÑÐµÑÐµÐ½Ð¸Ñ Ð·Ð°Ð´Ð°ÑÐ¸ Ð²ÑÐ±Ð¾ÑÐ° Ð¸Ð½Ð´ÐµÐºÑÐ¾Ð², ÐºÐ¾ÑÐ¾ÑÐ¾Ðµ ÑÐ°ÐºÐ¶Ðµ ÑÑÐ°Ð»Ð¾ Ð¾ÑÐ½Ð¾Ð²Ð¾Ð¹ ÑÐ°Ð±Ð¾ÑÑ. ÐÑÐ»Ð° ÑÐ°ÑÑÐ¼Ð¾ÑÑÐµÐ½Ð° ÑÐµÐ¾ÑÐµÑÐ¸ÑÐµÑÐºÐ°Ñ Ð¾ÑÐ½Ð¾Ð²Ð° Ð¾Ð±ÑÑÐµÐ½Ð¸Ñ Ñ Ð¿Ð¾Ð´ÐºÑÐµÐ¿Ð»ÐµÐ½Ð¸ÐµÐ¼. Ð ÑÐ°Ð¼ÐºÐ°Ñ Ð¸Ð·ÑÑÐ°ÐµÐ¼Ð¾Ð¹ Ð¿ÑÐ¾Ð±Ð»ÐµÐ¼Ñ Ð±ÑÐ»Ð¸ Ð¾Ð¿ÑÐµÐ´ÐµÐ»ÐµÐ½Ñ Ð°ÑÐ¿ÐµÐºÑÑ ÑÐ¾ÑÐ¼Ð¸ÑÐ¾Ð²Ð°Ð½Ð¸Ñ Ð¼Ð½Ð¾Ð¶ÐµÑÑÐ²Ð° Ð´Ð¾ÑÑÑÐ¿Ð½ÑÑ Ð´ÐµÐ¹ÑÑÐ²Ð¸Ð¹, Ð¿ÑÐµÐ´ÑÑÐ°Ð²Ð»ÐµÐ½Ð¸Ñ ÑÐ¾ÑÑÐ¾ÑÐ½Ð¸Ñ ÑÑÐµÐ´Ñ Ð¸ Ð½Ð°Ð³ÑÐ°Ð´Ñ Ð°Ð³ÐµÐ½ÑÐ°. ÐÑÐµÐ´Ð»Ð¾Ð¶ÐµÐ½Ð½Ð¾Ðµ ÑÐµÑÐµÐ½Ð¸Ðµ Ð´Ð¾Ð¿Ð¾Ð»Ð½Ð¸ÑÐµÐ»ÑÐ½Ð¾ Ð²ÐºÐ»ÑÑÐ°ÐµÑ Ð² ÑÐµÐ±Ñ Ð°Ð²ÑÐ¾Ð¼Ð°ÑÐ¸Ð·Ð°ÑÐ¸Ñ ÑÐ±Ð¾ÑÐ° ÑÐ°Ð±Ð¾ÑÐµÐ¹ Ð½Ð°Ð³ÑÑÐ·ÐºÐ¸. ÐÐ»Ð³Ð¾ÑÐ¸ÑÐ¼ Ð±ÑÐ» ÑÐµÐ°Ð»Ð¸Ð·Ð¾Ð²Ð°Ð½ Ð² Ð²Ð¸Ð´Ðµ Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½Ð¾Ð³Ð¾ Ð¸Ð½ÑÑÑÑÐ¼ÐµÐ½ÑÐ° Ñ CLI Ð¸Ð½ÑÐµÑÑÐµÐ¹ÑÐ¾Ð¼. Ð ÐµÐ·ÑÐ»ÑÑÐ°ÑÐ¾Ð¼ ÐµÐ³Ð¾ Ð¸ÑÐ¿Ð¾Ð»ÑÐ·Ð¾Ð²Ð°Ð½Ð¸Ñ ÑÐ²Ð»ÑÐµÑÑÑ Ð½Ð°Ð±Ð¾Ñ ÑÐµÐºÐ¾Ð¼ÐµÐ½Ð´Ð°ÑÐ¸Ð¹ Ð¿Ð¾ Ð´Ð¾Ð±Ð°Ð²Ð»ÐµÐ½Ð¸Ñ Ð¸Ð»Ð¸ ÑÐ´Ð°Ð»ÐµÐ½Ð¸Ñ Ð¸Ð½Ð´ÐµÐºÑÐ¾Ð². ÐÐ¾Ð»ÑÑÐµÐ½Ð½Ð¾Ðµ ÑÐµÑÐµÐ½Ð¸Ðµ Ð±ÑÐ»Ð¾ Ð¿ÑÐ¾ÑÐµÑÑÐ¸ÑÐ¾Ð²Ð°Ð½Ð¾ Ð½Ð° Ð±ÐµÐ½ÑÐ¼Ð°ÑÐºÐµ TPC-H Ð² ÑÑÐ°Ð²Ð½ÐµÐ½Ð¸Ð¸ Ñ Ð´ÑÑÐ³Ð¸Ð¼ ÑÐ¾Ð²ÑÐµÐ¼ÐµÐ½Ð½ÑÐ¼ Ð°Ð»Ð³Ð¾ÑÐ¸ÑÐ¼Ð¾Ð¼. ÐÐ¾Ð¿Ð¾Ð»Ð½Ð¸ÑÐµÐ»ÑÐ½Ð¾ ÑÑÑÐµÐºÑÐ¸Ð²Ð½Ð¾ÑÑÑ Ð¸Ð½ÑÑÑÑÐ¼ÐµÐ½ÑÐ° Ð±ÑÐ»Ð° Ð¿ÑÐ¾Ð²ÐµÑÐµÐ½Ð° Ð½Ð° ÐÐ ÑÐµÐ°Ð»ÑÐ½Ð¾Ð³Ð¾ IT-Ð¿ÑÐ¾ÐµÐºÑÐ°.
description: The masters final qualification work is devoted to the topic of reducing the execution time of resource-intensive queries to the PostgreSQL database. Approaches to query optimization due to the database schema are described. As the result of their analysis, the task of choosing the optimal set of indexes has become the algorithm base. State-of-the-art researches and tools related to the solution of the indicated problem were studied, comparison was carried out. A perspective direction to solve index selection problem is reinforcement learning, which also became the base of the work. The theoretical basis of reinforcement learning was considered. Within the framework of the problem under consideration, aspects of the formation of a set of available actions, the representation of the environment state and the agents reward were determined. The proposed solution additionally includes automation of workload collection. Algorithm was implemented as a software tool with a CLI interface. The result of its usage is a set of recommendations for adding or removing indexes. The solution was tested against TPC-H benchmark in comparison with another state-of-the-art algorithm. Additionally, the effectiveness of the tool was tested on the database of the real IT project.