Analisis Komparatif Model LLM Open-Source untuk Rekomendasi Indeks Otomatis pada Database

Wan, Muhafidz faldi (2026) Analisis Komparatif Model LLM Open-Source untuk Rekomendasi Indeks Otomatis pada Database. Masters thesis, Institut Teknologi Sepuluh Nopember.

This is the latest version of this item.

[thumbnail of 6025241045-Master_Thesis.pdf] Text
6025241045-Master_Thesis.pdf - Accepted Version
Restricted to Repository staff only
Download (4MB)

Abstract

Efisiensi eksekusi query merupakan faktor krusial dalam performa aplikasi modern, di mana pengindeksan basis data memegang peran sentral. Namun, proses optimasi indeks secara manual memerlukan keahlian khusus dan analisis mendalam terhadap skema basis data, sementara solusi otomatis komersial yang tersedia memiliki keterbatasan, seperti biaya lisensi yang tinggi. Large Language Models (LLM) menunjukkan potensi besar dalam memahami semantik SQL, tetapi penerapannya untuk rekomendasi indeks otomatis masih belum dieksplorasi secara sistematis. Penelitian ini bertujuan mengatasi kesenjangan tersebut melalui analisis komparatif terhadap beberapa model LLM open-source untuk tugas rekomendasi indeks pada basis data PostgreSQL. Menggunakan pendekatan eksperimental kuantitatif, penelitian ini mengevaluasi performa model pada dataset query dengan tiga tingkat kompleksitas (Simple, Moderate, Complex) yang berasal dari aplikasi. Efektivitas dua teknik prompting utama, yaitu Few-Shot Prompting dan Chain-of-Thought (CoT) Prompting, turut diinvestigasi. Arsitektur sistem diperkuat dengan Retrieval-Augmented Generation (RAG) untuk menyediakan metadata skema basis data operasional secara real-time guna mengurangi risiko halusinasi. Metrik evaluasi utama meliputi akurasi sintaksis (Parsing Success Rate), akurasi eksekusi (Execution Success Rate), dampak kuantitatif terhadap waktu eksekusi query, profil konsumsi sumber daya komputasi (CPU, RAM, GPU), serta latensi inferensi. Hasil eksperimen menunjukkan bahwa model berparameter besar seperti Gemma3:27b mencapai akurasi tertinggi (PSR 99%, ESR 93%), tetapi menghasilkan latensi inferensi yang tinggi. Sebaliknya, model kelas menengah seperti Mistral:7b teridentifikasi sebagai arsitektur paling optimal untuk implementasi near real-time. Secara keseluruhan, rekomendasi indeks dari LLM terbukti mampu memangkas waktu eksekusi secara signifikan, khususnya pada kueri kompleks (Level 3). Penelitian ini juga merumuskan taksonomi kesalahan, di mana column hallucination (21,28%) menjadi penyebab kegagalan utama akibat keterbatasan metrik statistik pada prompt. Kontribusi utama penelitian ini adalah evaluasi komprehensif dan dashboard interaktif yang telah tervalidasi secara fungsional oleh pakar (S-CVI 0,97), yang berfungsi sebagai alat decision support dalam optimasi basis data otomatis.
=================================================================================================================================
Query execution efficiency is a crucial factor in the performance of modern applications, where database indexing plays a central role. However, the manual index optimization process requires specialized expertise and in-depth analysis of the database schema, while existing commercial automated solutions are limited by factors such as high licensing costs. Large Language Models (LLMs) demonstrate significant potential in understanding SQL semantics, yet their application for automated index recommendation remains systematically unexplored. This study aims to address this gap by conducting a comparative analysis of several open-source LLMs for index recommendation tasks on PostgreSQL databases. Using a quantitative experimental approach, this research evaluates model performance on a query dataset with three levels of complexity (Simple, Moderate, and Complex) derived from applications. The effectiveness of two primary prompting techniques, namely Few-Shot Prompting and Chain-of-Thought (CoT) Prompting, is also investigated. The system architecture is enhanced with Retrieval-Augmented Generation (RAG) to provide real-time operational database schema metadata, thereby reducing the risk of hallucinations. The main evaluation metrics include syntactic accuracy (Parsing Success Rate), execution accuracy (Execution Success Rate), the quantitative impact on query execution time, computing resource consumption profiles (CPU, RAM, GPU), and inference latency. Experimental results show that large-parameter models such as Gemma3:27b achieved the highest accuracy (PSR 99%, ESR 93%) but exhibited high inference latency. In contrast, mid-scale models such as Mistral:7b were identified as the most suitable architecture for near real-time implementation. Overall, index recommendations generated by LLMs significantly reduced query execution time, particularly for complex queries (Level 3). This study also formulates an error taxonomy, identifying column hallucination (21.28%) as the primary cause of failure due to limitations in the statistical metrics provided within the prompt. The main contribution of this research is a comprehensive evaluation and an interactive dashboard that has been functionally validated by experts (S-CVI 0.97), serving as a decision support tool for automated database optimization.

Item Type: Thesis (Masters)
Uncontrolled Keywords: Large Language Models (LLM), Optimasi Query, Prompt Engineering, Rekomendasi Indeks Otomatis, Retrieval-Augmented Generation (RAG), Automatic Index Recommendation, Large Language Models (LLM), Prompt Engineering, Query Optimization, Retrieval-Augmented Generation (RAG)
Subjects: Q Science > QA Mathematics > QA336 Artificial Intelligence
Divisions: Faculty of Intelligent Electrical and Informatics Technology (ELECTICS) > Informatics Engineering > 55101-(S2) Master Thesis
Depositing User: Wan Muhafidz Faldi
Date Deposited: 27 Jul 2026 04:22
Last Modified: 27 Jul 2026 04:22
URI: http://repository.its.ac.id/id/eprint/137587

Available Versions of this Item

Actions (login required)

View Item View Item