Implementation:MaterializeInc Materialize Optbench SQL Module

Knowledge Sources	MaterializeInc_Materialize
Domains	Benchmarking, Query Optimization, SQL
Last Updated	2026-02-08 00:00 GMT

Overview

The Optbench SQL Module provides the core query manipulation, EXPLAIN execution, and database interaction classes for the optimizer benchmarking framework.

Description

This module contains three primary classes for optimizer benchmarking: Query wraps SQL query strings and provides methods for extracting query names from comments and generating EXPLAIN statements for both Materialize and PostgreSQL dialects; ExplainOutput parses EXPLAIN results to extract optimization timing data using regex matching; and Database provides a connection API to the target database under test. The Dialect enum distinguishes between PostgreSQL (EXPLAIN ANALYZE) and Materialize (EXPLAIN WITH(timing)) syntax. The companion util.py module provides duration_to_timedelta() for parsing timing strings and results_path() for generating versioned CSV output paths.

Usage

Use this module as the core SQL execution layer within the optbench benchmarking framework. It is consumed by the cli/optbench.py CLI tool to run and compare optimization benchmarks.

Code Reference

Source Location

Repository: MaterializeInc_Materialize
File: misc/python/materialize/optbench/sql.py
File: misc/python/materialize/optbench/util.py

Signature

# sql.py
class Dialect(Enum):
    PG = 0
    MZ = 1

class Query:
    def __init__(self, query: str) -> None: ...
    def name(self) -> str: ...
    def explain(self, timing: bool, dialect: Dialect = Dialect.MZ) -> str: ...

class ExplainOutput:
    def __init__(self, output: str) -> None: ...
    def optimization_time(self) -> np.timedelta64 | None: ...

class Database:
    def __init__(self, port: int, ...): ...

# util.py
def duration_to_timedelta(duration: str) -> np.timedelta64 | None: ...
def results_path(repository: Path, scenario: Scenario, version: str) -> Path: ...

Import

from materialize.optbench.sql import Query, ExplainOutput, Database, Dialect
from materialize.optbench.util import duration_to_timedelta, results_path

I/O Contract

Input	Type	Description
query	`str`	SQL query string, optionally with `-- name: {name}` comment
timing	`bool`	Whether to include timing in EXPLAIN output
dialect	`Dialect`	Target SQL dialect (MZ or PG)
duration	`str`	Duration string like "3.2ms", "150us", "1.5s"

Output	Type	Description
explain	`str`	EXPLAIN-prefixed query string for the target dialect
optimization_time	None	Parsed optimization time from EXPLAIN output
results_path	`Path`	Versioned CSV output path like `optbench-tpch-mz-v0.79.0-abc123.csv`

Usage Examples

from materialize.optbench.sql import Query, ExplainOutput, Dialect

# Create and manipulate a query
q = Query("-- name: tpch_q1\nSELECT * FROM lineitem WHERE l_quantity > 10")
print(q.name())  # "tpch_q1"

# Generate EXPLAIN statement for Materialize
explain_sql = q.explain(timing=True, dialect=Dialect.MZ)
# "EXPLAIN WITH(timing)\n-- name: tpch_q1\nSELECT * FROM lineitem WHERE l_quantity > 10"

# Parse EXPLAIN output
output = ExplainOutput("Optimization time: 3.2ms\n...")
opt_time = output.optimization_time()  # np.timedelta64(3200000, 'ns')

Related Pages

Page Connections

Double-click a node to navigate. Hold to expand connections.

Principle

Implementation

Heuristic

Environment