Kenapa Query Database Lu Masih Lemot Padahal Udah Dikasih Index?

Pernah ngalamin momen ini? Aplikasi tiba-tiba lag parah saat traffic naik. Lu langsung buka DBeaver atau pgAdmin, ngecek query log, nemu query yang butuh waktu 3 sampai 5 detik cuma buat narik 50 baris data.

Dengan percaya diri, lu bilang ke team lead: "Tenang, belum ada index-nya ini. Gua pasang index sekarang, pasti beres!"

Lu jalankan CREATE INDEX, deploy ke production, lalu... query-nya tetep lemot. Lu jalankan EXPLAIN ANALYZE, dan PostgreSQL dengan santainya ngasih tahu kalau dia masih pake Seq Scan (Sequential Scan). Index yang baru lu buat 100% diabaikan.

Di titik ini, banyak engineer mulai menyalahkan hardware, nyalahin ORM, atau malah langsung usul migrate ke NoSQL. Padahal masalahnya bukan di PostgreSQL-nya, tapi di pemahaman kita tentang cara kerja engine database saat mengevaluasi query dan index.

Indexing bukan jimat gaib. Asal nempel CREATE INDEX tanpa paham mekanisme B-Tree dan planner database cuma bakal nambah beban write tanpa ngasih benefit read sama sekali.

Mari kita bedah kenapa index lu diabaikan PostgreSQL dan gimana cara ngeberesinnya sampai tuntas.

1. Dosa Utama: Urutan Kolom Composite Index Ngacak (Rule Leftmost Prefix)

Ini kesalahan paling umum saat buat composite index (index lebih dari satu kolom).

Bayangain lu punya tabel orders dengan jutaan data. Query yang sering jalan adalah nyari order berdasarkan status dan created_at untuk tenant tertentu (tenant_id).

Lu buat index kaya gini:

CREATE INDEX idx_orders_status_created_tenant 
ON orders (status, created_at, tenant_id);

Lalu aplikasi lu nembak query begini:

SELECT * 
FROM orders 
WHERE tenant_id = 'tn_9982' 
  AND created_at >= '2024-01-01';

Hasilnya? Query lu tetep jalan pake Sequential Scan atau index scan yang sangat lambat. Kenapa?

Cara Kerja B-Tree Composite Index

Index B-Tree itu disusun berjenjang mirip buku telepon tebal. Buku telepon diurutkan berdasarkan Nama Belakang dulu, baru Nama Depan.

Jika lu nyari orang yang nama depannya "Budi" tanpa tahu nama belakangnya, buku telepon itu gak ada gunanya. Lu tetep harus buka halaman pertama sampai terakhir (Sequential Scan).

Pada index (status, created_at, tenant_id):

1. PostgreSQL mengurutkan data berdasarkan status terlebih dahulu.
2. Jika status sama, data diurutkan berdasarkan created_at.
3. Jika status dan created_at sama, baru diurutkan berdasarkan tenant_id.

Karena query lu gak menyertakan status di klausa WHERE, PostgreSQL gak bisa masuk ke cabang B-Tree yang tepat.

Solusi & Benchmark

Aturan utamanya (Rule of Leftmost Prefix): Letakkan kolom dengan kardinalitas/selektivitas paling tinggi dan paling sering dicarikan di urutan paling kiri.

Urutan index yang benar untuk multi-tenant app biasanya mendahulukan tenant_id:

-- Hapus index lama yang salah urutan
DROP INDEX IF EXISTS idx_orders_status_created_tenant;

-- Buat index baru dengan urutan yang benar (Leftmost Prefix)
CREATE INDEX idx_orders_tenant_status_created 
ON orders (tenant_id, status, created_at);

Mari kita uji perbedaannya dengan EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount 
FROM orders 
WHERE tenant_id = 'tn_9982' 
  AND status = 'COMPLETED' 
  AND created_at >= '2024-01-01';

Output Query Plan:

Index Scan using idx_orders_tenant_status_created on orders  (cost=0.43..8.45 rows=12 width=16) (actual time=0.034..0.041 rows=10 loops=1)
  Index Cond: ((tenant_id = 'tn_9982'::text) AND (status = 'COMPLETED'::text) AND (created_at >= '2024-01-01 00:00:00'::timestamp))
  Buffers: shared hit=4
Planning Time: 0.112 ms
Execution Time: 0.058 ms

Execution time turun dari ribuan milidetik jadi 0.058 ms. Itu peningkatan kecepatan puluhan ribu kali lipat.

2. Membungkus Kolom Terindeks Pake Fungsi (Non-SARGable Queries)

Ini jebakan batman yang sering dibuat developer pengguna ORM atau yang suka nulis raw SQL tanpa mikir panjang.

Misalkan lu punya index di kolom email pada tabel users:

CREATE INDEX idx_users_email ON users(email);

Di kode aplikasi, lu mau bikin pencarian email yang case-insensitive, jadi lu tulis query seperti ini:

SELECT id, name 
FROM users 
WHERE LOWER(email) = 'adhinugroho@vour.dev';

Begitu query ini jalan, PostgreSQL bakal mengabaikan idx_users_email total. Query planner dipaksa melakukan Sequential Scan ke seluruh baris di tabel users.

Kenapa ini terjadi?

Index idx_users_email menyimpan string email asli (adhinugroho@vour.dev, John.Doe@Gmail.com, dsb). Index tersebut tidak menyimpan hasil dari LOWER(email).

Engine database tidak tahu hasil dari LOWER(email) pada setiap baris sebelum fungsi itu dieksekusi satu per satu ke semua data. Query seperti ini disebut Non-SARGable (Not Search Argument Able).

Hal serupa terjadi saat lu menggunakan fungsi tanggal:

-- BAD: Menghancurkan penggunaan index pada created_at
SELECT * FROM transactions WHERE DATE(created_at) = '2024-02-15';

-- BAD: Menggunakan ekspresi aritmatika pada kolom
SELECT * FROM products WHERE price + 1000 = 50000;

Cara Memperbaikinya

Ada dua cara elegan untuk menyelesaikan masalah ini:

Opsi A: Ganti Query Menjadi SARGable

Ubah query agar fungsi tidak membalut kolom terindeks, melainkan diaplikasikan ke parameter input atau gunakan range comparison:

-- GOOD: Range comparison membiarkan created_at tetap murni
SELECT * FROM transactions 
WHERE created_at >= '2024-02-15 00:00:00' 
  AND created_at < '2024-02-16 00:00:00';

-- GOOD: Operasi matematika dipindah ke sisi nilai pencarian
SELECT * FROM products WHERE price = 50000 - 1000;

Opsi B: Gunakan Expression Index (Functional Index)

Jika lu memang harus melakukan query berdasarkan LOWER(email) secara masif, buat index khusus yang menampung hasil eksekusi fungsi tersebut:

-- Buat Expression Index di PostgreSQL
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

Mari cek hasilnya dengan EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT id, name 
FROM users 
WHERE LOWER(email) = 'adhinugroho@vour.dev';

Bitmap Heap Scan on users  (cost=4.30..12.45 rows=1 width=32) (actual time=0.022..0.024 rows=1 loops=1)
  Recheck Cond: (lower(email) = 'adhinugroho@vour.dev'::text)
  ->  Bitmap Index Scan on idx_users_lower_email  (cost=0.00..4.30 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=1)
        Index Cond: (lower(email) = 'adhinugroho@vour.dev'::text)
Planning Time: 0.085 ms
Execution Time: 0.040 ms

Sekarang planner memilih Bitmap Index Scan menggunakan idx_users_lower_email. Masalah tuntas.

3. Wildcard Search Seruntulan (LIKE '%text')

Fitur search bar di dashboard aplikasi sering kali menjadi penyebab utama database collapse. Developer biasanya secara spontan menulis query pencarian seperti ini:

SELECT * FROM products WHERE sku LIKE '%ABC%';

Meskipun lu udah memasang index CREATE INDEX idx_products_sku ON products(sku);, query di atas pasti melakukan Sequential Scan.

Mengapa B-Tree Gagal pada Leading Wildcard?

B-Tree index bekerja dari karakter pertama (kiri ke kanan). Jika query lu adalah LIKE 'ABC%' (trailing wildcard), B-Tree bisa melompat langsung ke node yang berawalan huruf 'A', lalu 'B', lalu 'C'.

Tapi kalau query lu berawalan % (LIKE '%ABC%'), database tidak punya petunjuk karakter pertama apa yang harus dicari. Karakter pertamanya bisa berupa huruf 'A' sampai 'Z', angka, atau simbol. Akhirnya, database terpaksa mengecek seluruh baris satu per satu.

Catatan Penting: B-Tree index HANYA bekerja untuk LIKE 'text%' (wildcard di belakang). Wildcard di depan (LIKE '%text') atau di tengah (LIKE '%text%') membuat B-Tree tidak berguna.

Solusi Modern: Trigram Index dengan pg_trgm & GIN

PostgreSQL punya rahasia ampuh untuk menangani masalah pencarian teks substring/wildcard tanpa harus beralih ke Elasticsearch: Extension `pg_trgm` dengan index GIN (Generalized Inverted Index).

Trigram memecah string menjadi potongan 3 karakter. Misalnya kata "VOUR" dipecah menjadi " v", " vo", "vou", "our", "ur ".

Berikut cara implementasinya:

-- 1. Aktifkan extension pg_trgm (cuma perlu sekali per database)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 2. Buat GIN index dengan trigram operator class
CREATE INDEX idx_products_name_trgm 
ON products USING gin (name gin_trgm_ops);

Sekarang coba jalankan query pencarian wildcard dengan EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price 
FROM products 
WHERE name LIKE '%keyboard%';

Hasil Plan:

Bitmap Heap Scan on products  (cost=12.05..21.80 rows=10 width=45) (actual time=0.105..0.118 rows=8 loops=1)
  Recheck Cond: (name ~~ '%keyboard%'::text)
  Rows Removed by Index Recheck: 0
  Heap Blocks: exact=4
  Buffers: shared hit=8
  ->  Bitmap Index Scan on idx_products_name_trgm  (cost=0.00..12.05 rows=10 width=0) (actual time=0.082..0.082 rows=8 loops=1)
        Index Cond: (name ~~ '%keyboard%'::text)
Planning Time: 0.210 ms
Execution Time: 0.145 ms

Query LIKE '%keyboard%' yang tadinya butuh full table scan berdetik-detik pada 5 juta row, kini selesai hanya dalam 0.145 milidetik!

4. Implicit Type Casting (Mismatched Data Types)

Masalah ini sering tak terlihat (silent killer) karena query tetap berjalan sukses dan menghasilkan data yang benar, tapi performanya hancur.

Bayangkan kolom account_number pada tabel bank_accounts bertipe VARCHAR(50) atau TEXT:

CREATE TABLE bank_accounts (
    id SERIAL PRIMARY KEY,
    account_number VARCHAR(50) NOT NULL,
    balance NUMERIC(15, 2)
);

CREATE INDEX idx_accounts_acc_num ON bank_accounts(account_number);

Lalu di layer aplikasi (misalnya NodeJS, Go, atau Java), developer melewatkan parameter berupa tipe Numeric / BigInt alih-alih String:

-- Parameter dikirim sebagai angka 990182312, bukan string '990182312'
SELECT * FROM bank_accounts WHERE account_number = 990182312;

Apa yang dilakukan PostgreSQL di balik layar?

PostgreSQL melihat perbandingan antara dua tipe data berbeda: VARCHAR (kolom) dan INTEGER (input parameter). Menurut aturan penentuan tipe data PostgreSQL, tipe data bernilai numerik memiliki presisi tinggi, sehingga PostgreSQL melakukan implicit type casting pada kolom:

-- Kode terselubung yang dijalankan PostgreSQL:
SELECT * FROM bank_accounts WHERE account_number::bigint = 990182312;

Kembali lagi ke prinsip Dosa #2: Kolom dibungkus oleh operasi perubah tipe data (`::bigint`). Hasilnya, index idx_accounts_acc_num langsung diabaikan dan dipaksa memakai Sequential Scan.

Solusi

• Pastikan tipe data variabel di aplikasi sesuai 100% dengan tipe data kolom di schema database.
• Jika menggunakan Raw SQL, lakukan explicit casting pada nilai input parameter, bukan pada kolomnya:

-- BAD
SELECT * FROM bank_accounts WHERE account_number = 990182312;

-- GOOD
SELECT * FROM bank_accounts WHERE account_number = '990182312';

5. Parameter Selektivitas: Saat PostgreSQL Menganggap Table Scan Lebih Murah

Ada kalanya lu udah bikin index dengan benar, urutan benar, query SARGable, tipe data cocok... tapi pas di-EXPLAIN ANALYZE, PostgreSQL tetep kekeh milih Seq Scan.

Apakah PostgreSQL lagi error?

Tidak. PostgreSQL Query Planner sangat pintar dan bekerja berbasiskan statistik kalkulasi cost I/O (cost-based optimizer).

Pahami Konsep Selectivity & Random Disk I/O

Index scan bukanlah operasi gratis. Mekanisme Index Scan bekerja dua kali:

1. Membaca node B-Tree Index untuk mendapatkan pointer alamat memori/disk (ItemPointer / TID).
2. Melakukan random access membaca blok data di tabel utama (Heap).

Sedangkan Sequential Scan membaca blok data tabel secara berurutan (sequential disk read), yang dari segi hardware jauh lebih efisien untuk membaca data dalam jumlah besar sekaligus.

Aturan Selektivitas: Jika query lu mengembalikan lebih dari 15% - 20% dari total seluruh baris data di tabel, PostgreSQL menilai bahwa melakukan Random Access Disk Read via Index jauh lebih mahal ketimbang menyapu bersih seluruh tabel via Sequential Scan.

Uji Coba Kasus Selektivitas

Misal tabel orders punya 1.000.000 data. Kolom status berisi:

• 'PENDING' (5.000 baris = 0.5%)
• 'COMPLETED' (995.000 baris = 99.5%)

Jika lu buat index di status:

CREATE INDEX idx_orders_status ON orders(status);

• Query WHERE status = 'PENDING' -> Pasti menggunakan Index Scan karena hasilnya sangat sedikit (selektivitas tinggi).
• Query WHERE status = 'COMPLETED' -> Pasti menggunakan Sequential Scan karena PostgreSQL harus mengambil 99.5% isi tabel. Memakai index untuk 99.5% data cuma bikin disk I/O kerja dua kali lipat lebih berat.

Solusi: Partial Index

Jika lu cuma sering nge-query data berstatus 'PENDING' (yang jumlahnya sedikit), jangan indeks seluruh tabel. Gunakan Partial Index untuk menghemat ukuran memori RAM dan mempercepat waktu write:

-- Partial Index: Hanya mengindeks baris yang statusnya PENDING
CREATE INDEX idx_orders_pending_only 
ON orders (created_at) 
WHERE status = 'PENDING';

Ukuran index ini sangat kecil (hanya menyimpan 0.5% data), hemat RAM shared_buffers, dan sangat cepat saat di-scan.

6. Jangan Lupa Lakukan Maintenance Statistik (ANALYZE)

Query planner PostgreSQL mengambil keputusan berdasarkan statistik distribusi data yang disimpan di tabel sistem pg_statistic.

Jika tabel lu baru saja kemasukan bulk import jutaan data atau baru melalui migrasi besar, statistik di PostgreSQL bisa kadaluarsa (stale statistics). Planner mengira isi tabel masih 1.000 baris (sehingga memilih Seq Scan), padahal isinya sudah 10.000.000 baris.

Solusinya sangat sederhana. Paksa PostgreSQL memperbarui statistiknya:

-- Perbarui statistik untuk tabel tertentu
ANALYZE VERBOSE orders;

Secara rutin pastikan daemon autovacuum di PostgreSQL lu aktif dan terkonfigurasi dengan benar agar proses ANALYZE berjalan otomatis di background.

Checklist Ringkas & Langkah Diagnosa (Action Items)

Ketika lu nemu query lemot padahal udah ada index, jalankan checklist langkah diagnosa ini:

1. Jalankan `EXPLAIN (ANALYZE, BUFFERS)`

Jangan pernah mengira-ngira. Lihat output aslinya. Apakah menggunakan Seq Scan, Index Scan, Index Only Scan, atau Bitmap Heap Scan?

1. Cek SARGability Query

Apakah ada fungsi seperti LOWER(), DATE(), COALESCE(), atau operasi matematika + / - yang membungkus kolom terindeks?

1. Cek Urutan Composite Index

Apakah filter di klausa WHERE lu menyertakan kolom paling kiri (leftmost) dari composite index?

1. Cek Wildcard (`LIKE`)

Apakah pencarian menggunakan %keyword di depan? Jika ya, ganti B-Tree menjadi GIN Index Trigram (pg_trgm).

1. Cek Selektivitas Data

Berapa banyak baris yang dikembalikan dibanding total baris tabel? Jika >20%, pertimbangkan Partial Index atau memang Seq Scan pilihan terbaik.

1. Update Statistik Tabel

Jalankan ANALYZE table_name; untuk memastikan optimizer mendapat angka statistik paling presisi.

Satu index yang dirancang dengan tepat jauh lebih berharga daripada sepuluh index sembarangan yang cuma menggelembungkan ukuran disk server lu. Selamat merapikan query!