Beberapa strategi untuk Optimisasi SQL Query

 

Panduan Lengkap Optimasi Query Database PostgreSQL

Pengenalan: Mengapa Query Ini Lambat?

Bayangkan Anda diminta mencari informasi dari 3 buku berbeda, tetapi setiap kali ingin tahu nama seseorang, Anda harus membuka buku pertama dari awal sampai akhir. Lalu untuk tahu nama proyeknya, buka buku kedua dari awal lagi. Dan Anda melakukan ini ribuan kali. Melelahkan, bukan?

Itulah yang terjadi pada query database yang tidak dioptimasi. Mari kita pelajari bagaimana memperbaikinya!

1. Memahami Masalah Query yang Ada

Query Bermasalah (Yang Lambat)

SELECT
    t.employee_id,
    (SELECT name FROM employee e WHERE e.id = t.employee_id) AS employee_name,
    (SELECT name FROM project p WHERE p.id = t.project_id) AS project_name,
    SUM(EXTRACT(EPOCH FROM (t.time_to - t.time_from)) / 3600) AS total_hours
FROM time_record t
WHERE t.time_from >= NOW() - INTERVAL '1 month'
GROUP BY
    t.employee_id,
    (SELECT name FROM employee e WHERE e.id = t.employee_id),
    (SELECT name FROM project p WHERE p.id = t.project_id)
ORDER BY
    (SELECT name FROM employee e WHERE e.id = t.employee_id),
    (SELECT name FROM project p WHERE p.id = t.project_id);

Masalah Utama #1: Subquery Berulang (Correlated Subquery)

Apa itu?

(SELECT name FROM employee e WHERE e.id = t.employee_id)

Ini adalah subquery yang dieksekusi untuk setiap baris data. Lebih parahnya, subquery ini muncul 3 kali dalam query yang sama:

• 1x di bagian SELECT
• 1x di bagian GROUP BY
• 1x di bagian ORDER BY

Analogi Sederhana: Bayangkan ada 1.000 siswa, dan untuk setiap siswa, Anda harus:

1. Buka buku daftar nama untuk mencari namanya
2. Buka lagi buku yang sama untuk mengelompokkan data
3. Buka lagi buku yang sama untuk mengurutkan data

Total: Anda membuka buku tersebut 3.000 kali! Padahal cukup buka sekali dan ingat hasilnya.

Dampak Performance:

• Untuk 100 baris data → 300 eksekusi subquery
• Untuk 10.000 baris data → 30.000 eksekusi subquery
• Untuk 1 juta baris data → 3 juta eksekusi subquery

Masalah Utama #2: Tidak Ada Index

Apa itu Index?

Index seperti daftar isi dalam buku. Tanpa daftar isi, Anda harus membaca buku dari halaman 1 sampai terakhir untuk menemukan topik tertentu. Dengan daftar isi, Anda langsung melompat ke halaman yang tepat.

Contoh Tanpa Index: Mencari karyawan dengan ID 101 dari 1 juta karyawan:

• Database harus cek satu-per-satu: "Apakah ini 101? Bukan. Apakah ini 101? Bukan..."
• Total: 1 juta pengecekan!

Contoh Dengan Index:

• Database langsung melompat ke data ID 101
• Total: Hanya ~20 pengecekan (menggunakan struktur pohon/B-tree)

Index yang Hilang:

1. Index untuk employee_id (untuk JOIN ke tabel employee)
2. Index untuk project_id (untuk JOIN ke tabel project)
3. Index untuk time_from (untuk filter tanggal)

Masalah Utama #3: Pengelompokan (GROUP BY) Tidak Efisien

GROUP BY
    t.employee_id,
    (SELECT name FROM employee e WHERE e.id = t.employee_id),
    (SELECT name FROM project p WHERE p.id = t.project_id)

Masalahnya: PostgreSQL harus menjalankan subquery lagi untuk setiap baris yang akan dikelompokkan. Ini pemborosan komputasi yang sangat besar!

Analogi: Seperti Anda menghitung total gaji per departemen, tapi setiap kali menghitung, Anda harus telepon HRD dulu untuk tanya nama departemennya. Padahal Anda sudah punya datanya!

2. Strategi Optimasi: Langkah demi Langkah

Langkah 1: Membuat Index (WAJIB!)

Index adalah fondasi optimasi. Tanpa index, teknik lain tidak akan efektif.

-- Index 1: Untuk relasi employee_id
-- Mempercepat JOIN antara time_record dan employee
CREATE INDEX idx_time_record_employee_id ON time_record(employee_id);

-- Index 2: Untuk relasi project_id  
-- Mempercepat JOIN antara time_record dan project
CREATE INDEX idx_time_record_project_id ON time_record(project_id);

-- Index 3: Untuk filter tanggal
-- Mempercepat pencarian data bulan terakhir
CREATE INDEX idx_time_record_time_from ON time_record(time_from);

Penjelasan Masing-masing Index:

Index #1 & #2 (Foreign Key):

• Tanpa Index:
  • Cari employee dengan ID 101 → Scan 1 juta baris
  • Waktu: O(n) = linear
• Dengan Index:
  • Langsung lompat ke ID 101 → ~20 perbandingan
  • Waktu: O(log n) = logaritmik
• Perbedaan: Untuk 1 juta data, 1.000.000 vs 20 operasi!

Index #3 (Date Range):

• Tanpa Index:
  • Cari data bulan lalu → Scan semua baris, cek tanggalnya satu-satu
• Dengan Index:
  • Lompat langsung ke range tanggal yang diminta
  • Skip data yang tidak relevan
• Perbedaan: Jika data bulan lalu hanya 5% dari total, Anda skip 95% data!

Kapan Menggunakan Composite Index (Opsional):

CREATE INDEX idx_time_record_composite 
ON time_record(time_from, employee_id, project_id);

• Untuk query yang sangat kompleks dan sering dijalankan
• Lebih cepat tapi pakai lebih banyak disk space
• Trade-off: Menulis data lebih lambat, membaca lebih cepat
• Gunakan jika: Aplikasi Anda lebih banyak membaca (read) daripada menulis (write)

Langkah 2: Ganti Subquery dengan JOIN

Query yang Dioptimasi:

SELECT
    t.employee_id,
    e.name AS employee_name,
    p.name AS project_name,
    SUM(EXTRACT(EPOCH FROM (t.time_to - t.time_from)) / 3600) AS total_hours
FROM time_record t
INNER JOIN employee e ON e.id = t.employee_id
INNER JOIN project p ON p.id = t.project_id
WHERE t.time_from >= NOW() - INTERVAL '1 month'
GROUP BY
    t.employee_id,
    e.name,
    p.name
ORDER BY
    e.name,
    p.name;

Perubahan Kunci:

SEBELUM:

(SELECT name FROM employee e WHERE e.id = t.employee_id)

• Dijalankan untuk setiap baris
• Dijalankan 3x (SELECT, GROUP BY, ORDER BY)
• Tidak bisa menggunakan index secara optimal

SESUDAH:

INNER JOIN employee e ON e.id = t.employee_id

• Dijalankan sekali untuk semua baris
• Hasilnya bisa dipakai berkali-kali
• Menggunakan index dengan optimal

Analogi Mudah:

Metode Subquery (Lambat):

1. Ambil data jam kerja karyawan 101
2. Cari nama karyawan 101 di tabel employee → "Tom"
3. Ambil data jam kerja karyawan 102
4. Cari nama karyawan 102 di tabel employee → "Jerry"
5. Kelompokkan data... tunggu, cari lagi nama karyawan 101 → "Tom"
6. Kelompokkan data... tunggu, cari lagi nama karyawan 102 → "Jerry"
7. Urutkan data... tunggu, cari lagi nama karyawan 101 → "Tom"
8. Dan seterusnya...

Metode JOIN (Cepat):

1. Gabungkan semua data sekaligus:
   • Data jam kerja karyawan 101 + namanya "Tom"
   • Data jam kerja karyawan 102 + namanya "Jerry"
2. Kelompokkan (nama sudah ada, tidak perlu cari lagi)
3. Urutkan (nama sudah ada, tidak perlu cari lagi)

Kesimpulan: JOIN mengambil data sekali, subquery mengambil berkali-kali!

Perbandingan Performance

Untuk 1 Juta Baris Data:

Aspek	Query Lama	Query Baru	Peningkatan
Waktu Eksekusi	30 detik	500 ms	60x lebih cepat
Waktu Planning	100 ms	10 ms	10x lebih cepat
SubPlan Execution	3 juta kali	0 kali	∞
Tipe Scan	Sequential	Index	Jauh lebih efisien

Mengapa Bisa Secepat Ini?

Kompleksitas Algoritma:

• Subquery: O(n²) - untuk setiap baris, cari lagi di tabel lain
• JOIN dengan Index: O(n log n) - untuk setiap baris, lookup langsung via index

Untuk 1 juta baris:

• O(n²) = 1,000,000,000,000 operasi (1 triliun!)
• O(n log n) = 20,000,000 operasi (20 juta)
• Selisih: 50.000x lebih sedikit operasi!

3. Teknik Optimasi Lanjutan

Teknik A: Materialized View (Tampilan Termaterialisasi)

Kapan Digunakan:

• Query yang sama dijalankan berulang kali (misal: laporan bulanan)
• Data tidak perlu real-time (boleh agak lama/stale)
• Aplikasi dashboard atau reporting

Konsep: Materialized View seperti foto hasil query. Alih-alih menghitung ulang setiap kali, Anda simpan hasilnya dan tinggal lihat fotonya.

Implementasi:

-- Buat Materialized View
CREATE MATERIALIZED VIEW mv_monthly_hours AS
SELECT
    t.employee_id,
    e.name AS employee_name,
    t.project_id,
    p.name AS project_name,
    DATE_TRUNC('month', t.time_from) AS month,
    SUM(EXTRACT(EPOCH FROM (t.time_to - t.time_from)) / 3600) AS total_hours
FROM time_record t
INNER JOIN employee e ON e.id = t.employee_id
INNER JOIN project p ON p.id = t.project_id
GROUP BY
    t.employee_id,
    e.name,
    t.project_id,
    p.name,
    DATE_TRUNC('month', t.time_from);

-- Buat index di Materialized View
CREATE INDEX idx_mv_monthly_hours_month ON mv_monthly_hours(month);

-- Query jadi super cepat!
SELECT * FROM mv_monthly_hours
WHERE month = DATE_TRUNC('month', NOW() - INTERVAL '1 month');

Cara Kerja:

1. Data dihitung sekali dan disimpan fisik di disk
2. Query tinggal baca hasil yang sudah jadi
3. Refresh secara berkala (misal: setiap hari jam 2 pagi)

-- Refresh data (jalankan via cron job)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_hours;

Perbandingan Performance:

• Query langsung ke tabel: 30 detik
• Query ke Materialized View: 10 milidetik (3.000x lebih cepat!)
• Waktu refresh: 5 detik (acceptable untuk refresh harian)

Analogi:

• Tanpa MV: Setiap kali bos minta laporan, Anda hitung manual dari awal
• Dengan MV: Anda sudah siapkan laporan kemarin, tinggal kasih ke bos (update besok pagi)

Teknik B: Table Partitioning (Pemisahan Tabel)

Kapan Digunakan:

• Tabel sangat besar (10GB+, jutaan baris)
• Query sering filter berdasarkan range waktu
• Ada kebijakan retensi data (misal: hapus data lama)

Konsep: Partitioning seperti membagi arsip besar menjadi folder-folder kecil per bulan. Alih-alih cari di semua arsip, cukup buka folder bulan yang diminta.

Implementasi:

-- Buat tabel partitioned
CREATE TABLE time_record_partitioned (
    id INT8 NOT NULL,
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    time_from TIMESTAMP NOT NULL,
    time_to TIMESTAMP NOT NULL
) PARTITION BY RANGE (time_from);

-- Buat partisi per bulan
CREATE TABLE time_record_2024_01 PARTITION OF time_record_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE time_record_2024_02 PARTITION OF time_record_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE time_record_2024_03 PARTITION OF time_record_partitioned
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
-- Dan seterusnya...

-- Query otomatis pakai Partition Pruning
SELECT * FROM time_record_partitioned
WHERE time_from >= '2024-02-01' AND time_from < '2024-03-01';
-- PostgreSQL HANYA scan partisi Februari, skip yang lain!

Cara Kerja:

1. Data Januari disimpan di tabel terpisah
2. Data Februari di tabel terpisah
3. Data Maret di tabel terpisah
4. Query filter Februari → PostgreSQL otomatis skip Januari & Maret!

Keuntungan:

• Partition Pruning: Skip partisi yang tidak relevan
• Smaller Index: Index per partisi lebih kecil = lebih cepat
• Easy Archival: Tinggal hapus partisi lama (misal: hapus data 2 tahun lalu)
• Parallel Query: Bisa query beberapa partisi paralel

Perbandingan:

• Tanpa Partition: Scan 12 bulan = 100% data
• Dengan Partition: Scan 1 bulan = 8.3% data (12x lebih sedikit!)

Analogi:

• Tanpa Partition: Arsip 10 tahun dalam 1 lemari besar → susah cari
• Dengan Partition: Arsip dipisah per tahun → langsung buka tahun yang diminta

Teknik C: Application-Level Caching

Kapan Digunakan:

• Dashboard yang diakses banyak user
• Query yang hasilnya tidak berubah sering
• Aplikasi dengan traffic tinggi

Konsep: Caching seperti fotokopi hasil query. User pertama tunggu agak lama, user selanjutnya dapat hasil instant dari fotokopian.

Implementasi (Pseudocode):

// Contoh di Node.js dengan Redis
async function getMonthlyHours(month) {
    const cacheKey = `monthly_hours:${month}`;
    
    // Cek cache dulu
    let result = await cache.get(cacheKey);
    
    if (!result) {
        // Cache miss - query database
        result = await db.query(`
            SELECT t.employee_id, e.name, p.name, 
                   SUM(EXTRACT(EPOCH FROM (t.time_to - t.time_from)) / 3600)
            FROM time_record t
            JOIN employee e ON e.id = t.employee_id
            JOIN project p ON p.id = t.project_id
            WHERE t.time_from >= $1
            GROUP BY t.employee_id, e.name, p.name
        `, [month]);
        
        // Simpan di cache selama 1 jam
        await cache.set(cacheKey, result, 3600);
    }
    
    return result;
}

Strategi Cache Invalidation:

1. Time-based: Cache expire otomatis setelah X waktu

   • Simple
   • Cocok untuk data yang update rutin

2. Event-based: Hapus cache saat ada perubahan data

   • Lebih akurat
   • Lebih kompleks implement

3. Hybrid: Kombinasi keduanya

   • Expire setelah 1 jam, tapi hapus manual jika ada update data penting

Perbandingan:

• Query pertama: 500 ms (dari database)
• Query kedua dst: 5 ms (dari cache) - 100x lebih cepat!

4. Rangkuman: Kapan Pakai Teknik Apa?

Teknik	Kompleksitas	Kecepatan	Kapan Digunakan
Index + JOIN	Rendah	10-100x	SELALU! Ini fondasi wajib
Materialized View	Sedang	100-1000x	Laporan rutin (harian/mingguan)
Partitioning	Tinggi	10-50x	Tabel sangat besar (>10GB)
Caching	Sedang	1000x+	Dashboard real-time, traffic tinggi

Rekomendasi Pendekatan:

Fase 1 (WAJIB): Index + Rewrite Query

• Buat index di foreign key dan filter column
• Ganti subquery dengan JOIN
• INI HARUS DILAKUKAN DULU!

Fase 2 (Jika Masih Lambat): Materialized View

• Untuk query yang dijalankan berkali-kali
• Cocok untuk laporan bulanan/mingguan

Fase 3 (Jika Data Sangat Besar): Partitioning

• Saat tabel sudah >10GB
• Data bertambah terus (time-series)

Fase 4 (Jika Traffic Tinggi): Caching

• Dashboard yang diakses banyak user
• Kombinasikan dengan teknik lain

5. Monitoring dan Maintenance

Cara Cek Performance Query

-- Lihat execution plan
EXPLAIN (ANALYZE, BUFFERS) 
SELECT ... -- query Anda

-- Lihat query paling lambat
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%time_record%'
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Cek apakah index dipakai
SELECT tablename, indexname, idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE tablename = 'time_record'
ORDER BY idx_scan DESC;

Cara Baca EXPLAIN Output

Yang Bagus (✓):

• Index Scan atau Index Only Scan → Pakai index
• Hash Join → JOIN efisien
• Execution time rendah

Yang Buruk (✗):

• Seq Scan → Scan seluruh tabel (lambat!)
• SubPlan → Correlated subquery (sangat lambat!)
• Execution time tinggi

6. Kesimpulan Penting untuk Siswa

3 Prinsip Emas Optimasi Database:

1. Index adalah Teman Terbaik Anda

• Selalu buat index di kolom yang sering di-JOIN
• Selalu buat index di kolom yang sering di-filter (WHERE)
• Index = daftar isi buku → melompat langsung, tidak baca dari awal

2. JOIN Lebih Baik dari Subquery (untuk kasus seperti ini)

• Subquery berjalan berkali-kali → pemborosan
• JOIN berjalan sekali → efisien
• Subquery: O(n²), JOIN: O(n log n)

3. Jangan Hitung Ulang yang Sudah Dihitung

• GROUP BY pakai kolom, bukan expression
• ORDER BY pakai kolom, bukan expression
• Reuse hasil yang sudah ada

Mindset Developer Profesional:

1. Measure First: Jalankan EXPLAIN ANALYZE dulu, baru optimasi
2. Start Simple: Index + JOIN dulu, baru teknik advanced
3. Test Results: Pastikan hasil query sama sebelum dan sesudah optimasi
4. Monitor Always: Cek performance secara rutin, jangan tunggu komplain user

Latihan untuk Siswa:

1. Jalankan query lama dengan EXPLAIN ANALYZE, screenshot hasilnya
2. Buat index sesuai panduan
3. Jalankan query baru dengan EXPLAIN ANALYZE, bandingkan hasilnya
4. Hitung berapa kali lebih cepat query Anda sekarang!

Kesimpulan dan Penutup:

Optimasi database bukan magic, tapi pemahaman tentang bagaimana database bekerja. Seperti Anda tidak akan membaca buku dari awal setiap kali cari kata tertentu (Anda pakai daftar isi), database juga butuh "daftar isi" (index) untuk bekerja efisien.

Mulai dari yang simple (index + JOIN), kuasai dulu, baru ke teknik advanced. Happy optimizing!