sql_exenv

sql_exenv is an advanced SQL execution environment specifically designed for Large Language Model (LLM) agents, enabling efficient and reliable PostgreSQL query optimization while validating correctness through innovative metamorphic testing techniques.

Features

• Query Correctness Validation: Leverages TLP and NoREC metamorphic testing to ensure SQL queries are semantically accurate and return the expected results.
• Performance Bottleneck Detection: Analyzes the execution plans using EXPLAIN to identify inefficient operations and reaction points within the SQL queries.
• Optimization Suggestions: Automatically recommends improvements, such as index creation and query rewrites, to enhance performance and execution speed.
• Autonomous Optimization Loops: Implements safety controls to run continuous optimization processes that refine queries autonomously, ensuring robustness and efficiency.

LLM-generated SQL queries can sometimes contain hidden semantic errors that lead to incorrect result sets. This tool addresses these issues promptly, significantly improving the reliability of LLM outputs.

Usage Examples

To utilize the provided capabilities, users can employ the command line interface as follows:

python cli.py  # Launch chat mode
python cli.py --query "SELECT * FROM users WHERE email='user5000@example.com'"
python cli.py --query "..." --validate-only  # Skip optimization
python cli.py --query "..." --no-validation  # Skip validation

Additionally, an autonomous agent can be executed via:

python run_agent.py

For those looking to integrate the features programmatically, the Python API allows for seamless interaction with the optimization agent:

from src.agent import SQLOptimizationAgent

agent = SQLOptimizationAgent()
result = await agent.optimize_query(
    sql="SELECT * FROM users WHERE email='user@example.com'",
    db_connection="postgresql://postgres:postgres@localhost:5432/demo",
)

print(result['success'], result['final_query'])

Underlying Mechanisms

The tool operates through several components:

• Analyzer: Decodes EXPLAIN JSON outputs to uncover high-cost operations and sequential scans, and to assess estimate errors.
• Semanticizer: Transforms analysis results into natural language insights, using Claude, to recommend actions like CREATE INDEX or alternative query rewrites.
• Agent: Utilizes a ReAct-style loop with Claude Sonnet to iteratively enhance queries until they reach optimal performance or the maximum iteration limit is reached.
• Safety Measures: Implements a two-phase EXPLAIN system to first estimate costs, running ANALYZE only if deemed safe, alongside statement timeouts and HypoPG for virtual index testing to ensure safe query execution.

This repository aims to streamline and validate the SQL query optimization process for LLMs, providing a comprehensive environment that fosters improved accuracy and performance.