Skip to content

CLAUDE.md

Latest commit

 

History

History
89 lines (55 loc) · 4.69 KB

CLAUDE.md

File metadata and controls

89 lines (55 loc) · 4.69 KB
Raw

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

This is a synthesized bespoke cardinality estimator research project. The system uses LLM agents to automatically synthesize a custom cardinality estimator for the IMDB Join Order Benchmark (JOB) workload — one planning agent analyzes the database schema and designs a statistics strategy, and a coding agent implements the estimator in card_estimator.py. The estimator is iteratively refined and evaluated against PostgreSQL's built-in estimator.

Running

# Install dependencies (uses uv)
uv sync

# Run the full agent pipeline (plan → code → evaluate → optimize)
uv run python main.py

# Run only the agent loop (skips subplan generation)
uv run python agents.py

Environment Variables

A .env file is required in the project root. Required variables:

  • OPENAI_API_KEY — for GPT models (used directly via OpenAI Responses API)
  • ANTHROPIC_API_KEY — for Anthropic models (used via LiteLLM)
  • VENV_PATH — absolute path to the Python executable in the venv (used when spawning evaluate.py as a subprocess)
  • TABLES_PATH — path to the directory containing IMDB CSV files (one per table)
  • end2end_timeout — timeout in seconds for end-to-end query execution

Optional:

  • LLM_API_BASE / OPENAI_API_BASE / LITELLM_API_BASE — for local or custom LLM endpoints

Architecture

Agent Pipeline (agents.py / main.py)

The coding_loop() function in agents.py orchestrates the full workflow:

  1. Planning Agent — queries the DuckDB database to analyze data distributions, then outputs a statistics design plan (outputs/statistics_plan.txt)
  2. Coding Agent — implements the plan as card_estimator.py, then iteratively refines it across phases: initial setup → join accuracy → filter accuracy → combined final
  3. Best-run selector — the planner reviews all iteration feedback and selects the best implementation

Both agents are instances of OpenAIAgentsSDKWrapper, which wraps the OpenAI Agents SDK (openai-agents).

Model Routing (utils/model_setup.py)

  • Model names starting with gpt- use the native OpenAI Responses API (CachedOpenAIResponsesModel)
  • All other models (e.g. anthropic/claude-sonnet-4-6) use LiteLLM (CachedLitellmModel)
  • Both LLM wrappers support disk caching of responses in cache/

Agent Tools

The coding agent has: apply_patch (edit card_estimator.py), shell (read-only: ls/cat/sed/head/tail/wc), evaluate (runs evaluate.py as a subprocess), ask_agent (escalate to the planning agent).

The planning agent has only: query_db (read-only DuckDB queries against data/imdb.duckdb).

Cardinality Estimator Interface

Every estimator (synthesized or hand-crafted) subclasses base_card_estimator (defined identically in utils/base_card_estimator.py and synthesized_card_estimators/interface.py) and implements:

  • setup(self) — load CSV data and compute statistics
  • estimate(self, tables: list, filters: list, joins: list) -> int — return estimated cardinality

The custom_csv_reader helper on the base class reads IMDB CSV files from TABLES_PATH, applying a workaround for escaped commas.

Evaluation Flow

evaluate.py (not tracked in git — generated at runtime or provided externally) calls annotate_subplans_with_bespoke_cards() and computes q-error metrics written to outputs/feedback.json. The agent reads this file to decide next steps.

Subplans are pre-generated by utils/subplan_generation.py from data/orig_queries.sql and stored in outputs/job_subplans.json with true cardinalities (via DuckDB) and Postgres estimates annotated.

Data Files (data/)

  • schema.json / schema.sql — IMDB schema
  • orig_queries.sql — original JOB queries; all_queries.sql — augmented query set
  • row_counts.json, unique_vals.json — precomputed statistics fed into the planner prompt
  • imdb.duckdb — read-only DuckDB database (not in repo; must be provisioned)

LLM Cache (agents_sdk/llm/)

CachedOpenAIResponsesModel and CachedLitellmModel serialize LLM requests/responses to disk under cache/. The stop_on_cache_miss flag lets you run deterministically against a pre-populated cache for reproducibility.

Synthesized Estimators (synthesized_card_estimators/)

Contains hand-crafted reference estimators (job.py, job_complex.py) implementing the same interface, useful for benchmarking. They use sampling, top-k frequency dictionaries, and join-graph logic.

Logging and Archiving

Archiver logs each agent interaction to .logs/<timestamp>/ as markdown files, and saves a plot of q-error across optimization iterations.