Llmidxadvis Eval

---
name: llmidxadvis-eval
description: Evaluates the effectiveness and efficiency of an LLM-based index recommendation system in selecting database indexes for given SQL workloads under varying storage constraints and schema generalization settings. Use when the user wants to benchmark on TPC-H, JOB, TPC-DS, SSAG, AMPS, or asks about evaluating this task. Reports Relative Workload Cost Reduction.
metadata:
  skill_kind: dataset_eval
  source_arxiv: 2503.07884
  bibtex_key: zhao2025llmidxadvis
  confidence: high
---

# llmidxadvis-eval

> LLMIdxAdvis: Resource-Efficient Index Advisor Utilizing Large Language Model — Zhao et al. (2025) (arXiv:2503.07884, 2025)

## What this evaluates

Evaluates the effectiveness and efficiency of an LLM-based index recommendation system in selecting database indexes for given SQL workloads under varying storage constraints and schema generalization settings.

## Datasets

- **TPC-H** — total ?; splits: test (-1)
- **JOB** — total ?; splits: test (113)
- **TPC-DS** — total ?; splits: test (-1)
- **SSAG** — total ?; splits: test (-1)
- **AMPS** — total ?; splits: test (-1)

## Metrics

- `Relative Workload Cost Reduction` **(primary)** — range: percent
  - The proportion of reduction in the workload's estimated cost after virtually creating the recommended indexes, calculated as (cost_without_indexes - cost_with_indexes) / cost_without_indexes. Measured via the EXPLAIN command using a what-if caller.
- `Algorithm Runtime` — range: other
  - The wall-clock execution time required for the algorithm to generate the index recommendation result.
- `Relative Workload Latency Reduction` — range: percent
  - The proportion of reduction in the actual workload execution latency after physically creating the recommended indexes, calculated as (latency_without_indexes - latency_with_indexes) / latency_without_indexes.

## Input / output format

**Input**: Target SQL workload queries, database schema and column statistics, storage constraint percentage, and in-context demonstrations (SQL queries with corresponding index labels).

**Output**: A set of recommended database indexes (table and column names, index type) that must satisfy the specified storage constraint.

## Scoring recipe

```python
def score(predictions, workload, gold_indexes=None):
    # 1. Cost Reduction (Estimated)
    cost_no_idx = execute_explain(workload, indexes=[])
    cost_with_idx = execute_explain(workload, indexes=predictions)
    cost_reduction = (cost_no_idx - cost_with_idx) / cost_no_idx

    # 2. Latency Reduction (Actual)
    latency_no_idx = run_actual_workload(workload, indexes=[])
    latency_with_idx = run_actual_workload(workload, indexes=predictions)
    latency_reduction = (latency_no_idx - latency_with_idx) / latency_no_idx

    # 3. Runtime
    runtime = time_taken_to_generate(predictions)
    return cost_reduction, latency_reduction, runtime
```

## Common pitfalls

- Relying solely on estimated cost (what-if) metrics can mislead performance claims, as heuristic methods often overfit to cost estimators while actual latency may differ significantly.
- Failing to filter out queries from standard benchmarks in the demonstration pool can cause data leakage, since LLMs may have encountered these queries during pre-training.
- Ignoring the storage constraint when comparing index sets, as index size directly impacts recommendation feasibility and fairness across methods.

## Evidence (verbatim from paper)

> We evaluate the index advisors mainly from three aspects as follows. (1) Relative Workload Cost Reduction defines as the proportion of reduction in the workload estimated cost after virtually creating the recommended indexes, which can be obtained in the query plan through executing the “EXPLAIN” command. A higher value signifies a better performance improvement. (2) Algorithm Runtime is the execution time of the algorithms to generate the index recommendation result, where lower value indicates better efficiency. (3) Relative Workload Latency Reduction defines as the proportion of reduction in the workload latency after creating the recommended indexes, which requires making actual modifications to the database to obtain accurate execution metrics. A higher value denotes a better performance improvement.

## Citation

```bibtex
@misc{zhao2025llmidxadvis,
  title={LLMIdxAdvis: Resource-Efficient Index Advisor Utilizing Large Language Model},
  author={Zhao et al. (2025)},
  year={2025},
  note={arXiv:2503.07884}
}
```

- arXiv: 2503.07884