Auto-Steer

License

This prototype implementation is licensed under the 'MIT license' (see LICENSE).

Requirements

Packages

sqlite3
- Statistics extension (we provide a download script: sqlean-extensions/download.sh)
python3 (at least version 3.10)

Python3 requirements

Install python requirements using the file pip3 install -r requirements.txt

Run Auto-Steer

Database Systems

Auto-Steer-G already supports five open-source database systems:

PostgreSQL
- We tested AutoSteer with PostgreSQL 13
PrestoDB
- PrestoDB does not expose many rewrite rules. Therefore, the following patch exposes the top-7 hints we found in our experiments.
- Get the most recent version of PrestoDB
- Apply the PrestoDB patch : git apply Presto-disable-optimizers-through-session-properties.patch
- Build PrestoDB from source and start the server
MySQL
- We tested AutoSteer with MySQL 8
DuckDB
- Install the DuckDB-python package via pip
SparkSQL
- We run SparkSQL using the official Docker image of its most recent version

DBMS Configuration

Depending on your custom installation and DBMS setup, add the required information to the configs/<dbms>.cfg-file.

Executing Auto-Steer's Training Mode

Auto-Steer's training mode execution consists of two steps:

(A) Approximate the query span, and (B) run the dynamic programming-based hint-set exploration

main.py --training --database {postgres|presto|mysql|duckdb|spark} --benchmark {path-to-sql-queries}

By now, Auto-Steer persisted all generated training data (e.g. query plans and execution statistics) in a sqlite-database that can be found under results/<database>.sqlite.
For PrestoDB query plans, we implemented the preprocessing of query plans for tree convolutional neural networks.
```
main.py --inference --database presto --benchmark {path-to-sql-queries}
```
The inference results can be found in the directory evaluation.

Code Formatting

All python files will be checked using pylint before they can be comitted. The code style is primarily based on the Google Python Style Guide. However, it allows longer lines (160 characters).
Please, install and run pylint (there is also a git pre-commit hook) before committing

Cite

If you use AutoSteer in your work, please cite us:

@article{autosteer2023,
    author       = {Anneser, Christoph and Tatbul, Nesime and Cohen, David and Xu, Zhenggang and Pandian, Prithviraj and Laptev, Nikolay and Marcus, Ryan},
    date         = {2023},
    journaltitle = {PVLDB},
    number       = {12},
    pages        = {3515--3527},
    title        = {AutoSteer: Learned Query Optimization for Any SQL Database},
    volume       = {16},
}

wanweiqiangintel/Auto-Steer