Join Query Optimization with Deep Reinforcement Learning
This repository contains the DRL-based FOOP-environment:
"Join Query Optimization with Deep Reinforcement Learning Algorithms" by Jonas Heitz and Kurt Stockinger, Zurich University of Applied Sciences, Winterthur, Switzerland
The source code is based on the gym from OpenAI. The code is divided in to two parts (Agent and Environment).
All the code was executed on Ubuntu version 18.04.1.
- In the folder
/gym/envs/database/are the reinforcement learning environments defined to plan queries according to the template of gym.
- In the folder
/queryoptimization/you find the files
cm1_postgres_card.py. The first takes over the parsing of simple SQL-queries and includes the logic of the query planning. Whereas
cm1_postgres_card.pydelivers the expected costs of a query object according to the cost model introduced in the paper “How good are query optimizers, really?” by Leis et al.
We used Ray RLLib to train our deep reinforcement learning models. Therefore, in the folder
agents/run/ you find the following files:
config.py: With the configurations of the models vanilla DQN (SIMPLE_CONFIG), DDQN (DOUBLE_PRIO) and PPO (PPO_CONFIG).
execute.py: Includes the code to execute a set of experiments.
models.py: Includes the neural nets with the action-masking layer.
masking_env_cros.py: Prepares the environments to deliver the information needed for the action-masking layer in
In the folder
/agents/rollout/ you find the scripts to test trained models. The folder
/agents/queries/ contains the queries used for the experiments.
- Install PostrgreSQL
- Load IMDb according to the guide from the JOB
- Install Python 3.*
- Clone repository
- Install virtual environment from
requirements.txtin the project folder
- As a last step you need to update the DB connection details and the path of the query files in the
reset()function of the environment files at
With the script
/agents/run/ you can check if the installation of gym and ray works.
To execute the experiments you can start