OptBench: An Interactive Workbench for AI/ML-SQL Co-Optimization[Extended Demonstration Proposal]

This paper presents OptBench, an interactive workbench built on DuckDB that enables researchers and practitioners to transparently build, benchmark, and visually compare query optimizers for hybrid SQL and AI/ML workloads through a unified platform.

The Gist

Imagine you are running a massive, high-speed kitchen (a database) where chefs (SQL queries) prepare meals by chopping vegetables and boiling water. But recently, the restaurant has started adding a new, mysterious ingredient: AI and Machine Learning.

Suddenly, the chefs aren't just chopping; they are asking a "black box" robot to taste the soup, predict the weather, or identify a face in a photo before serving it. The problem? The robot is slow, expensive, and the chefs don't know how to talk to it efficiently. Sometimes, the robot tastes the soup before the vegetables are even chopped, wasting hours of work.

OptBench is the new, interactive kitchen simulator built by researchers at Arizona State University to help chefs and robot engineers figure out the best way to cook these complex "SQL + AI" meals together.

Here is a breakdown of how it works, using simple analogies:

1. The Problem: The "Black Box" Robot

In the old days, database optimizers were like experienced head chefs who knew exactly how long it took to chop an onion or boil pasta. They could rearrange the cooking steps to save time.

But when you add AI (the robot), the chef loses control. The robot is a "black box"—you feed it data, and it gives you an answer, but the chef doesn't know how long it will take or if the robot could have worked faster if the ingredients were pre-sorted.

• The Result: The kitchen runs slowly because the robot is doing unnecessary work, or the chef is waiting for the robot when they could have been doing other things.

2. The Solution: The OptBench Workbench

OptBench is a virtual training kitchen (built on top of a modern, flexible engine called DuckDB) where researchers and developers can experiment with how to best combine human chefs and AI robots.

Think of it as a Lego set for database optimization. Instead of building a whole new kitchen from scratch every time you want to test a new idea, you use a standard set of bricks (the OptBench tools) to build different strategies.

3. The Three Magic Tools in the Kit

A. The "Rewrite Action" Library (The Magic Spells)

Imagine you have a spellbook. Each spell changes the recipe slightly to make it faster, without changing the final taste of the meal.

• The "Sparse" Spell: If the robot only needs to look at 1 out of 100 ingredients, this spell tells the robot, "Ignore the empty plates!" (Switching from a dense to a sparse calculation).
• The "Pushdown" Spell: This tells the robot, "Don't wait until the whole pot is full to taste it. Taste the ingredients before you mix them." (Moving the AI calculation earlier in the process to filter out bad data sooner).
• The "Fusion" Spell: Instead of asking the robot to taste, then blend, then taste again, this spell combines all three steps into one super-fast "Taste-Blend-Taste" machine.

B. The "Statistics" Window (The Crystal Ball)

Before the chefs start cooking, OptBench gives them a crystal ball that predicts:

• How many ingredients are we dealing with?
• How "empty" (sparse) are our data sets?
• How long will the robot take to think?
  This helps the chef decide which "Magic Spell" to use.

C. The "Side-by-Side" Showdown (The Race Track)

This is the coolest part. OptBench lets you run two different cooking strategies on the exact same ingredients at the exact same time.

• Chef A uses the "Old Way" (no optimization).
• Chef B uses the "New AI Strategy" (pushing the robot earlier, using sparse spells).
  The screen shows you the logical plan (the recipe steps) side-by-side and tells you exactly how many seconds Chef B saved. It's like a drag race where you can see exactly which engine part made the difference.

4. Who Uses This?

• The Researcher: Wants to invent a new "Magic Spell" to make AI faster. They can test it immediately without building a whole new database system.
• The Data Scientist: Has a slow AI pipeline and wants to know why. They can use OptBench to see if the robot is waiting too long or if the data is being sorted poorly.
• The Student: Can learn how to optimize complex queries by playing with the "drag-and-drop" rules in the web interface.

5. Why Is This a Big Deal?

Previously, if you wanted to test a new way to optimize AI in databases, you had to build your own custom database, write your own code, and hope it worked. It was like trying to compare two different cars by building them from scratch in your garage.

OptBench puts everyone on the same track, with the same fuel, and the same stopwatch. It allows the world to fairly compare different optimization ideas, ensuring that the "winner" is actually the best strategy, not just the one built on the fastest computer.

In short: OptBench is the ultimate playground where we teach databases how to talk to AI robots efficiently, so we can get our answers faster without wasting energy or time.

Technical Summary

Here is a detailed technical summary of the paper "OptBench: An Interactive Workbench for AI/ML-SQL Co-Optimization".

1. Problem Statement

The integration of Artificial Intelligence (AI) and Machine Learning (ML) pipelines into relational database systems has created a new class of hybrid "SQL+AI/ML" queries. These queries interleave standard relational operators (joins, filters, aggregations) with expensive, opaque ML operators (e.g., neural networks, decision trees, vector transformations), often implemented as User-Defined Functions (UDFs).

Optimizing these workloads presents three critical challenges:

• Black-Box Nature of ML Operators: Traditional optimizers struggle with ML operators because they lack visibility into data-dependent effects (e.g., sparsity, selectivity, cardinality) and cannot easily apply heuristics like "filter before inference" or "switch to sparse kernels."
• Lack of Fair Comparison: Research prototypes for SQL+ML optimization are typically built on different database backends (e.g., PostgreSQL, SQL Server, Oracle, custom engines) with different query representations and evaluation pipelines. This makes "apples-to-apples" comparison of optimization strategies impossible.
• Opaque Optimization Process: Existing systems often treat optimization as a black box, providing only final runtimes without visibility into decision traces, rule firing, or how specific data statistics influenced plan selection.

2. Methodology: The OptBench System

OptBench is an interactive, web-based workbench designed to build, benchmark, and visualize SQL+ML query optimizers in a transparent, unified environment. It is built on top of DuckDB, chosen for its hackable optimizer, strong UDF support, and in-process execution model.

The system architecture consists of five core components:

A. Extensible ML Function Library

OptBench encapsulates common ML operators as DuckDB C++ UDFs. These include:

• Linear Algebra: Matrix multiplication, addition, convolution.
• Neural Networks: Activation functions (sigmoid, softmax), fused DNN operators.
• Tree Models: Decision trees, random forests, gradient boosting.
• Preprocessing: Normalization, one-hot encoding, distance calculations.
  These functions allow users to compose complex inference workflows directly within SQL.

B. Extensible Rewrite Actions

The core of the optimization logic is a library of Rewrite Actions. These are composable transformations that convert a logical query plan into an equivalent but more efficient plan. Key actions include:

• Kernel Selection: Switching between dense and sparse matrix multiplication based on input sparsity metrics.
• Relationalization: Converting ML UDFs (e.g., dot products, decision forests) into standard relational operators (joins + aggregates) to enable classic optimizations like pushdown and reordering.
• Fusion: Merging chains of scalar UDFs (e.g., multi-layer NN) into a single fused operator (e.g., Torch NN) to reduce interpretation overhead.
• Decomposition & Pushdown: Breaking down compound ML expressions and pushing them down past joins/projections to reduce the number of rows processed by expensive inference.
• Pruning: Using query constraints to eliminate unreachable branches in tree-based models.

C. Extensible Statistics Estimation

To drive optimization decisions, OptBench computes and caches specific statistics:

• Relational Stats: Cardinality, selectivity (via DuckDB's native estimator).
• ML-Specific Stats: Sparsity ratios (non-zero elements), zero-row/col fractions, model FLOPS, parameter counts, and tree depth.
• Sampling: It uses lightweight sampling queries to estimate these metrics without executing the full inference pipeline.

D. Diverse Query Suite

OptBench includes a benchmark suite of 10 SQL+ML queries derived from three sources:

1. Raven: Real-world datasets (Expedia, Flights, Credit Card fraud) focusing on join-heavy workloads.
2. TPCx-AI: Vendor-neutral business tasks (clustering, forecasting, spam detection, fraud scoring).
3. IDNet: Image-centric pipelines involving CNNs and LLMs for fraud detection.

E. Web-Based Interface

The system provides a UI with eight panels allowing users to:

• Select queries and view side-by-side logical plan visualizations.
• Inspect computed statistics and available rewrite actions.
• Define optimizers using a drag-and-drop rule builder (mapping statistics to actions).
• Upload custom optimizer profiles or action definitions.
• Visualize benchmark results (latency, plan structure) across the query suite.

3. Key Contributions

1. Unified Optimization Workbench: OptBench is the first system to provide a transparent, unified environment for developing and comparing SQL+ML optimizers, eliminating the "different backend" bias in research.
2. Extensible Rewrite Framework: It introduces a modular library of rewrite actions that allows researchers to easily prototype new co-optimization strategies (e.g., factorization, pushdown, fusion) without rewriting the entire optimizer engine.
3. Interactive Benchmarking & Visualization: The system enables side-by-side comparison of plan structures and decision traces, allowing users to attribute performance gains to specific optimization rules.
4. Rapid Prototyping: Through its web interface and C++/Python integration, OptBench allows users to develop, test, and benchmark a new rule-based optimizer in minutes (demonstrated as ~4 minutes for a simple rule).

4. Results and Demonstration Scenarios

The paper outlines three primary demonstration scenarios:

• Scenario 1 (Optimizer Development): A user defines a rule-based optimizer (e.g., "If join cardinality > X and sparsity > Y, then push ML below join and switch to sparse kernels"). The system compiles this into an optimizer profile, executes it, and shows a decision trace.
  • Result: In a demo case, applying such rules reduced end-to-end latency from 85 seconds to 1.976 seconds by pushing inference below joins and utilizing sparse kernels.
• Scenario 2 (Performance Comparison): Users select two optimizers (e.g., DuckDB default vs. a custom SQL-ML optimizer) and run them on the same query suite. The system visualizes the structural differences in the logical plans and quantifies the latency improvements.
• Scenario 3 (Extensibility): Users can upload external, complex optimizers (e.g., cost-based DP optimizers written in C++) and compare them against built-in rule-based optimizers within the same framework.

5. Significance

OptBench addresses a critical gap in the database research community by solving the reproducibility and comparability crisis in SQL+ML optimization.

• For Researchers: It provides a standardized platform to validate new optimization theories against a diverse set of workloads without the overhead of building a full database engine.
• For Practitioners: It offers a tool to debug performance issues in in-database ML pipelines, visualize why an optimizer made a specific decision, and experiment with custom optimization strategies.
• For the Field: By standardizing the evaluation of "SQL+AI/ML" co-optimization, it accelerates the development of more efficient hybrid database systems capable of handling modern AI workloads.

The system is currently a prototype running on DuckDB but is designed to be extensible to other database systems in the future.