# TiDB Execution Plan Evaluation System

This is a comprehensive evaluation framework for analyzing and comparing TiDB execution plans, supporting plan similarity calculation, performance analysis, cross-time-period/schema plan change monitoring, and hybrid data source comparisons (SQLite + TiDB).

## Architecture Overview

```
evaluation/
├── plan_types.py          # Plan data structure definitions
├── plan_parser.py         # Unified plan parser (supports EXPLAIN and system table formats)
├── plan_comparator.py     # Core plan comparison logic
├── plan_evaluator.py      # Generic evaluation framework
├── evaluate_common_sqls.py # Hybrid plan comparison evaluator (supports SQLite + TiDB)
├── config.py              # Configuration management (database, tables, queries, hybrid sources)
├── db_connector.py        # TiDB database connection utilities
├── sqlite_connector.py    # SQLite database connection utilities
└── example.env            # Example environment configuration
```

## Core Evaluation Formulas and Weights

### 1. Plan Similarity Calculation

#### 1.1 Operator Similarity Formula

**Main Formula:**
```
Operator Similarity = type_score × 0.25 + task_score × 0.05 + children_score × 0.4 + specific_score × 0.15 + estrow_score × 0.15
```

**Component Weights:**
- **Operator Type Match (type_score)**: 25%
- **Task Type Match (task_score)**: 5%
- **Children Similarity (children_score)**: 40%
- **Operator-Specific Attributes (specific_score)**: 15%
- **EstRow Similarity (estrow_score)**: 15%

**Implementation:** `plan_comparator.py` in `_calculate_operator_similarity` method

#### 1.2 EstRow Similarity Calculation

**Basic Formula (Node Level):**
```python
# If both EstRows are 0, similarity is 1.0
if est1 == 0 and est2 == 0:
    return 1.0

# If one is 0 and the other isn't, similarity is 0.0
if (est1 == 0) != (est2 == 0):
    return 0.0

# Ratio-based similarity calculation
if est1 > 0 and est2 > 0:
    ratio = min(est1, est2) / max(est1, est2)
    return ratio
```

**Implementation:** `plan_comparator.py` in `_calculate_est_row_similarity` method

#### 1.2.1 Weighted Plan-Level EstRow Similarity

**Overview:**
The weighted plan-level estRow similarity calculation considers the tree structure and gives higher weights to more important operators for a more accurate assessment of plan similarity from a performance perspective.

**Weighting System:**
- **Level-based**: Upper-level operators receive higher weight (0.7ˣ decay per level)
- **Operator-type**: Data source operators (2.0×), Join operators (1.5×), Selection (0.7×)
- **Row count**: Operators with larger estimated rows get higher weight (up to 2×)

**Main Formula:**
```python
def calculate_plan_estrow_similarity(self, plan1, plan2):
    # Remove projections for consistent comparison
    root1 = self._remove_projection(plan1.root)
    root2 = self._remove_projection(plan2.root)
    
    total_score, total_weight = self._calculate_weighted_estrow_similarity(root1, root2)
    return total_score / total_weight
```

**Weight Calculation:**
```python
def _get_operator_weight(self, op, level):
    # Base weight decreases with depth
    base_weight = 1.0 * (0.7 ** level)  # Each level reduces weight by 30%
    
    # Increase weight for important operator types
    if op_type in {OpType.TABLE_SCAN, OpType.INDEX_SCAN, OpType.POINT_GET}:
        type_multiplier = 2.0  # Data source operators are critical
    elif "JOIN" in str(op_type).upper():
        type_multiplier = 1.5  # Join operators are important
    elif op_type == OpType.SELECTION:
        type_multiplier = 0.7  # Selection operators often have less reliable estRows
    else:
        type_multiplier = 1.0
    
    # Consider row count impact
    est_rows = op.get_est_row()
    size_factor = min(1.0 + (est_rows / 1000), 2.0) if est_rows > 0 else 1.0
    
    return base_weight * type_multiplier * size_factor
```

**Implementation:** `plan_comparator.py` in `calculate_plan_estrow_similarity` method

#### 1.3 Children Similarity Calculation

**Handling Different Child Counts:**
```python
# Compare children at same positions + extra children get score 0
for i in range(min_children):
    score = calculate_operator_similarity(children1[i], children2[i])
    child_scores.append(score)

# Extra children get score 0
for i in range(min_children, max_children):
    child_scores.append(0.0)

children_score = sum(child_scores) / len(child_scores) if child_scores else 0.0
```

**Implementation:** `plan_comparator.py` in `_calculate_operator_similarity` method

### 2. Plan Complexity Scoring

#### 2.1 Complexity Calculation Formula

**Main Formula:**
```
Complexity = Node Count × (1 + Depth × 0.3)
```

**Parameters:**
- **Node Count Weight**: 1.0 (base weight)
- **Depth Weight**: 0.3 (depth impact coefficient on complexity)

**Implementation:** `plan_evaluator.py` in `_calculate_complexity_score` method

#### 2.2 Complexity Ratio Calculation

**Two Types of Ratio Calculations:**

1. **For Similarity Purposes (closer to 1 is better):**
   ```python
   complexity_similarity = min(p1_complexity, p2_complexity) / max(p1_complexity, p2_complexity)
   ```

2. **For Change Multiplier Purposes:**
   ```python
   complexity_ratio = p2_complexity / p1_complexity if p1_complexity > 0 else float('inf')
   ```

**Implementation:** `compare_common_sqls.py` in `create_plan_comparison_score` function

### 3. Comprehensive Plan Comparison Scoring

#### 3.1 Overall Similarity Formula

**Main Formula:**
```
Overall Similarity = structure_score × 0.6 + estrow_score × 0.3 + complexity_similarity × 0.1
```

**Weight Distribution:**
- **Structure Similarity**: 60% (primary factor)
- **EstRow Similarity**: 30% (performance prediction importance)
  - Now uses the weighted plan-level estRow similarity for improved accuracy
- **Complexity Similarity**: 10% (complexity impact)

**Implementation:** `compare_common_sqls.py` in `create_plan_comparison_score` function

### 4. Evaluation Metrics Classification

#### 4.1 Similarity Distribution Thresholds

**Classification Criteria:**
- **High Similarity**: > 0.8
- **Medium Similarity**: 0.4 ≤ score ≤ 0.8
- **Low Similarity**: < 0.4

**Implementation:** `plan_evaluator.py` in `_calculate_summary_metrics` method

#### 4.2 Performance Change Assessment

**Performance Multiplier Calculation:**
```python
latency_ratio = period2_avg_latency / period1_avg_latency
```

**Optimization Recommendation Triggers:**
- Focus on SQLs with performance differences exceeding 10x
- Update statistics when EstRow similarity is low
- Check index configuration when structural similarity is low

### 5. Special Handling Rules

#### 5.1 PointGet Optimization Handling

**Special Equivalence Rules:**
```python
# PointGet is always better than TableReader/IndexReader/IndexLookup
# Consider equivalent when accessing the same table
def _special_handle_point_get(self, op1: Operator, op2: Operator) -> bool:
    if not op_type_is_data_source(op1.get_type()) or op2.get_type() != OpType.POINT_GET:
        return False
    
    is_simple, table = self._extract_data_source_info(op1)
    if isinstance(op2, PointGetOp):
        if is_simple and self._same_table(op2.table, table):
            return True
```

**Implementation:** `plan_comparator.py` in `_special_handle_point_get` method

#### 5.2 Projection Operator Removal

**Removal Logic:**
```python
def _remove_projection(self, node: Optional[Operator]) -> Optional[Operator]:
    if not node:
        return None
    
    if node.get_type() == OpType.PROJECTION:
        children = node.get_children()
        # Directly return first child (assume it exists)
        return self._remove_projection(children[0]) if children else None
    
    # Recursively process children
    children = node.get_children()
    for i, child in enumerate(children):
        node.set_child(i, self._remove_projection(child))
    
    return node
```

**Implementation:** `plan_comparator.py` in `_remove_projection` method

### 6. Operator-Specific Similarity Calculation

#### 6.1 TableScan Operator

**Matching Rules:**
```python
if op_type == OpType.TABLE_SCAN:
    if isinstance(op1, TableScanOp) and isinstance(op2, TableScanOp):
        return 1.0 if self._same_table(op1.table, op2.table) else 0.0
```

#### 6.2 IndexScan Operator

**Matching Rules:**
```python
if op_type == OpType.INDEX_SCAN:
    if isinstance(op1, IndexScanOp) and isinstance(op2, IndexScanOp):
        table_match = self._same_table(op1.table, op2.table)
        index_match = op1.index == op2.index
        return 1.0 if table_match and index_match else (0.5 if table_match else 0.0)
```

**Scoring Rules:**
- Both table and index match: 1.0
- Only table matches: 0.5
- Neither matches: 0.0

**Implementation:** `plan_comparator.py` in `_calculate_operator_specific_similarity` method

### 7. Execution Count Correlation Analysis

#### 7.1 Pearson Correlation Coefficient Calculation

**Formula:**
```
r = Σ((xi - x̄)(yi - ȳ)) / √(Σ(xi - x̄)² × Σ(yi - ȳ)²)
```

**Parameter Description:**
- **xi, yi**: Execution counts of corresponding SQLs in two time periods
- **x̄, ȳ**: Mean of execution counts
- **r**: Correlation coefficient (-1 ≤ r ≤ 1)

**Implementation:** `compare_common_sqls.py` in `_calculate_pearson_correlation` method

#### 7.2 Correlation Strength Interpretation

**Classification Criteria:**
- **Strong Correlation**: |r| ≥ 0.8
- **Moderate Correlation**: 0.5 ≤ |r| < 0.8
- **Weak Correlation**: 0.3 ≤ |r| < 0.5
- **No Correlation**: |r| < 0.3

**Implementation:** `compare_common_sqls.py` in `_interpret_correlation` method

#### 7.3 Reliability Assessment

**Assessment Rules:**
```python
if exec_correlation > 0.7:
    # Workload patterns highly similar, comparison results have high reliability
elif exec_correlation > 0.3:
    # Workload patterns moderately similar, comparison results should be interpreted with caution
else:
    # Workload patterns differ significantly, comparison results have low reliability
```

**Use Cases:**
- Verify business pattern consistency between two time periods
- Assess reliability of plan comparison results
- Identify workload change impact on performance

### 8. Table Name Alias Handling

#### 8.1 Table Name Matching Rules

**Matching Logic:**
```python
def _same_table(self, table1: str, table2: str) -> bool:
    t1, t2 = table1.lower(), table2.lower()
    
    # Direct match
    if t1 == t2:
        return True
    
    # Alias match
    if self.table_aliases.get(t1) == t2:
        return True
    if self.table_aliases.get(t2) == t1:
        return True
    
    return False
```

#### 8.2 Alias Extraction

**Regular Expression:**
```python
pattern = r'\b(\w+)\s+(?:as\s+)?(\w+)\b'
matches = re.findall(pattern, sql.lower(), re.IGNORECASE)
```

**Implementation:** `plan_comparator.py` in `_extract_table_aliases` and `_same_table` methods

## Hybrid Data Source Support

### Overview

The evaluation system supports hybrid data source comparisons, enabling you to compare execution plans between SQLite databases and TiDB INFORMATION_SCHEMA tables. This is particularly useful for comparing historical captured data with current synthetic workload data.

### Data Flow Architecture

```
┌─────────────────────┐    ┌──────────────────────┐
│   Previous Period   │    │   Current Period     │
│                     │    │                      │
│  TiDB Production    │───▶│  SQLite Database     │
│  (Historical Data)  │    │  (simcluster.db)     │
└─────────────────────┘    └──────────────────────┘
                                       │
                                       ▼
                           ┌──────────────────────┐
                           │   Hybrid Evaluator   │
                           │                      │
                           │   SQLite ←→ TiDB     │
                           │ (Historical vs       │
                           │  Current Synthetic)  │
                           └──────────────────────┘
                                       │
                                       ▼
                           ┌──────────────────────┐
                           │  TiDB Current        │
                           │  (Synthetic Data)    │
                           │  INFORMATION_SCHEMA  │
                           └──────────────────────┘
```

### Key Features

- **Automatic Data Source Detection**: System automatically detects SQLite vs TiDB and applies appropriate query logic
- **Column Name Compatibility**: Handles differences between SQLite (lowercase) and TiDB (UPPERCASE) column names
- **Type Conversion**: Automatically converts between SQLite (float) and TiDB (Decimal) data types
- **Performance Optimization**: Uses separate queries for each data source to avoid cross-database JOIN performance issues
- **Unified Interface**: Same API works for both hybrid and single-source comparisons

### Use Cases

1. **Historical vs Current Comparison**: Compare captured historical workload data (SQLite) with current synthetic workload (TiDB)
2. **Cross-Environment Analysis**: Compare plans between different database environments
3. **Workload Evolution Tracking**: Monitor how execution plans change over time across different data storage systems
4. **Synthetic Workload Validation**: Verify synthetic workload fidelity against historical real workload data

## Usage Examples

### Basic Plan Comparison

```python
from evaluation import parse_text, compare_plans

# Parse plans
plan1 = parse_text(sql, plan_text1, "v4")
plan2 = parse_text(sql, plan_text2, "v4")

# Compare plans
result = compare_plans(plan1, plan2)
print(f"Similarity: {result.similarity_score:.3f}")
print(f"Same: {result.is_same}")
```

### Hybrid Workload Plan Comparison

```python
from evaluation.evaluate_common_sqls import compare_workloads, WorkloadPlanEvaluator
from evaluation.config import TargetConfig

# Option 1: Hybrid comparison (SQLite historical vs TiDB current) - RECOMMENDED
# Compare historical data (SQLite) vs current synthetic workload (TiDB)
result = compare_workloads(
    real_schema='test',               # Historical data schema in SQLite
    synthetic_schema='test',          # Current data schema in TiDB
    real_data_source='sqlite',        # Use SQLite for historical data (default)
    synthetic_data_source='tidb',     # Use TiDB for current data (default)
    sqlite_db_path='simcluster.db',   # Path to SQLite database (default)
    generate_report=True
)

# Option 2: Time-based comparison within same data source
result = compare_workloads(
    real_schema='test',
    synthetic_schema='test',
    real_time_range=('2025-07-15 14:46:00', '2025-07-15 15:07:00'),
    synthetic_time_range=('2025-07-15 15:38:00', '2025-07-15 16:39:00'),
    real_data_source='tidb',          # Both from TiDB
    synthetic_data_source='tidb',
    generate_report=True
)

# Option 3: Manual configuration for advanced hybrid use cases
historical_config = TargetConfig(
    schema='test',                    # Schema name in SQLite
    data_source_type='sqlite',        # Use SQLite data source
    sqlite_db_path='simcluster.db'    # Path to SQLite database
)

current_config = TargetConfig(
    schema='test',                    # Schema name in TiDB
    data_source_type='tidb',          # Use TiDB data source
    start_time='2025-07-15 15:38:00',
    end_time='2025-07-15 16:39:00'
)

evaluator = WorkloadPlanEvaluator(historical_config, current_config)
result = evaluator.analyze_sql_plan_differences(generate_report=True)
```

### Custom Configuration

```python
from evaluation.config import EvaluationConfig, TableConfig, DatabaseConfig, SQLiteConfig

# Create custom configuration with hybrid data sources
config = EvaluationConfig(
    database=DatabaseConfig.from_env(),  # TiDB connection from .env file
    sqlite=SQLiteConfig(db_path="custom_path/simcluster.db"),  # Custom SQLite path
    tables=TableConfig(
        statements_history_table="custom.my_statements_table"
    )
)

# Use custom configuration
evaluator = WorkloadPlanEvaluator(historical_config, current_config, config)
```

### Batch Plan Evaluation

```python
from evaluation.plan_evaluator import PlanEvaluator

evaluator = PlanEvaluator()
result = evaluator.evaluate_plan_sets(plan_set1, plan_set2)

print(f"Total comparisons: {result.metrics.total_comparisons}")
print(f"Average similarity: {result.metrics.avg_similarity:.3f}")
```

## Output Formats

### Console Output Example

```
=== Analyzing SQL 1/13: SELECT ol_i_id, ol_supply_w_id, ol_quantity... ===
  📊 Performance: 320442ns → 883768417ns (2758.0x)
  📈 Complexity: 4.8 → 6.4 (1.3x)
  ⭐ Plan similarity: Structure 0.38
  🔍 ❌ Plans are completely different
```

### Evaluation Metrics Structure

```python
@dataclass
class EvaluationMetrics:
    total_comparisons: int = 0
    identical_plans: int = 0
    different_plans: int = 0
    comparison_errors: int = 0
    
    avg_similarity: float = 0.0
    min_similarity: float = 0.0
    max_similarity: float = 0.0
    similarity_distribution: Dict[str, int] = field(default_factory=dict)
    
    avg_complexity_ratio: float = 0.0
    max_complexity_ratio: float = 0.0
```

## Table Configuration

### Environment-based Table Configuration

The system reads the statement history table name from environment configuration:

**Default Configuration Examples:**

| Environment | Table Configuration | Description |
|-------------|-------------------|-------------|
| Development | `INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY_HISTORY` | Standard TiDB system table |
| Production/Testing | `schema.table` | Custom backup table |

### Configuration Examples

```python
# Period comparison: both periods must be provided
period1_config = TimePeriodConfig(
    schema='test',
    start_time='2025-07-15 14:46:00',
    end_time='2025-07-15 15:07:00'
)

period2_config = TimePeriodConfig(
    schema='test_sim',
    start_time='2025-07-15 15:38:00',
    end_time='2025-07-15 16:39:00'
)

# Create comparison evaluator
evaluator = TimeBasedPlanEvaluator(period1_config, period2_config)

# Global analysis: both periods must be None
evaluator_global = TimeBasedPlanEvaluator(None, None)

# Custom table name override
period_config = TimePeriodConfig(
    schema='test',
    start_time='2025-07-15 14:46:00',
    end_time='2025-07-15 15:07:00',
    table_name='custom.statements_table'  # Override default table
)

# Note: Mixed configuration (one None, one provided) will raise ValueError
```

### Performance Optimizations

The system includes several performance optimizations:

1. **Single Query Loading**: All plan data and performance metrics are fetched in one optimized JOIN query
2. **Configurable Column Mappings**: Different workload types use appropriate column names
3. **SQL Template System**: All SQL queries are stored in config.py as templates
4. **Flexible Table Configuration**: Support for custom table names and schemas
5. **No Redundant Queries**: Eliminated separate plan loading queries

## Configuration and Extension

### Custom Weights

To adjust similarity calculation weights, modify the formula in `plan_comparator.py`:

```python
# Current weight configuration
return (type_score * 0.25 + task_score * 0.05 + children_score * 0.4 + 
        specific_score * 0.15 + estrow_score * 0.15)

# Custom weight example
return (type_score * 0.3 + task_score * 0.1 + children_score * 0.35 + 
        specific_score * 0.15 + estrow_score * 0.1)
```

### Adding New Operator Types

1. Define new operator types in `plan_types.py`
2. Add parsing rules in `plan_parser.py`
3. Add specific comparison logic in `plan_comparator.py`

## Database Configuration

### Environment Setup

The system supports both TiDB and SQLite databases. For TiDB connections, create a `.env` file based on `example.env`:

```bash
# Copy the example configuration
cp evaluation/example.env evaluation/.env

# Edit the configuration
vim evaluation/.env
```

**TiDB Configuration Parameters:**
```bash
DB_URL="127.0.0.1"          # TiDB server address
DB_PORT="4000"              # TiDB server port
DB_USERNAME="root"          # Database username
DB_PASSWORD=""              # Database password (leave empty if no password)
DEFAULT_DB="INFORMATION_SCHEMA"  # Default database schema
STATEMENTS_TABLE="INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY_HISTORY"  # Statement history table
```

**SQLite Configuration Parameters:**
```bash
SQLITE_DB_PATH="simcluster.db"  # Path to SQLite database file (optional)
```

### Hybrid Configuration Example

```python
from evaluation.config import EvaluationConfig, DatabaseConfig, SQLiteConfig

# Configure both data sources
config = EvaluationConfig(
    database=DatabaseConfig(
        host="127.0.0.1",
        port=4000,
        username="root",
        password="",
        default_db="INFORMATION_SCHEMA"
    ),
    sqlite=SQLiteConfig(
        db_path="path/to/simcluster.db"
    )
)
```

**Usage:**
- The system automatically loads TiDB settings from `.env` file using built-in parser
- SQLite databases are accessed directly via file path
- Environment variables take precedence over .env file settings
- No external dependencies required for .env file parsing
- Supports both single-source and hybrid data source comparisons

**Features:**
- Built-in .env file parser (no python-dotenv dependency needed)
- Supports comments in .env files (lines starting with #)
- Automatic quote removal from values
- Environment variable override support
- Automatic data source type detection
- Column name and data type compatibility handling

## Troubleshooting

### Common Issues

1. **Parse Failure**: Check if plan text format is correct
2. **Zero Similarity**: Check for operator type mismatches
3. **Poor Performance**: Consider reducing the number of plans to compare or use sampling
4. **SQLite Connection Error**: Verify that simcluster.db exists and is accessible
5. **No Common SQLs Found**: Check that both data sources contain data for the specified schema
6. **Type Conversion Errors**: System automatically handles SQLite (float) vs TiDB (Decimal) differences

### Debug Options

```python
# Enable verbose logging
import logging
logging.basicConfig(level=logging.DEBUG)

# Check parsing results
plan = parse_text(sql, plan_text, "v4")
print(f"Root node: {plan.root.get_type() if plan.root else 'None'}")

# Test hybrid data source connections
from evaluation.sqlite_connector import test_sqlite_connection
from evaluation.db_connector import test_env_connection

test_sqlite_connection("simcluster.db")  # Test SQLite
test_env_connection()                     # Test TiDB
```

## Report Generation

The system automatically generates detailed markdown reports containing:

- **Performance Overview**: Latency multipliers and plan similarities
- **Detailed Analysis**: SQL-by-SQL breakdown with plan trees
- **Statistical Summary**: Execution count correlations and distributions
- **Optimization Recommendations**: Based on analysis results

Reports are saved as `TIME_BASED_PLAN_COMPARISON_REPORT_[timestamp].md` in the evaluation directory.