Rekomendasi optimasi query dari LLM sering kali menghasilkan saran indeks yang tidak efektif, redundan, atau bahkan kontraproduktif di lingkungan produksi. Pola EXPLAIN Enforced menyelesaikan masalah ini dengan memperlakukan saran AI bukan sebagai solusi final, melainkan hipotesis yang wajib diverifikasi secara deterministik melalui sandbox database.

Arsitektur Pola EXPLAIN Enforced

Pola ini mengharuskan sistem perantara (harness) mengeksekusi rencana eksekusi database sebelum dan sesudah indeks diterapkan. Tiga metrik utama yang wajib dievaluasi adalah:

  • Execution Time: Penurunan total durasi eksekusi query.
  • Shared Hit/Read Blocks: Total halaman memori atau disk buffer yang diakses. Penurunan buffer hit menunjukkan pengurangan beban I/O yang nyata.
  • Scan Type Mutation: Perubahan node dari Seq Scan menjadi Index Scan atau Index Only Scan pada tabel target yang memiliki filter/kondisi join.

Pipeline menolak otomatis rekomendasi AI jika rasio penurunan buffer tidak mencapai ambang batas minimum atau jika indeks yang disarankan tidak pernah disentuh oleh query planner.

Implementasi Verifikasi Harness dengan Python

Skrip berikut mengevaluasi query menggunakan PostgreSQL driver (psycopg2). Eksekusi dilakukan di dalam transaksi yang di-rollback agar tidak meninggalkan sisa indeks di database pengujian.

import json
import psycopg2

def run_explain_analyze(cursor, query):
    cursor.execute(f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {query}")
    result = cursor.fetchone()[0][0]
    plan = result["Plan"]
    
    total_time = result["Execution Time"]
    shared_hit = plan.get("Shared Hit Blocks", 0)
    shared_read = plan.get("Shared Read Blocks", 0)
    node_type = plan.get("Node Type")
    
    return {
        "time_ms": total_time,
        "buffers": shared_hit + shared_read,
        "node_type": node_type,
        "raw": plan
    }

def verify_candidate_index(conn_str, target_query, index_ddl, min_buffer_reduction=0.4):
    conn = psycopg2.connect(conn_str)
    conn.autocommit = False
    
    try:
        with conn.cursor() as cur:
            # 1. Baseline
            baseline = run_explain_analyze(cur, target_query)
            
            # 2. Terapkan indeks kandidat
            cur.execute(index_ddl)
            
            # 3. Metrik setelah indeks
            optimized = run_explain_analyze(cur, target_query)
            
            # 4. Validasi aturan
            buffer_diff = baseline["buffers"] - optimized["buffers"]
            reduction_ratio = (buffer_diff / baseline["buffers"]) if baseline["buffers"] > 0 else 0
            
            is_seq_scan_removed = (
                "Seq Scan" in baseline["node_type"] and 
                "Seq Scan" not in optimized["node_type"]
            )
            
            passed = reduction_ratio >= min_buffer_reduction or is_seq_scan_removed
            
            return {
                "passed": passed,
                "baseline_buffers": baseline["buffers"],
                "optimized_buffers": optimized["buffers"],
                "reduction_pct": round(reduction_ratio * 100, 2),
                "node_before": baseline["node_type"],
                "node_after": optimized["node_type"]
            }
    finally:
        conn.rollback()
        conn.close()

# Contoh Penggunaan:
# ddl = "CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);"
# query = "SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;"
# print(verify_candidate_index("postgresql://user:pass@localhost:5432/testdb", query, ddl))

Aturan Mitigasi Over-Indexing

Setiap indeks tambahan memiliki konsekuensi pada performa operasi INSERT, UPDATE, dan DELETE. Harness verifikasi wajib menerapkan aturan mitigasi berikut sebelum membuka Pull Request optimasi:

  1. Redundansi Awalan (Prefix Check): Cek metadata database (misalnya pg_indexes) untuk memastikan kandidat indeks baru bukan merupakan subset prefix dari composite index yang sudah ada.
  2. Batas Kardinalitas Rendah: Tolak pembuatan B-Tree index tunggal untuk kolom dengan variasi data sangat rendah (seperti kolom status boolean atau enum 2-3 nilai), kecuali berupa Partial Index dengan predikat WHERE yang spesifik.
  3. Dead Index Guard: Pastikan query planner benar-benar memilih indeks baru tersebut tanpa mematikan fitur planner bawaan (jangan menggunakan SET enable_seqscan = off untuk memanipulasi pengujian).
Catatan: Selalu jalankan uji verifikasi pada database replika atau runner CI/CD yang memiliki snapshot data setara produksi beserta statistik tabel (ANALYZE) yang mutakhir. Lingkungan lokal kosong akan membuat query planner PostgreSQL selalu memilih Seq Scan karena biaya baca disk dinilai terlalu kecil.