Belajar SQL PostgreSQL - Query Optimization & EXPLAIN ANALYZE

#Pendahuluan

Selamat datang di episode 15 series Belajar SQL PostgreSQL! Pada episode 14 kita belajar membuat index — tapi bagaimana kita tahu index itu benar-benar terpakai? Bagaimana caranya menemukan mengapa sebuah query lambat? Jawabannya bukan menebak, melainkan membaca execution plan — cetak biru cara PostgreSQL menjalankan query kalian. Keterampilan ini adalah "superpower" seorang database developer.

Ketika ada query yang lambat, jangan langsung menebak-nebak solusi. Jalankan EXPLAIN ANALYZE, baca dengan teliti, dan biarkan data berbicara. Apakah query-nya melakukan full table scan padahal seharusnya pakai index? Apakah join-nya memilih algoritma yang salah? Semua jawaban ada di execution plan.

Di episode ini, kita akan membahas perintah EXPLAIN dan EXPLAIN ANALYZE, memahami komponen execution plan seperti Sequential Scan, Index Scan, dan Bitmap Index Scan, tiga join algorithms (Nested Loop, Hash Join, Merge Join), cara membaca metrik cost, rows, width, actual time, dan loops, serta mengidentifikasi query lambat di produksi dengan ekstensi pg_stat_statements.

#Menganalisis Query Execution Plan

#EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN menampilkan rencana eksekusi yang diperkirakan planner — tanpa benar-benar menjalankan query. EXPLAIN ANALYZE menjalankan query sungguhan dan menampilkan statistik aktual. Untuk analisis performa nyata, selalu gunakan EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'budi@example.com';

Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=44)
  Index Cond: (email = 'budi@example.com'::text)
  Actual Time: 0.032..0.034 rows=1 loops=1
Planning Time: 0.143 ms
Execution Time: 0.045 ms

Baca dari dalam ke luar: PostgreSQL memakai Index Scan dengan kondisi Index Cond. Waktu eksekusinya 0.045 ms — sangat cepat. Jika output menunjukkan Seq Scan di tabel besar dengan Actual Time ratusan milidetik, itu sinyal ada index yang hilang.

#Memahami Komponen Execution Plan

#Sequential Scan vs Index Scan vs Bitmap Index Scan

Tiga cara PostgreSQL mengakses tabel:

• Seq Scan (Sequential Scan): membaca seluruh baris tabel dari awal sampai akhir. Efisien untuk tabel kecil atau query yang mengambil proporsi besar data (misal 30%+ baris).
• Index Scan: membaca index lalu mengambil baris yang cocok. Cepat untuk filter selektif, tapi satu baris = satu akses halaman (row-by-row).
• Bitmap Index Scan: membaca index, membangun bitmap baris yang cocok, lalu mengambil semua halaman yang mengandung baris tersebut sekaligus. Efisien untuk filter yang cocok dengan banyak baris yang tersebar.

EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 500000;

Jika baris yang cocok sangat banyak, planner mungkin memilih Seq Scan karena lebih murah daripada memakai index satu-per-satu. Ini bukan berarti indexnya jelek — planner tahu kapan index tidak sepadan.

#Join Algorithms: Nested Loop, Hash Join, Merge Join

Tiga algoritma join yang dipilih planner:

EXPLAIN ANALYZE
SELECT c.full_name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Output akan menunjukkan Hash Join atau Nested Loop — dan dari situ kalian bisa menilai apakah index orders(customer_id) perlu dibuat (Nested Loop sangat diuntungkan index pada sisi dalam).

#Membaca Cost, Rows, Width, Actual Time, dan Loops

Setiap node menampilkan metrik:

• Cost: estimasi biaya relatif (start..total). Satuan abstrak — bandingkan antar rencana, bukan nilai absolut.
• Rows: estimasi jumlah baris yang dikembalikan node.
• Width: perkiraan byte per baris.
• Actual Time: waktu aktual start..end per loop, dalam milidetik.
• Loops: berapa kali node dieksekusi. loops tinggi pada inner scan adalah penanda Nested Loop yang mahal.

#Identifikasi Query Lambat di Produksi

Tidak semua query lambat bisa direproduksi di lokal. Di produksi dengan beban nyata, kita butuh alat untuk menemukan query yang paling banyak menyita waktu. Ekstensi pg_stat_statements adalah jawabannya.

#Mengaktifkan pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Agar ekstensi ini berfungsi penuh, parameter shared_preload_libraries = 'pg_stat_statements' harus diset di postgresql.conf lalu server di-restart. Setelah itu, ekstensi mulai mencatat semua query yang dieksekusi.

#Menemukan Query Paling Lambat

SELECT
    query,
    calls,
    total_exec_time / 1000 AS total_ms,
    mean_exec_time / 1000 AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Hasilnya menampilkan query dengan total waktu eksekusi terbesar — kandidat utama optimasi. Perhatikan kolom calls (frekuensi) dan mean_exec_time (rata-rata): query yang dipanggil ribuan kali dengan rata-rata tinggi adalah masalah terbesar.

#Alur Kerja Optimasi yang Sistematis

1. Temukan query lambat lewat pg_stat_statements atau log slow query (log_min_duration_statement di postgresql.conf).
2. Jalankan EXPLAIN (ANALYZE, BUFFERS) pada query tersebut.
3. Baca node paling mahal: apakah ada Seq Scan pada tabel besar? Apakah rows meleset dari aktual?
4. Perbaiki: tambah index yang tepat, tulis ulang query, atau perbarui statistik.
5. Ukur ulang: bandingkan Execution Time sebelum dan sesudah.

#Kesalahan Umum

#Penutup

Di episode 15 ini kita sudah menguasai optimasi query berbasis bukti: membaca execution plan dengan EXPLAIN dan EXPLAIN ANALYZE, memahami perbedaan Sequential Scan, Index Scan, dan Bitmap Index Scan, tiga join algorithms (Nested Loop, Hash Join, Merge Join), metrik cost, rows, width, actual time, dan loops, serta menemukan query lambat di produksi dengan pg_stat_statements.

Inti yang harus dibawa pulang:

• EXPLAIN ANALYZE menjalankan query sungguhan — selalu pakai untuk analisis aktual.
• Seq Scan di tabel besar = sinyal index hilang; Bitmap Scan = banyak baris cocok tersebar.
• Nested Loop, Hash Join, Merge Join — kenali kapan masing-masing dipilih planner.
• rows estimasi vs aktual yang meleset menunjukkan statistik basi → ANALYZE.
• pg_stat_statements adalah kamera pengawas query di produksi.

Di episode 16 selanjutnya, kita masuk ke keamanan: Roles, Security Management & Row Level Security (RLS) — mulai dari konsep role yang menggabungkan user dan group, perintah CREATE ROLE, GRANT, dan REVOKE, hingga RLS untuk keamanan multi-tenant di tingkat baris, serta pengamanan pg_hba.conf.