Alat bantu kecerdasan buatan dan AI agent sering menghasilkan query SQL yang valid secara fungsional namun bermasalah dalam eksekusi produksi. Pada tabel berukuran puluhan juta baris, query hasil generasi AI kerap mengabaikan korelasi indeks, menyebabkan perencana query (query planner) beralih ke degenerate index scan atau kombinasi Bitmap Index Scan yang menghabiskan CPU dan disk I/O.
Anatomi Masalah: Query AI dan Degenerate Index Scan
Masalah umum muncul saat AI menulis query analitik atau pencarian transaksi dengan banyak predikat WHERE tanpa mempertimbangkan struktur composite index yang sudah ada. Bayangkan skema tabel log transaksi berikut:
CREATE TABLE transactions (
id BIGSERIAL PRIMARY KEY,
tenant_id INT NOT NULL,
status VARCHAR(32) NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
amount NUMERIC(12, 2) NOT NULL,
metadata JSONB
);
-- Indeks yang sudah ada di database
CREATE INDEX idx_transactions_tenant_created ON transactions(tenant_id, created_at);
CREATE INDEX idx_transactions_status ON transactions(status);Sebuah AI coding assistant diminta membuat filter: "Ambil semua transaksi berstatus 'FAILED' untuk tenant 101 dalam rentang waktu bulan lalu yang membutuhkan audit."
AI menghasilkan query berikut:
SELECT id, tenant_id, status, created_at, amount
FROM transactions
WHERE tenant_id = 101
AND UPPER(status) = 'FAILED'
AND created_at >= '2025-01-01 00:00:00+00'
AND created_at < '2025-02-01 00:00:00+00'
ORDER BY created_at DESC;Dua kesalahan struktural yang terjadi di sini:
- Ekspresi non-sargable: Penggunaan
UPPER(status)membuat perencana mengabaikan indeks pada kolomstatus. - Pengabaian index filter efficiency: Kolom
statusdievaluasi sebagai filter terpisah, memaksa planner melakukan BitmapAnd atau membaca indeks rentang secara luas, menghasilkan ribuan buffer read yang sia-sia.
Diagnosa Rencana Eksekusi: EXPLAIN (ANALYZE, BUFFERS)
Jalankan diagnosis dengan buffer tracking untuk melihat biaya I/O yang sebenarnya:
EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE)
SELECT id, tenant_id, status, created_at, amount
FROM transactions
WHERE tenant_id = 101
AND UPPER(status) = 'FAILED'
AND created_at >= '2025-01-01 00:00:00+00'
AND created_at < '2025-02-01 00:00:00+00'
ORDER BY created_at DESC;Hasil rencana eksekusi yang menunjukkan bottleneck:
Bitmap Heap Scan on transactions (cost=1254.20..45812.10 rows=412 width=40) (actual time=85.120..420.311 rows=380 loops=1)
Recheck Cond: ((tenant_id = 101) AND (created_at >= '2025-01-01 00:00:00+00'::timestamptz) AND (created_at < '2025-02-01 00:00:00+00'::timestamptz))
Filter: (upper((status)::text) = 'FAILED'::text)
Rows Removed by Filter: 148200
Buffers: shared hit=4210 read=18920
-> Bitmap Index Scan on idx_transactions_tenant_created (cost=0.00..1254.10 rows=152000 width=0) (actual time=35.402..35.402 rows=148580 loops=1)
Index Cond: ((tenant_id = 101) AND (created_at >= '2025-01-01 00:00:00+00'::timestamptz) AND (created_at < '2025-02-01 00:00:00+00'::timestamptz))
Buffers: shared hit=12 read=410
Planning Time: 0.285 ms
Execution Time: 420.850 msEvaluasi metric plan di atas:
- Rows Removed by Filter: Database membaca 148.580 baris dari heap hanya untuk membuang 148.200 baris melalui filter ekspresi CPU. Ini adalah ciri khas degenerate index scan, di mana indeks membaca blok data secara masif tanpa selektivitas predikat yang efektif.
- Buffers:
read=18920blok (sekitar ~147 MB data ditarik dari disk ke shared buffers hanya untuk 380 baris hasil akhir).
Langkah Kurasi dan Perbaikan Predikat
Kurasi dilakukan dengan dua intervensi: menghapus fungsi pembungkus kolom (sargability) dan menyusun strategi indexing yang membatasi ukuran struktur pohon B-tree.
1. Normalisasi Query (Sargable Predicates)
Hapus pemanggilan fungsi UPPER() jika data status telah tersimpan dalam format kanonikal atau gunakan tipe enumerasi:
SELECT id, tenant_id, status, created_at, amount
FROM transactions
WHERE tenant_id = 101
AND status = 'FAILED'
AND created_at >= '2025-01-01 00:00:00+00'
AND created_at < '2025-02-01 00:00:00+00'
ORDER BY created_at DESC;2. Implementasi Partial B-Tree Index
Pada banyak sistem transaksi, transaksi berstatus FAILED memiliki proporsi sangat kecil dibanding status COMPLETED atau SUCCESS (misalnya hanya < 2% dari keseluruhan tabel). Alih-alih membuat composite index besar pada semua data, gunakan partial index:
CREATE INDEX idx_transactions_tenant_failed_created
ON transactions (tenant_id, created_at DESC)
WHERE status = 'FAILED';Keunggulan indeks parsial ini:
- Footprint I/O kecil: Ukuran indeks jauh lebih ringkas, ramah shared_buffers, dan mempertahankan performa cache hit ratio.
- Maintenance Cost rendah: Transaksi berstatus
SUCCESSyang mendominasi operasi penulisan (INSERT/UPDATE) tidak akan memperbarui struktur tree pada indeks ini. - Urutan Sortir: Mencantumkan
created_at DESCmenghilangkan nodeSortterpisah dari execution plan.
Evaluasi Eksekusi Pasca-Kurasi
Uji kembali performa query terkurasi menggunakan konfigurasi EXPLAIN yang sama:
EXPLAIN (ANALYZE, BUFFERS, COSTS)
SELECT id, tenant_id, status, created_at, amount
FROM transactions
WHERE tenant_id = 101
AND status = 'FAILED'
AND created_at >= '2025-01-01 00:00:00+00'
AND created_at < '2025-02-01 00:00:00+00'
ORDER BY created_at DESC;Hasil rencana eksekusi pasca-optimasi:
Index Scan using idx_transactions_tenant_failed_created on transactions (cost=0.42..38.15 rows=380 width=40) (actual time=0.035..0.210 rows=380 loops=1)
Index Cond: ((tenant_id = 101) AND (created_at >= '2025-01-01 00:00:00+00'::timestamptz) AND (created_at < '2025-02-01 00:00:00+00'::timestamptz))
Buffers: shared hit=45
Planning Time: 0.150 ms
Execution Time: 0.245 ms| Metrik | Sebelum Kurasi | Sesudah Kurasi | Efisiensi |
|---|---|---|---|
| Metode Scan | Bitmap Heap Scan | Index Scan | Penghapusan Bitmap overhead |
| Cost Estimasi | 1254.20..45812.10 | 0.42..38.15 | Penurunan cost ~1200x |
| Shared Buffers Hit/Read | 23.130 blok | 45 blok | Reduksi I/O 99.8% |
| Execution Time | 420.850 ms | 0.245 ms | Peningkatan latensi ~1700x |
Checklist Kurasi Query AI untuk Engineer
Untuk mencegah regresi performa basis data dari code generator AI, terapkan langkah peninjauan berikut:
- Deteksi Manipulasi Kolom: Periksa predikat yang membungkus kolom dalam fungsi kalkulasi (seperti
LOWER(),DATE(), atau casting eksplisit::text). Tulis ulang menjadi ekspresi literal yang kompatibel secara tipe data. - Periksa Tingkat Selektivitas Indeks: Evaluasi nilai
Rows Removed by FilterpadaEXPLAIN ANALYZE. Jika nilainya mencapai ribuan sementara baris hasil akhir sedikit, composite index yang digunakan tidak berada pada urutan kolom yang benar. Aturan umumnya: kolom persamaan (=) di depan, kolom rentang (>,<,BETWEEN) di akhir. - Gunakan Partial Indexing untuk Nilai Langka: Identifikasi status kegagalan, antrean yang belum diproses (
processed_at IS NULL), atau flag operasional yang condong (skewed) untuk dibuatkan indeks parsial daripada indeks universal.
Komentar
0 komentar
Masuk ke akun kamu untuk ikut berkomentar.
Belum ada komentar
Jadilah yang pertama ikut berdiskusi!