OPERA

This repository is a compact source-code release for running the OPERA training and evaluation path used by the paper. OPERA is a learned execution-time predictor that uses optimizer cost signals and operator-level cost-formula features as priors; the method details are described in the paper.

Repository Layout

• run_opera.py: main command-line entry for loading data, training OPERA, evaluating on the selected test split, and writing metrics.
• requirements.txt: minimal Python dependency list.
• data/: place generated train/test trace files here. Trace files are JSONL text files and are not included in the release.
• data/benchmark/: SQL templates for JOB/IMDB, TPC-H, and TPC-DS trace collection.
• data/schemainfo/: generated schema and configuration metadata cache for local workloads.
• cost_file/: PostgreSQL operator cost-formula hooks used to derive operator-level prior features.
• common/: shared parsing, plan traversal, catalog lookup, and runtime cost-model helpers.
• db_info/: schema metadata loading and optional live PostgreSQL connection helpers.
• docs/supplemental_operator_formulas.pdf: supplemental documentation for the operator cost formulas implemented by the release.
• tools/generate_workload_traces.py: reads benchmark SQL, samples PostgreSQL knobs, runs EXPLAIN ANALYZE, and writes training/evaluation trace files.
• tools/sql_postprocess.py: PostgreSQL compatibility rewrites for benchmark SQL.
• tools/build_node_cost_cache.py: generates the operator-cost feature artifact from selected traces and cost_file/ hooks.
• tools/validate_query_total_runtime_based_cost.py: support validator used by the cache builder.

This release intentionally excludes raw trace files, generated schema caches, operator-cost feature artifacts, plotting code, historical ablation scripts, generated run directories, and paper-table post-processing scripts.

Setup

The release was prepared with the following software environment:

Python: 3.11.15
PostgreSQL: 13.3

PostgreSQL 13.3 is recommended when collecting plans, rebuilding schema metadata, or preparing operator-cost features for a custom workload. The included cost hooks and schema-statistics helpers follow PostgreSQL 13 behavior.

Install the Python dependency set before training or rebuilding artifacts:

pip install -r requirements.txt

requirements.txt includes the packages needed for model training, operator-cost feature preparation, and PostgreSQL metadata access:

numpy==2.4.4
torch==2.11.0+cu128
psycopg2-binary==2.9.12
paramiko==4.0.0
pandas==3.0.2

Generate Trace Files

Set PostgreSQL connection information before collecting traces:

export OPERA_DB_HOST=127.0.0.1
export OPERA_DB_USER=postgres
export OPERA_DB_PASSWORD=your_password
export OPERA_DB_PORT=5432

Example trace collection:

python tools/generate_workload_traces.py \
  --workload workload_name \
  --db-name database_name \
  --sql-file data/benchmark/workload_name \
  --out data/trace_file_name.txt \
  --n-samples-per-query 1 \
  --sample-seed 0

Repeat the command for each train/test trace file you want to collect.

Train And Evaluate

After preparing the trace files:

python run_opera.py \
  --device cuda \
  --train-files workload_label_1:data/train_file_name_1.txt,workload_label_2:data/train_file_name_2.txt \
  --test-files workload_label_1:data/test_file_name_1.txt,workload_label_2:data/test_file_name_2.txt \
  --outdir outputs/run_name

--train-files and --test-files are comma-separated label:path entries. The label is used as the workload identifier in model features; the path points to a generated trace file. The command prepares data/opera_node_cost_cache.npz when it is missing, reuses it when it matches the selected trace files, trains on the selected train files, evaluates on the selected test files, and writes:

outputs/run_name/metrics.json

If the selected traces or --max-plan-nodes changed after a cache was generated, rebuild it explicitly:

python run_opera.py \
  --device cuda \
  --train-files workload_label_1:data/train_file_name_1.txt,workload_label_2:data/train_file_name_2.txt \
  --test-files workload_label_1:data/test_file_name_1.txt,workload_label_2:data/test_file_name_2.txt \
  --rebuild-node-cost-cache \
  --outdir outputs/run_name

Database Metadata

Schema and configuration metadata can be loaded from data/schemainfo/ when matching cache files are available.

If the schema cache for a workload is missing, db_info/database_info.py falls back to a live PostgreSQL connection. In that path, db_info/infos.py reads connection settings from environment variables:

OPERA_DB_HOST
OPERA_DB_USER
OPERA_DB_PASSWORD
OPERA_DB_NAME
OPERA_DB_PORT
OPERA_PG_COMMAND_CTRL
OPERA_SSH_USER
OPERA_SSH_PASSWORD
OPERA_SSH_PORT