Query optimizer

tdfs4ds.query_optimizer analyses and rewrites Teradata SQL feature-engineering queries to improve EXPLAIN scores and reduce spool usage. It is process-aware: when you pass a process_id, it pulls the registered SQL and any stored EXPLAIN documentation directly from the catalog, avoiding redundant LLM calls.

9-step pipeline

Step	Action
0	Process context — SQL + stored EXPLAIN analysis fetched from the catalog (skipped when no process_id).
0.5	SQL simplification — structural compaction merges unnecessary nesting into CTE + single outer SELECT. The simplified form is accepted only when its EXPLAIN score ≥ original.
1	Structured EXPLAIN analysis — scores the simplified query 1–5 with [You]-prefixed author-actionable warnings.
2	Lineage graph — Primary Index + partition columns collected for every underlying object.
3	DDL fetch — SHOW TABLE / SHOW VIEW for every referenced object.
4	Candidate generation — LLM proposes up to QUERY_OPTIMIZER_MAX_CANDIDATES rewrites, each tagged with a strategy.
5	Candidate EXPLAIN — document_sql_query_explain per candidate.
6	Plan comparison — LLM picks the best plan by score delta and resolved warnings.
7	FilterManager check — partitioned-but-unfiltered objects are flagged for incremental processing.
8	Final report — Markdown with Score Summary, Simplification, 3-stage comparison (Input → Simplified → Optimized), Candidates, Selected Optimisation, FilterManager.

Quick start

import tdfs4ds

tdfs4ds.INSTRUCT_MODEL_PROVIDER = 'vllm'
tdfs4ds.INSTRUCT_MODEL_MODEL    = 'mistral-7b-instruct'
tdfs4ds.INSTRUCT_MODEL_API_KEY  = os.getenv('FS_API_KEY')

tdfs4ds.QUERY_OPTIMIZER_MAX_CANDIDATES       = 5
tdfs4ds.QUERY_OPTIMIZER_MAX_FAILURES         = 5
tdfs4ds.QUERY_OPTIMIZER_MAX_CANDIDATE_TOKENS = None

# Process-aware
result = tdfs4ds.query_optimizer(process_id='<UUID>', thread_id='session-1')

# Or pass raw SQL directly
result = tdfs4ds.query_optimizer(
    sql_query="SELECT ... FROM db.tbl",
    thread_id='session-2',
)

In a Jupyter notebook prefer the async entry point — it avoids the background-thread overhead from LangGraph's sync wrapper:

from tdfs4ds.agent import aquery_optimizer
result = await aquery_optimizer(process_id='<UUID>', thread_id='session-async')

Multi-turn follow-ups

Reuse the same thread_id to keep the SQL and report in context:

result = tdfs4ds.query_optimizer(
    "Would adding a Secondary Index on the join column help?",
    thread_id='session-1',
)

Result dict

Key	Content
answer	Structured Markdown report (Summary, Score Summary, Lineage, ...)
best_sql	Recommended SQL — equal to the original when no rewrite improves the score
score_delta	Before/after comparison of scores + execution metrics
original_analysis	explanation, user_score, global_score, n_steps, n_spool_objects, warnings, recommendations
candidates	List of {sql, strategy, rationale, analysis}
comparison	best_index, reasoning, business_logic_preserved
filtermanager_applicable	True when a FilterManager rewrite is recommended
process_info	Full process catalog record (when process_id is supplied)
steps	Every pipeline step with inputs and outputs

Score delta

sd = result['score_delta']
# {
#   'optimized':                True,
#   'best_strategy':            'partition_pruning',
#   'original_user_score':      2,   'best_user_score':      4,  'user_score_delta':   2.0,
#   'original_global_score':    3,   'best_global_score':    4,  'global_score_delta': 1.0,
#   'original_n_steps':         14,  'best_n_steps':         9,  'steps_delta':       -5,
#   'original_n_spools':        6,   'best_n_spools':        4,  'spools_delta':      -2,
#   'business_logic_preserved': True,
# }

Negative steps_delta and spools_delta mean the rewrite is lighter than the baseline. When the score improves but adds steps or spools (typically single-row CTEs backing a precomputed threshold), the report includes an explanatory note about the trade-off.

Standalone simplification

The simplification pass is callable on its own:

result = tdfs4ds.simplify_query(sql_query="SELECT ...")
# or
result = tdfs4ds.simplify_query(process_id='<UUID>')

# result keys: simplified_sql, original_sql, simplified (bool),
#              original_score, simplified_score
if result['simplified']:
    print(result['simplified_sql'])

Notebook display

from tdfs4ds.agent import display_optimization_result
display_optimization_result(result)

Renders a before/after table with colour-coded Δ Change cells (green = improvement, red = regression) followed by the full Markdown report.

FilterManager hand-off

When filtermanager_applicable is True, the report includes a ready-to-use snippet:

fm = tdfs4ds.FilterManager(
    schema_name = tdfs4ds.SCHEMA,
    view_name   = 'TRANSACTIONS',
    col_names   = ['transaction_date'],
)

for filter_id in range(fm.nb_filters):
    fm.update(filter_id)
    tdfs4ds.run(process_id)

See User guide / FilterManager for the full pattern.

Report files

Each run writes a Markdown report under:

{QUERY_OPTIMIZER_REPORT_DIR}/{DATA_DOMAIN}/tdfs4ds_optim_{view}_{process_id}.md

QUERY_OPTIMIZER_REPORT_DIR defaults to ./tdfs4ds_optim_reports/. The directory is created automatically.

---

Using the optimizer via the API server

When tdfs4ds is deployed with python -m tdfs4ds.serve, the query optimizer is exposed through two HTTP endpoints. Choose the one that fits your workflow:

POST /v1/optimize — structured JSON (programmatic)

Accepts raw SQL, returns a structured JSON object. No OpenAI framing needed.

curl

curl -s http://localhost:8000/v1/optimize \
  -H 'Content-Type: application/json' \
  -d '{
        "sql": "SELECT c.customer_id, SUM(s.amount) AS total FROM sales s JOIN customers c ON s.cust_id = c.id GROUP BY 1"
      }'

Response:

{
  "optimized_sql": "...",
  "optimization_strategy": "Replaced implicit PI join with explicit PI alignment ...",
  "token_consumption": {
    "prompt_tokens": 1842,
    "completion_tokens": 312,
    "total_tokens": 2154
  }
}

Python (requests)

import requests

result = requests.post(
    "http://localhost:8000/v1/optimize",
    json={"sql": "SELECT * FROM my_db.my_view WHERE ..."},
).json()

print(result["optimized_sql"])
print(result["optimization_strategy"])
print("tokens:", result["token_consumption"]["total_tokens"])

POST /v1/chat/completions — Markdown report (conversational)

Use the tdfs4ds-query-optimizer model for multi-turn interaction and full Markdown reports with scores, strategy details, and download links.

curl

curl -s http://localhost:8000/v1/chat/completions \
  -H 'Content-Type: application/json' \
  -d '{
        "model": "tdfs4ds-query-optimizer",
        "messages": [{"role": "user", "content": "Optimize: SELECT * FROM sales s JOIN customers c ON s.cust_id = c.id"}]
      }'

openai Python SDK

from openai import OpenAI

client = OpenAI(base_url="http://localhost:8000/v1", api_key="not-needed")

resp = client.chat.completions.create(
    model="tdfs4ds-query-optimizer",
    messages=[{"role": "user", "content": "Optimize: SELECT * FROM sales s JOIN customers c ON s.cust_id = c.id"}],
)
print(resp.choices[0].message.content)

# Score metadata is available in the usage block
print(resp.usage.model_extra.get("optimized"))     # True / False
print(resp.usage.model_extra.get("score_delta"))   # {original_user_score, best_user_score, ...}

LangChain (ChatOpenAI)

from langchain_openai import ChatOpenAI
from langchain_core.messages import HumanMessage

llm = ChatOpenAI(
    base_url="http://localhost:8000/v1",
    api_key="not-needed",
    model="tdfs4ds-query-optimizer",
)

sql = "SELECT * FROM sales s JOIN customers c ON s.cust_id = c.id WHERE c.region = 'EU'"
response = llm.invoke([HumanMessage(content=f"Optimize: {sql}")])
print(response.content)

See Deployment / API server for the full endpoint reference, including path-prefix, cloud deployments, and the comparison table between the two optimizer endpoints.