Cara Optimasi Query PostgreSQL dengan EXPLAIN

Performa database adalah salah satu faktor krusial dalam pengembangan aplikasi. Ketika query PostgreSQL mulai terasa lambat, langkah pertama yang wajib kamu lakukan adalah cara optimasi query PostgreSQL dengan EXPLAIN ANALYZE. Fitur bawaan ini membantu kamu memahami bagaimana PostgreSQL mengeksekusi setiap query dan di mana letak bottleneck-nya.

Dalam artikel ini, kita akan membahas secara praktis bagaimana menggunakan EXPLAIN dan EXPLAIN ANALYZE untuk menganalisis, memahami, dan mengoptimasi query di PostgreSQL.

Apa Itu EXPLAIN dan EXPLAIN ANALYZE?

PostgreSQL menyediakan dua perintah utama untuk menganalisis query:

• EXPLAIN - menampilkan query plan tanpa benar-benar menjalankan query. Cocok untuk query yang bersifat destruktif seperti DELETE atau UPDATE.
• EXPLAIN ANALYZE - menjalankan query secara nyata dan menampilkan actual execution time beserta jumlah row yang diproses.

-- Hanya melihat query plan (tidak menjalankan query)
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Menjalankan query dan menampilkan actual time
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

Perbedaan utamanya: EXPLAIN hanya memberikan estimasi, sedangkan EXPLAIN ANALYZE memberikan data aktual. Untuk optimasi yang akurat, selalu gunakan EXPLAIN ANALYZE pada query SELECT.

Membaca Output EXPLAIN ANALYZE

Ketika kamu menjalankan EXPLAIN ANALYZE, output-nya mungkin terlihat membingungkan di awal. Mari kita bedah satu per satu.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1042;

Output yang mungkin muncul:

Seq Scan on orders  (cost=0.00..1250.00 rows=15 width=120)
  (actual time=0.025..12.450 rows=12 loops=1)
  Filter: (customer_id = 1042)
  Rows Removed by Filter: 49988
Planning Time: 0.085 ms
Execution Time: 12.510 ms

Komponen Penting dalam Output

• Seq Scan - PostgreSQL melakukan sequential scan alias membaca seluruh tabel baris per baris. Ini biasanya tanda bahwa index belum ada.
• cost=0.00..1250.00 - estimasi biaya dari awal hingga selesai. Angka ini relatif, bukan dalam satuan waktu.
• rows=15 - estimasi jumlah baris yang akan dikembalikan.
• actual time=0.025..12.450 - waktu nyata dalam milidetik. Angka pertama adalah waktu hingga baris pertama, angka kedua hingga baris terakhir.
• Rows Removed by Filter: 49988 - jumlah baris yang dibaca tapi tidak memenuhi kondisi WHERE. Angka besar di sini berarti banyak pekerjaan sia-sia.

Langkah Praktis Optimasi Query

1. Identifikasi Sequential Scan pada Tabel Besar

Sequential Scan pada tabel kecil (ratusan baris) tidak masalah. Tapi pada tabel dengan jutaan baris, ini adalah masalah serius.

-- Cek ukuran tabel
SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC;

Jika tabel besar dan query masih menggunakan Seq Scan, saatnya menambahkan index.

2. Tambahkan Index yang Tepat

Berdasarkan contoh sebelumnya, kolom customer_id jelas membutuhkan index:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Setelah membuat index, jalankan EXPLAIN ANALYZE lagi:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1042;

Output seharusnya berubah menjadi:

Index Scan using idx_orders_customer_id on orders
  (cost=0.42..8.44 rows=12 width=120)
  (actual time=0.028..0.065 rows=12 loops=1)
  Index Cond: (customer_id = 1042)
Planning Time: 0.120 ms
Execution Time: 0.095 ms

Perhatikan perbedaannya: dari 12.510 ms menjadi 0.095 ms - peningkatan lebih dari 100x lipat.

3. Gunakan Composite Index untuk Multi-Column Filter

Jika query sering memfilter lebih dari satu kolom, gunakan composite index:

-- Query yang sering dijalankan
SELECT * FROM orders
WHERE customer_id = 1042 AND status = 'pending';

-- Buat composite index
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

Urutan kolom dalam composite index penting. Letakkan kolom dengan kardinalitas tinggi (banyak nilai unik) di depan.

4. Partial Index untuk Data Spesifik

Jika kamu hanya perlu query data dengan kondisi tertentu secara berulang, partial index lebih efisien:

-- Hanya index order yang masih pending
CREATE INDEX idx_orders_pending
ON orders(customer_id)
WHERE status = 'pending';

Partial index menghemat ruang disk dan lebih cepat karena hanya meng-index subset data yang relevan.

Teknik EXPLAIN Lanjutan

Menggunakan BUFFERS untuk Analisis I/O

Tambahkan opsi BUFFERS untuk melihat berapa banyak data yang dibaca dari disk vs cache:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 1042;

Index Scan using idx_orders_customer_id on orders
  (actual time=0.028..0.065 rows=12 loops=1)
  Buffers: shared hit=4
Planning Time: 0.120 ms
Execution Time: 0.095 ms

• shared hit=4 - semua 4 page data dibaca dari shared buffer (RAM). Bagus!
• shared read=N - jika muncul ini, berarti data dibaca dari disk. Pertimbangkan untuk memperbesar shared_buffers.

Menggunakan FORMAT JSON untuk Parsing Otomatis

Jika kamu ingin mengolah output EXPLAIN secara programatis:

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders WHERE customer_id = 1042;

Output JSON lebih mudah di-parse oleh tools monitoring atau script otomasi.

Optimasi Query JOIN

Query JOIN sering menjadi sumber masalah performa. EXPLAIN ANALYZE sangat berguna di sini:

EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

Perhatikan jenis JOIN yang digunakan PostgreSQL:

• Nested Loop - efisien untuk result set kecil atau ketika salah satu sisi memiliki index.
• Hash Join - baik untuk join dua tabel besar tanpa index.
• Merge Join - optimal jika kedua sisi sudah ter-sort.

Jika kamu melihat Nested Loop pada tabel besar tanpa index, itu red flag. Tambahkan index pada kolom yang digunakan untuk JOIN.

-- Pastikan foreign key memiliki index
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Tips Praktis Sehari-hari

Gunakan pg_stat_statements

Extension ini mencatat semua query yang dijalankan beserta statistiknya:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Cari query paling lambat
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Rutin Jalankan VACUUM ANALYZE

PostgreSQL membutuhkan statistik tabel yang akurat untuk membuat query plan optimal:

-- Update statistik tabel tertentu
ANALYZE orders;

-- Atau vacuum sekaligus analyze
VACUUM ANALYZE orders;

Statistik yang outdated bisa membuat PostgreSQL memilih query plan yang buruk, bahkan ketika index sudah tersedia.

Hindari SELECT *

Selalu spesifikkan kolom yang dibutuhkan. Selain mengurangi transfer data, ini juga memungkinkan PostgreSQL menggunakan index-only scan:

-- Kurang optimal
SELECT * FROM orders WHERE customer_id = 1042;

-- Lebih optimal (bisa memanfaatkan covering index)
SELECT id, total, status FROM orders WHERE customer_id = 1042;

Checklist Optimasi Query PostgreSQL

Sebelum menutup artikel ini, berikut checklist yang bisa kamu gunakan setiap kali menghadapi query lambat:

1. Jalankan EXPLAIN ANALYZE pada query yang bermasalah
2. Periksa apakah ada Seq Scan pada tabel besar
3. Cek Rows Removed by Filter - angka besar berarti filter tidak efisien
4. Tambahkan index yang sesuai (single, composite, atau partial)
5. Verifikasi dengan EXPLAIN ANALYZE lagi setelah menambah index
6. Periksa apakah statistik tabel sudah ter-update (ANALYZE)
7. Monitor query lambat dengan pg_stat_statements

Kesimpulan

Optimasi query PostgreSQL bukan hal yang rumit jika kamu tahu cara membaca output EXPLAIN ANALYZE. Mulai dari mengidentifikasi Seq Scan, menambahkan index yang tepat, hingga memahami pola JOIN - semua bisa dilakukan secara sistematis.

Kunci utamanya adalah: selalu ukur sebelum dan sesudah optimasi. Jangan menebak-nebak. Biarkan EXPLAIN ANALYZE memberitahu kamu di mana masalahnya, dan buktikan bahwa solusi yang kamu terapkan benar-benar memberikan perbaikan performa.