Faldi, Wan Muhafidz (2026) Analisis Komparatif Model LLM Open-Source Untuk Rekomendasi Indeks Otomatis Pada Database. Masters thesis, Institut Teknologi Sepuluh Nopember.
![[thumbnail of 6025241045-Master_Thesis.pdf]](http://repository.its.ac.id/style/images/fileicons/text.png) |
Text
6025241045-Master_Thesis.pdf - Accepted Version Restricted to Repository staff only Download (4MB) | Request a copy |
Abstract
Efisiensi eksekusi query merupakan faktor krusial untuk performa aplikasi modern, di mana pengindeksan basis data memegang peran sentral. Namun, proses optimasi indeks secara manual memerlukan keahlian khusus dan analisis mendalam terhadap schema database, sementara solusi otomatis komersial yang ada 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 diharapkan dapat mengatasi kesenjangan tersebut dengan melakukan 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) dari aplikasi. Efektivitas dua teknik prompting utama diinvestigasi, yaitu Few-Shot Prompting dan Chain-of-Thought (CoT) Prompting. 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 mencakup akurasi sintaksis (Parsing Success Rate), akurasi eksekusi (Execution Success Rate), dampak kuantitatif terhadap waktu eksekusi query, profil konsumsi sumber daya komputasi (CPU, RAM, GPU), dan latensi inferensi Hasil eksperimen menunjukkan bahwa model berparameter besar seperti Gemma3:27b mencapai akurasi tertinggi (PSR 99%, ESR 93%), namun 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 dari penelitian ini adalah sebuah 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 for 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 have limitations such as high licensing costs. Large Language Models (LLMs) show great potential in understanding SQL semantics, but their application for automated index recommendation remains systematically unexplored. This research is expected to address this gap by conducting a comparative analysis of several open source LLM models for index recommendation tasks on PostgreSQL databases. Using a quantitative experimental approach, this study evaluates model performance on a query dataset with three levels of complexity (Simple, Moderate, Complex) derived from applications. The effectiveness of two primary prompting techniques is investigated, namely Few-Shot Prompting and Chain-of-Thought (CoT) Prompting. The system architecture is enhanced with Retrieval-Augmented Generation (RAG) to provide real-time operational database schema metadata to reduce the risk of hallucinations. Key evaluation metrics include syntactic accuracy (Parsing Success Rate), execution accuracy (Execution Success Rate), quantitative impact on query execution time, computing resource consumption profiles (CPU, RAM, GPU), and inference latency. Experimental results show that large-parameter models like Gemma3:27b achieved the highest accuracy (PSR 99%, ESR 93%) but generated high inference latency. In contrast, mid-scale models such as Mistral:7b were identified as the most optimal architecture for near real-time implementation. Overall, index recommendations from LLMs proved capable of significantly reducing execution time, particularly on complex queries (Level 3). This research also formulates an error taxonomy, where column hallucination (21.28%) became the primary cause of failure due to the limitation of statistical metrics in 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 in 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 01:46 |
| Last Modified: | 27 Jul 2026 01:46 |
| URI: | http://repository.its.ac.id/id/eprint/137535 |
Available Versions of this Item
- Analisis Komparatif Model LLM Open-Source Untuk Rekomendasi Indeks Otomatis Pada Database. (deposited 27 Jul 2026 01:46) [Currently Displayed]
Actions (login required)
 |
View Item |