Adaptiveness vs Learning

This is the code repository for the VLDB 2023 paper: Simple Adaptive Query Processing vs. Learned Query Optimizers: Observations and Analysis. In this repository, we include the postgres extension we used, LIP+AJA rewritten queries, and evaluation tools for the queries.

Preperation

For preperation details of Postgres, you may refer to the Balsa repo. The following instructions are adapted from Balsa repo.

Installing Postgres

• Download Postgres source code:

cd /tmp/ # change to any directory to compile and download pg
wget https://ftp.postgresql.org/pub/source/v12.5/postgresql-12.5.tar.gz
tar xzvf postgresql-12.5.tar.gz

• Compile and install Postgres

The default installation location is /mnt/postgresql-12.5. Change it to any other locations if needed.

sudo apt update
sudo apt install build-essential zlib1g-dev
cd postgresql-12.5
./configure --prefix=/mnt/postgresql-12.5 --without-readline
sudo make -j
sudo make install
echo 'export PATH=/mnt/postgresql-12.5/bin:$PATH' >> ~/.bashrc
source ~/.bashrc

Installing pg_hint_plan

sudo apt install git
cd /tmp/
git clone https://github.com/ossc-db/pg_hint_plan.git -b REL12_1_3_7
# Modify Makefile: change line
#   PG_CONFIG = pg_config
# to
#   PG_CONFIG = /mnt/postgresql-12.5/bin/pg_config
sudo make
sudo make install

Loading IMDB data to Postgres

To run the following commands, make sure to run as users other than root.

# Clone this repo
cd /mnt/
git clone https://github.com/yunjiazhang/adaptiveness_vs_learning.git
# Download data
cd adaptiveness_vs_learning/load_data/imdb/
wget -c http://homepages.cwi.nl/~boncz/job/imdb.tgz && tar -xvzf imdb.tgz
# Add header
pip install absl-py
python3 add_headers.py

# Create database at /mnt/postgres_data/, can be changed to any directory
cd /mnt/adaptiveness_vs_learning/
pg_ctl -D /mnt/postgres_data/imdb/ initdb

# Copy the config file
cp load_data/imdb/postgresql.conf /mnt/postgres_data/imdb/

# Start the server
pg_ctl -D /mnt/postgres_data/imdb/ start
bash load_data/imdb/load_job.sh /mnt/adaptiveness_vs_learning/load_data/imdb/

Installing LIP Extension for Postgres

We use Makefile to make the installation procedure fluent. The default PostgreSQL installation directory /mnt/postgresql-12.5. If you use other directories, change the PG_DIR in Makefile. To compile, simply run make.

cd pg_lip_bloom/
make
make install 

Usage Example

To use pg_lip, we need to first rewrite the query with the extension functions provided, then the query can be directly run with the PostgreSQL. For a new PostgreSQL session, run CREATE EXTENSION pg_lip_bloom; to create the extension.

Then the query should be rewritten as the following example:

LIP query rewriting

For simple JOB queries, we provide a auto query rewriting tool ./pg_lip_bloom/lip_query_rewriter/rewriter.py. The main function rewrites all the queries in all_files and output the rewriten queries to the subdir ./pg_lip_bloom/lip_auto_rewrite/. Note that this rewriter only rewrite for LIP extension. It needs PostgreSQL to be running and accept connection at port 5432.

Query Plans

We provide JOB rewritten queries in ./queries/job/LIP+AJA/. The plans include both LIP and AJA, and also applied the optimization rules of LIP manually (see our paper in detail). For other queries, you may consider using the auto-rewriter first and then apply the optimization rules and AJA by hand.

Evaluating Runtimes

We provide a toolkit to evaluate the query workload runtime with and without LIP+AJA in ./runtime_eval/. To run the evaluation tool, first install the required python packages

sudo apt install python3 python3-pip unixodbc-dev libpq-dev
cd runtime_eval/
pip install -r requirements.txt

Then, you may refer to the notebook ./runtime_eval/runtime_quality.ipynb for usage examples.

Baseline RL-based Query Optimizers

We used the original repo provided by the authors of Bao and Balsa to conduct our comparative study. We are thankful to the authors of Balsa and Bao for transparency in their work and releasing their code. Our research would have been far more difficult and the results harder to understand without this gracious contribution by the Balsa and Bao team.

Citation

TBD