Fase 4 · Database & Persistensi

Index, EXPLAIN & kueri yang lambat

Sebagian besar masalah performa aplikasi web bukan di kode Go, melainkan di satu kueri yang tidak memakai index. Materi ini tentang membuktikannya, bukan menebaknya.

Sumber asli www.postgresql.org Resmi Rangkuman ~9 menit baca

Intisari

EXPLAIN (ANALYZE, BUFFERS) menjalankan kueri sungguhan dan menunjukkan waktu aktual, bukan perkiraan.
Seq Scan pada tabel besar hampir selalu index yang hilang; pada tabel kecil ia justru wajar.
Pada index gabungan, urutan kolom menentukan apakah ia terpakai sama sekali.
Fungsi pada kolom (lower(nama) = ...) mematikan index — kecuali kamu membuat index ekspresi.
Paginasi OFFSET makin lambat makin dalam; keyset pagination berbiaya tetap.

Membaca rencana kueri

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, nama, harga FROM produk
WHERE kategori_id = 3 AND harga < 50000
ORDER BY harga LIMIT 20;

Limit  (cost=0.43..8.91 rows=20 width=32)
       (actual time=0.031..0.089 rows=20 loops=1)
  Buffers: shared hit=23
  ->  Index Scan using idx_produk_kategori_harga on produk
        (cost=0.43..421.55 rows=994 width=32)
        (actual time=0.029..0.082 rows=20 loops=1)
        Index Cond: ((kategori_id = 3) AND (harga < 50000))
Planning Time: 0.187 ms
Execution Time: 0.118 ms

Yang dibaca	Artinya
`actual time`	Waktu sungguhan. `cost` cuma perkiraan relatif — abaikan angkanya
`rows` perkiraan vs aktual	Selisih besar = statistik basi. Jalankan `ANALYZE produk`
`Buffers: shared hit`	Blok dari cache. `read` berarti dari disk — jauh lebih mahal
`loops=1`	Kalau > 1, waktunya dikali jumlah itu
`Index Cond` vs `Filter`	`Filter` berarti baris dibaca dulu lalu dibuang — index tidak menyaringnya

Jenis pemindaian

Node	Artinya	Bermasalah kalau
`Seq Scan`	Baca seluruh tabel	Tabelnya besar dan ada `WHERE` selektif
`Index Scan`	Lewat index, lalu ambil barisnya	Biasanya baik
`Index Only Scan`	Cukup dari index saja	Terbaik
`Bitmap Heap Scan`	Banyak baris cocok; dikumpulkan dulu	Wajar untuk hasil menengah
`Nested Loop`	Untuk tiap baris kiri, cari di kanan	Buruk kalau sisi kiri banyak baris
`Hash Join`	Bangun tabel hash lalu cocokkan	Baik untuk join besar
`Sort` dengan `external merge`	Pengurutan tumpah ke disk	Naikkan `work_mem`, atau beri index yang sudah urut

Urutan kolom pada index gabungan

CREATE INDEX idx_produk_kategori_harga ON produk (kategori_id, harga);

Kueri	Index terpakai?
`WHERE kategori_id = 3`	✅ ya
`WHERE kategori_id = 3 AND harga < 50000`	✅ ya, keduanya
`WHERE kategori_id = 3 ORDER BY harga`	✅ ya, tanpa langkah sort
`WHERE harga < 50000`	❌ tidak — kolom pertama dilewati
`ORDER BY harga` saja	❌ tidak

Aturannya: kolom yang dibandingkan dengan = lebih dulu, lalu rentang, lalu yang dipakai ORDER BY. Index gabungan hanya bisa dipakai dari kiri — persis seperti mencari nama di buku telepon yang diurutkan nama belakang lalu nama depan: tanpa nama belakang, urutannya tidak membantu sama sekali.

Yang mematikan index

-- ❌ fungsi pada kolom: index pada nama tidak terpakai
WHERE lower(nama) = 'kopi'
-- ✅ buat index ekspresinya
CREATE INDEX idx_produk_nama_lower ON produk (lower(nama));

-- ❌ wildcard di depan: tidak bisa dicari lewat B-tree
WHERE nama LIKE '%kopi%'
-- ✅ trigram, atau pencarian teks penuh
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_produk_nama_trgm ON produk USING gin (nama gin_trgm_ops);

-- ❌ tipe tidak cocok memaksa konversi
WHERE id = '42'
-- ✅ kirim tipe yang benar dari Go (int64, bukan string)

-- ❌ OR antar kolom berbeda sering menggagalkan index
WHERE nama = $1 OR email = $1
-- ✅ dua kueri digabung UNION, atau index terpisah + bitmap OR

Index yang biasanya dibutuhkan

Pola	Index
Foreign key	Selalu — Postgres tidak membuatnya otomatis
Kolom di `WHERE` yang selektif	B-tree biasa
Filter + urutan bersama	Gabungan, urutan sesuai aturan di atas
Soft delete	Index parsial: `WHERE dihapus_pada IS NULL`
Kolom `jsonb`	GIN
Pencarian teks	GIN dengan `tsvector` atau trigram
Multi-tenant	`tenant_id` sebagai kolom pertama di hampir semua index

-- Index parsial: jauh lebih kecil, dan cocok persis dengan kueri aplikasi
CREATE INDEX idx_produk_aktif ON produk (kategori_id, harga)
WHERE dihapus_pada IS NULL;

Index bukan gratis. Setiap index memperlambat INSERT, UPDATE, dan DELETE, serta memakan disk dan memori cache. Tabel dengan 15 index yang setengahnya tidak pernah dipakai adalah pola yang umum — dan ia membuat semua tulisan lebih lambat tanpa mempercepat satu bacaan pun.

-- Index yang tidak pernah dipakai sejak statistik terakhir direset
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Paginasi: `OFFSET` versus keyset

-- ❌ OFFSET 100000 berarti database membaca lalu MEMBUANG 100.000 baris
SELECT * FROM produk ORDER BY id LIMIT 20 OFFSET 100000;

-- ✅ keyset: biaya tetap, sedalam apa pun halamannya
SELECT * FROM produk WHERE id > $1 ORDER BY id LIMIT 20;

	`OFFSET`	Keyset
Biaya di halaman jauh	Naik terus	Tetap
Lompat ke halaman 500	Bisa	Tidak
Tahan data baru masuk	Tidak — baris bisa terlihat dua kali	Ya
Cocok untuk	Tabel admin kecil	API, umpan tak terbatas

Menemukan kueri lambat di produksi

-- Aktifkan pg_stat_statements (RDS: lewat parameter group)
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS rata_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Urutkan berdasarkan total_exec_time, bukan mean_exec_time. Kueri 2 ms yang dipanggil sejuta kali membebani database jauh lebih besar daripada laporan 5 detik yang dijalankan sekali sehari — dan yang pertama itulah yang biasanya bisa diperbaiki dengan satu index atau satu cache. Di AWS, Performance Insights menampilkan hal yang sama secara grafis (Fase 10).

Latihan: isi tabel produk dengan satu juta baris (INSERT ... SELECT generate_series), jalankan kueri filter + urut dan catat Execution Time-nya. Tambahkan index gabungan dengan urutan yang benar dan ulangi. Lalu buat index dengan urutan kolom terbalik dan buktikan ia tidak terpakai — jalankan EXPLAIN untuk ketiganya dan bandingkan.

Rangkuman ini sengaja dipangkas ke bagian yang dipakai di roadmap. Buka sumber aslinya saat kamu butuh detail lengkap atau referensi parameter.