LLMIdxAdvis: Resource-Efficient Index Advisor Utilizing Large Language Model

2025-03-10

AI Summary

Existing index recommendation methods suffer from high inference latency, substantial training resource overhead, and poor generalization across diverse workloads and database schemas. To address these challenges, this paper proposes a fine-tuning-free, lightweight large language model (LLM)-driven approach. We formulate index recommendation as a sequence-to-sequence generation task, enabling end-to-end inference that jointly incorporates workload characteristics, storage constraints, and database environment context. We introduce a novel dual-path inference scaling mechanism: vertical scaling via Index-Guided Majority Voting and Best-of-N sampling, and horizontal scaling through database-feedback-driven iterative self-refinement. Additionally, we construct a high-quality offline demonstration pool using GPT-4-Turbo–synthesized SQL queries with heuristic labeling. Evaluated on three OLAP benchmarks and two real-world workloads, our method achieves state-of-the-art recommendation quality, significantly reduces inference latency, and demonstrates strong cross-workload and cross-schema generalization capability.

Technology Category

• Search and Optimization: Distributed Search
• Data Mining & Knowledge Management: Conversational Systems for Recommendation & Retrieval
• Planning, Routing, and Scheduling: Planning with Language Models

Application Category

• User Modeling, Personalization and Recommendation: Large Language Models (LLM) for user modeling and recommendation
• Graph Algorithms and Modeling for the Web: Querying, indexing, and retrieval in Web-related graphs
• Search and Retrieval-Augmented AI: Web crawling and indexing

Abstract

Index recommendation is essential for improving query performance in database management systems (DBMSs) through creating an optimal set of indexes under specific constraints. Traditional methods, such as heuristic and learning-based approaches, are effective but face challenges like lengthy recommendation time, resource-intensive training, and poor generalization across different workloads and database schemas. To address these issues, we propose LLMIdxAdvis, a resource-efficient index advisor that uses large language models (LLMs) without extensive fine-tuning. LLMIdxAdvis frames index recommendation as a sequence-to-sequence task, taking target workload, storage constraint, and corresponding database environment as input, and directly outputting recommended indexes. It constructs a high-quality demonstration pool offline, using GPT-4-Turbo to synthesize diverse SQL queries and applying integrated heuristic methods to collect both default and refined labels. During recommendation, these demonstrations are ranked to inject database expertise via in-context learning. Additionally, LLMIdxAdvis extracts workload features involving specific column statistical information to strengthen LLM's understanding, and introduces a novel inference scaling strategy combining vertical scaling (via ''Index-Guided Major Voting'' and Best-of-N) and horizontal scaling (through iterative ''self-optimization'' with database feedback) to enhance reliability. Experiments on 3 OLAP and 2 real-world benchmarks reveal that LLMIdxAdvis delivers competitive index recommendation with reduced runtime, and generalizes effectively across different workloads and database schemas.

Problem

• Improves query performance via optimal index recommendations.
• Addresses challenges of traditional index recommendation methods.
• Utilizes LLMs for efficient, generalizable index advising.

Innovation

• Uses large language models for index recommendation
• Constructs high-quality demonstration pool offline
• Implements novel inference scaling strategy