DBPLANBENCH

Setup

<details> <summary>Show Python environment and env file setup</summary>

AWS S3 Setup

<details> <summary>Show AWS S3 setup steps</summary>

Modal sandboxes write results to S3, and the local client retrieves them. Create your own S3 bucket with any name you choose.

1. Create an S3 bucket - Open the S3 console and click Create bucket. - Choose any bucket name (e.g., my-faster-dbs-bucket). - Keep the default settings, and click Create bucket.

2. Set your bucket name in .env

   S3_BUCKET_NAME=your-bucket-name-here
   

3. Create IAM credentials - Open the IAM console and go to Users -> Create user. - On Set permissions, choose Attach policies directly. - Click Create policy, go to the JSON tab, and paste (replace YOUR_BUCKET_NAME with your actual bucket name):

     {
       "Version": "2012-10-17",
       "Statement": [
         {
           "Effect": "Allow",
           "Action": [
             "s3:PutObject",
             "s3:GetObject"
           ],
           "Resource": "arn:aws:s3:::YOUR_BUCKET_NAME/*"
         },
         {
           "Effect": "Allow",
           "Action": "s3:ListBucket",
           "Resource": "arn:aws:s3:::YOUR_BUCKET_NAME"
         }
       ]
     }
     

4. Add credentials to .env - Go to IAM -> Users -> your user -> Security credentials -> Create access key. - Choose the Local code use case, then create the key. - Copy the Access key ID and Secret access key immediately (they are shown only once). - Add them to .env:

     AWS_ACCESS_KEY_ID=your_access_key_id_here
     AWS_SECRET_ACCESS_KEY=your_secret_access_key_here
     

</details>

Modal Setup

<details> <summary>Show Modal setup steps</summary>

Create a Modal account and authenticate:

python3 -m modal setup

This creates ~/.modal.toml with your Modal credentials. Open the file, grab the following credentials, and add them to .env:

MODAL_TOKEN_ID=your_token_id_here
MODAL_TOKEN_SECRET=your_token_secret_here

Next, create a Modal secret for AWS on the Modal dashboard so that sandboxes can access your S3 bucket:

• Click Create new secret and choose AWS from the menu.
• Name the secret s3-aws-credentials.
• Paste your AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY, then save.

Note: the secret name s3-aws-credentials in Modal must match AWS_SECRET_NAME in src/modal_controller/constants.py.

</details>

LLM Setup

<details> <summary>Show LLM setup steps</summary>

This project uses LiteLLM for LLM inference. Add your LLM API key to .env. This project uses GPT-5 by default:

OPENAI_API_KEY=your_openai_api_key_here

For other providers, check the LiteLLM documentation.

</details>

---

Quick Example

from dbplanbench import optimize_queries

QUERY = "SELECT * FROM ... WHERE ... ORDER BY ..."

opt_result = optimize_queries(
    queries=[QUERY],
    dataset="tpcds",
    scale_factor=3,
    n_steps=2,
    n_samples_per_step=5,
    top_k_patches=1,
    n_runs=5,
)

single_plan = opt_result.optimization_outcome[0]
best_patch = single_plan.patch[0]
summary = opt_result.summary

Quick Start

See notebooks/examples.ipynb for a self-contained walkthrough that:

• Generates synthetic SQL queries with LLMs
• Optimizes their execution plans via evolutionary sampling
• Transfers optimizations across dataset scale factors
• Benchmarks at both scales and compares improvement_x side-by-side

Here's a condensed version:

from dbplanbench import (
    generate_queries,
    optimize_queries,
    scale_optimizations,
    benchmark_plans,
    benchmark_queries,
    get_engine_plans,
)
from dbplanbench_types import PatchedPlan

Python Environment

Python 3.10 or higher is required. Install dependencies with uv:

uv sync
source .venv/bin/activate

Environment Variables

Copy local.env to .env and fill in your values as you complete the setup steps below:

cp local.env .env

local.env lists all required environment variables with placeholder values. .env is gitignored.

</details>

modal_resources configures the Modal sandbox resources (CPU, memory, timeouts)