Menguasai Query Execution Plan PostgreSQL: Panduan Lengkap untuk Optimasi Performa Database

Setiap developer yang bekerja dengan database, cepat atau lambat pasti akan menghadapi masalah performa query. Query yang lambat bukan hanya mengganggu pengalaman pengguna, tetapi juga bisa menjadi bottleneck serius dalam aplikasi. Di sinilah Query Execution Plan di PostgreSQL menjadi ‘mata’ kita untuk melihat apa yang sebenarnya terjadi di balik layar ketika database menjalankan sebuah perintah SQL.

Membaca dan memahami execution plan adalah skill fundamental bagi setiap software engineer, backend developer, maupun data engineer yang ingin membangun aplikasi yang cepat dan responsif. Ini bukan sekadar mengetahui sintaks EXPLAIN ANALYZE, tetapi lebih kepada kemampuan menginterpretasi setiap node, angka, dan metrik untuk menemukan akar masalah performa.

Artikel ini akan memandu Anda secara mendalam tentang cara membaca Query Execution Plan di PostgreSQL, mulai dari dasar hingga strategi optimasi praktis. Kita akan bedah perintah EXPLAIN ANALYZE, memahami setiap komponen outputnya, mengenali pola-pola masalah, dan tentunya, bagaimana cara memperbaikinya.

Apa Itu Query Execution Plan?

Sebelum database menjalankan sebuah query, ia akan melewati proses yang disebut sebagai “Query Planner” atau “Query Optimizer”. Tugas dari planner ini adalah menentukan cara terbaik untuk mengambil data yang diminta oleh query. Ada banyak cara untuk melakukan hal yang sama – misalnya, apakah harus menggunakan indeks, atau melakukan scan seluruh tabel, atau melakukan join dengan metode tertentu. Setiap metode memiliki biaya (cost) yang berbeda.

Query Execution Plan adalah representasi visual atau tekstual dari langkah-langkah yang akan (atau sudah) diambil oleh PostgreSQL untuk menjalankan query Anda. Ini seperti peta jalan yang menunjukkan rute terbaik yang dipilih oleh planner, lengkap dengan perkiraan waktu, jumlah baris, dan sumber daya yang akan digunakan.

Dengan membaca plan ini, kita bisa:

• Mengidentifikasi bagian query mana yang paling lambat.
• Melihat apakah indeks digunakan secara efektif.
• Memahami bagaimana tabel di-join.
• Mendeteksi anomali atau kesalahan estimasi oleh planner.

Mengenal Perintah EXPLAIN dan EXPLAIN ANALYZE

Di PostgreSQL, kita menggunakan perintah EXPLAIN untuk melihat execution plan. Ada dua varian utama yang sering digunakan:

1. EXPLAIN

EXPLAIN tanpa ANALYZE akan menunjukkan perkiraan plan. Artinya, PostgreSQL akan melakukan perencanaan query tanpa benar-benar menjalankannya. Ini berguna jika Anda ingin melihat potensi plan tanpa memodifikasi data atau menunggu query berjalan lama. Namun, karena ini hanya perkiraan, angka-angka yang ditunjukkan (misalnya, jumlah baris, biaya) mungkin tidak selalu akurat.

Contoh penggunaan:

EXPLAIN SELECT * FROM users WHERE age > 30;

2. EXPLAIN ANALYZE

Ini adalah alat favorit para developer untuk debugging performa. EXPLAIN ANALYZE akan menjalankan query Anda, mengumpulkan statistik aktual, dan kemudian menampilkan plan bersama dengan metrik waktu dan baris yang sebenarnya. Ini memberikan gambaran yang jauh lebih akurat tentang apa yang terjadi saat query dijalankan di lingkungan nyata. Perlu diingat, karena query dijalankan, ada potensi perubahan data jika query tersebut adalah INSERT, UPDATE, atau DELETE. Untuk query SELECT, ini aman digunakan.

Contoh penggunaan:

EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Electronics' AND price < 500 ORDER BY created_at DESC;

Selain itu, ada beberapa opsi lain yang bisa ditambahkan ke EXPLAIN atau EXPLAIN ANALYZE untuk mendapatkan detail lebih lanjut:

• EXPLAIN (ANALYZE, VERBOSE): Menampilkan informasi lebih detail tentang setiap node, seperti nama kolom dan ekspresi yang digunakan.
• EXPLAIN (ANALYZE, COSTS): Menampilkan perkiraan biaya (ini adalah default).
• EXPLAIN (ANALYZE, BUFFERS): Menampilkan informasi tentang penggunaan buffer memori dan I/O disk. Ini sangat berguna untuk melihat apakah query melakukan banyak pembacaan disk atau memanfaatkan cache dengan baik.
• EXPLAIN (ANALYZE, WAL): Menampilkan informasi tentang Write-Ahead Log (WAL) untuk operasi DML (INSERT, UPDATE, DELETE).
• EXPLAIN (ANALYZE, TIMING): Menampilkan waktu aktual untuk setiap node (ini adalah default untuk ANALYZE).
• EXPLAIN (ANALYZE, SUMMARY): Menampilkan ringkasan total waktu perencanaan dan eksekusi (ini adalah default untuk ANALYZE).

Kombinasi yang paling sering saya gunakan adalah EXPLAIN (ANALYZE, BUFFERS, VERBOSE) karena memberikan detail yang cukup komprehensif.

Anatomi Output EXPLAIN ANALYZE

Output dari EXPLAIN ANALYZE mungkin terlihat rumit pada awalnya, tetapi setelah Anda memahami komponen-komponen utamanya, itu akan menjadi sangat informatif. Mari kita bedah bagian-bagian penting:

1. Node Tree (Pohon Node)

Plan ditampilkan sebagai struktur pohon. Setiap “node” merepresentasikan sebuah operasi database (misalnya, scan tabel, join, sort, aggregate). Operasi paling bawah (indentasi paling dalam) adalah yang pertama kali dieksekusi, dan hasilnya kemudian diteruskan ke node di atasnya.

2. Cost (Biaya)

cost=start_cost..total_cost: Ini adalah perkiraan biaya yang dihitung oleh Query Planner.

• start_cost: Biaya yang dibutuhkan untuk mendapatkan baris pertama dari node tersebut.
• total_cost: Biaya total untuk mendapatkan semua baris dari node tersebut.

Biaya ini adalah unit arbitrer yang relatif, bukan dalam milidetik. Ini berguna untuk membandingkan efisiensi relatif dari strategi yang berbeda.

3. Rows (Baris)

rows=N: Ini adalah perkiraan jumlah baris yang akan dihasilkan oleh node tersebut. Dalam output ANALYZE, akan ada juga actual rows=N yang menunjukkan jumlah baris aktual yang dihasilkan.

4. Width (Lebar)

width=N: Ini adalah perkiraan lebar rata-rata (dalam byte) dari baris yang dihasilkan oleh node tersebut. Ini membantu planner memperkirakan penggunaan memori.

5. Actual Time (Waktu Aktual)

actual time=time_start..time_end: Ini adalah metrik paling penting dari ANALYZE.

• time_start: Waktu rata-rata (dalam milidetik) yang dibutuhkan untuk mendapatkan baris pertama dari node tersebut.
• time_end: Waktu rata-rata (dalam milidetik) yang dibutuhkan untuk mendapatkan semua baris dari node tersebut.

Fokuslah pada node yang memiliki time_end tinggi, karena ini menunjukkan di mana sebagian besar waktu eksekusi query Anda dihabiskan.

6. Loops

loops=N: Menunjukkan berapa kali node ini dieksekusi. Untuk scan tabel tunggal, ini biasanya 1. Untuk operasi di dalam join seperti Nested Loop Join, ini bisa jadi lebih dari 1, menunjukkan berapa kali tabel bagian dalam di-scan atau di-index scan.

7. Planning Time dan Execution Time

Di bagian paling bawah output ANALYZE, Anda akan melihat:

• Planning Time: X.XXX ms: Waktu yang dibutuhkan Query Planner untuk memutuskan plan terbaik. Jika ini sangat tinggi (ratusan milidetik atau lebih), mungkin ada masalah dengan statistik database atau kompleksitas query yang berlebihan.
• Execution Time: Y.YYY ms: Total waktu aktual yang dibutuhkan untuk menjalankan query setelah plan diputuskan. Inilah yang biasanya kita coba optimalkan.

8. Buffers (dengan opsi BUFFERS)

Jika Anda menggunakan EXPLAIN (ANALYZE, BUFFERS), Anda akan melihat detail tentang penggunaan buffer, seperti:

• shared hit: Jumlah halaman yang ditemukan di shared_buffers (cache memori PostgreSQL).
• shared read: Jumlah halaman yang harus dibaca dari disk dan dimasukkan ke shared_buffers.
• local hit, local read, temp read, temp write: Statistik serupa untuk buffer lokal atau file sementara.

Banyak shared read menunjukkan I/O disk yang tinggi, yang seringkali menjadi indikasi kurangnya indeks atau data yang terlalu besar untuk cache.

Node-node Penting dalam Plan yang Perlu Diketahui

Memahami jenis node adalah kunci untuk menginterpretasi plan. Berikut adalah beberapa yang paling umum:

1. Scan Operations

• Seq Scan (Sequential Scan): Database membaca seluruh tabel dari awal hingga akhir. Ini adalah operasi yang sangat mahal pada tabel besar, terutama jika hanya mencari sebagian kecil data. Biasanya, ini menandakan kurangnya indeks atau indeks yang tidak dapat digunakan.
• Index Scan: Database menggunakan indeks untuk menemukan baris yang relevan. Jauh lebih cepat daripada Seq Scan jika kondisi WHERE cocok dengan kolom yang diindeks.
• Index Only Scan: Ini adalah varian Index Scan yang sangat efisien. Database dapat mengambil semua data yang dibutuhkan hanya dari indeks, tanpa perlu mengakses tabel utama sama sekali. Ini terjadi jika semua kolom yang diminta dalam SELECT dan WHERE sudah ada di indeks.
• Bitmap Index Scan / Bitmap Heap Scan: Digunakan ketika ada beberapa kondisi WHERE yang menggunakan indeks berbeda, dan PostgreSQL memutuskan untuk menggabungkannya. Pertama, Bitmap Index Scan akan membuat bitmap (peta lokasi) dari baris yang cocok dari setiap indeks. Kemudian, Bitmap Heap Scan akan menggunakan bitmap tersebut untuk mengambil baris dari tabel utama. Ini lebih efisien daripada melakukan banyak Index Scan terpisah.

2. Join Operations

• Nested Loop Join: Untuk setiap baris dari tabel luar, PostgreSQL melakukan scan atau index scan pada tabel dalam. Efisien untuk tabel kecil atau ketika tabel dalam dapat diakses dengan cepat melalui indeks. Sangat mahal untuk tabel besar jika tabel dalam tidak memiliki indeks yang sesuai.
• Hash Join: PostgreSQL membangun tabel hash dari tabel yang lebih kecil (tabel “build”) di memori, lalu melakukan scan tabel yang lebih besar (tabel “probe”) dan mencari kecocokan di tabel hash. Efisien untuk join tabel besar tanpa indeks yang cocok. Membutuhkan memori yang cukup.
• Merge Join: Kedua tabel diurutkan berdasarkan kolom join, lalu digabungkan seperti proses penggabungan dua daftar yang sudah terurut. Efisien jika kedua tabel sudah terurut (misalnya, karena indeks) atau jika cost untuk mengurutkan lebih rendah dari Hash Join.

3. Sort Operations

• Sort: Database perlu mengurutkan hasil query, biasanya karena ada klausa ORDER BY, GROUP BY, atau DISTINCT yang tidak dapat di-handle oleh indeks. Operasi ini bisa sangat mahal jika jumlah baris yang diurutkan banyak dan tidak muat di memori (`work_mem`), menyebabkan penggunaan disk sementara.

4. Aggregate Operations

• Aggregate: Digunakan untuk fungsi agregat seperti SUM(), COUNT(), AVG(), biasanya dalam kombinasi dengan GROUP BY.

5. Filter

• Filter: (condition): Menunjukkan kondisi WHERE atau HAVING yang diterapkan pada baris yang telah diambil oleh node sebelumnya. Jika banyak baris di-filter setelah scan mahal (misalnya Seq Scan), ini menunjukkan bahwa kondisi tersebut tidak dapat memanfaatkan indeks.
• Rows Removed by Filter: N: Jumlah baris yang dibuang oleh filter. Jika angka ini tinggi setelah sebuah scan, artinya banyak data yang tidak relevan harus dibaca sebelum akhirnya dibuang.

6. Materialize

• Materialize: Database menyimpan hasil dari sebuah subquery atau CTE (Common Table Expression) ke memori sementara atau disk, agar dapat digunakan berkali-kali tanpa perlu dieksekusi ulang. Bisa jadi optimasi yang bagus, tapi juga bisa jadi mahal jika datanya terlalu besar.

Langkah-langkah Praktis Membaca Query Execution Plan

Membaca plan adalah seni sekaligus sains. Berikut adalah alur kerja yang saya gunakan:

1. Jalankan Query dengan EXPLAIN ANALYZE

Selalu gunakan EXPLAIN (ANALYZE, BUFFERS, VERBOSE) untuk mendapatkan data aktual dan detail I/O. Jangan hanya menggunakan EXPLAIN saja.

2. Mulai dari Node Paling Bawah (Paling Dalam)

Plan dibaca dari dalam ke luar, atau dari bawah ke atas. Operasi yang paling awal dieksekusi adalah yang paling indentasi ke dalam. Identifikasi operasi paling dasar yang memakan waktu.

3. Identifikasi Node dengan Actual Time Tertinggi

Cari node yang memiliki actual time (khususnya time_end) yang sangat tinggi. Ini adalah bottleneck utama Anda. Biasanya, node ini akan menunjukkan:

• Seq Scan pada tabel besar: Ini adalah lampu merah terbesar. Artinya, database harus membaca seluruh tabel, padahal mungkin hanya butuh sebagian kecil data. Solusinya seringkali adalah indeks.
• Sort: Jika Sort memakan waktu lama, artinya ada banyak data yang perlu diurutkan di memori atau bahkan tumpah ke disk sementara. Indeks bisa membantu jika ORDER BY cocok dengan indeks.
• Hash Join / Merge Join pada tabel yang sangat besar: Meskipun efisien, jika data terlalu besar, proses pembuatan hash table atau pengurutan bisa jadi mahal.
• Nested Loop Join dengan tabel bagian dalam yang tidak terindeks: Jika loops pada tabel luar tinggi dan tabel bagian dalam di-Seq Scan berkali-kali, ini sangat buruk.

4. Bandingkan Rows (Perkiraan vs Aktual)

Perhatikan rows=X (perkiraan) vs actual rows=Y (aktual).

• Jika ada perbedaan besar (perkiraan jauh lebih rendah atau lebih tinggi dari aktual): Ini menunjukkan bahwa Query Planner memiliki informasi statistik yang buruk. PostgreSQL mungkin memilih plan yang salah karena estimasinya jauh meleset. Solusi: jalankan ANALYZE TABLE_NAME; atau VACUUM ANALYZE TABLE_NAME; untuk memperbarui statistik.
• Perkiraan rendah, aktual tinggi: Planner mungkin mengharapkan hanya sedikit baris, sehingga memilih Nested Loop Join. Tapi karena aktualnya tinggi, Nested Loop Join jadi lambat.

5. Perhatikan “Rows Removed by Filter”

Jika Anda melihat Filter: (condition) dan Rows Removed by Filter: N dengan angka N yang besar, ini berarti banyak baris harus dibaca terlebih dahulu sebelum dibuang oleh filter. Ini sering terjadi setelah Seq Scan yang mahal. Solusinya adalah membuat indeks yang mencakup kondisi filter tersebut agar baris yang tidak relevan tidak perlu dibaca sama sekali.

6. Analisis Penggunaan Buffers (dengan opsi BUFFERS)

Jika Anda melihat banyak shared read, ini berarti banyak data harus dibaca dari disk. Ini menunjukkan bahwa data tidak ada di cache memori (`shared_buffers`) atau tidak ada indeks yang membantu mengurangi jumlah data yang perlu dibaca. Mengurangi shared read adalah salah satu tujuan utama optimasi.

7. Evaluasi Join Strategy

Jenis join yang dipilih sangat memengaruhi performa.

• Jika ada Nested Loop Join pada tabel besar dan tabel bagian dalam di-Seq Scan, ini tanda bahaya. Pastikan tabel bagian dalam memiliki indeks pada kolom join.
• Hash Join dan Merge Join umumnya lebih baik untuk tabel besar. Jika Hash Join tumpah ke disk karena kurangnya memori (`work_mem`), coba tingkatkan work_mem di konfigurasi PostgreSQL.

Strategi Optimasi Berdasarkan Plan

Setelah mengidentifikasi bottleneck, langkah selanjutnya adalah mengimplementasikan solusi:

1. Membuat Indeks yang Tepat

Ini adalah optimasi paling umum dan seringkali paling efektif.

• Missing Index: Jika Anda melihat Seq Scan pada tabel besar di node yang mahal, dan query Anda memiliki klausa WHERE atau ORDER BY pada kolom tertentu, buatlah indeks pada kolom tersebut.

  CREATE INDEX idx_nama_kolom ON nama_tabel (nama_kolom);

• Composite Index: Jika ada beberapa kondisi di WHERE (misalnya, WHERE A = X AND B = Y), pertimbangkan indeks komposit:

  CREATE INDEX idx_nama_kolom_a_b ON nama_tabel (kolom_a, kolom_b);

  Pastikan urutan kolom dalam indeks sesuai dengan kondisi di WHERE.

• Index Only Scan Optimization: Jika Anda hanya memilih kolom yang sudah ada di indeks, Anda bisa mendapatkan Index Only Scan yang sangat cepat. Pertimbangkan menambahkan kolom yang sering di-SELECT ke indeks (misalnya, dengan klausa INCLUDE di PostgreSQL 11+).

  CREATE INDEX idx_nama_kolom ON nama_tabel (kolom_filter) INCLUDE (kolom_select_lain);

2. Mengoptimalkan JOIN

• Indeks pada Kolom JOIN: Pastikan kolom yang digunakan dalam klausa ON pada join memiliki indeks. Ini sangat membantu Nested Loop Join dan Merge Join.
• Urutan JOIN: Meskipun PostgreSQL planner biasanya pintar, terkadang ia bisa salah. Dalam kasus tertentu, menulis ulang query untuk “memaksa” urutan join tertentu (misalnya, menggunakan SET join_collapse_limit = 1; atau menggunakan CTE) bisa membantu.

3. Menulis Ulang Query

• Hindari SELECT *: Hanya pilih kolom yang benar-benar Anda butuhkan. Ini mengurangi lebar baris (width) dan data yang perlu di-transfer.
• Gunakan CTE (Common Table Expressions) secara bijak: CTE bisa membuat query lebih mudah dibaca, tapi kadang bisa menambah kompleksitas plan. Bandingkan plan dengan subquery vs CTE.
• Hindari Fungsi di Klausa WHERE: Menggunakan fungsi pada kolom yang diindeks (misalnya, WHERE lower(kolom) = 'abc') akan mencegah penggunaan indeks pada kolom tersebut. Alih-alih, terapkan fungsi pada nilai yang dibandingkan (misalnya, WHERE kolom = lower('ABC')) atau gunakan functional index.

4. Menganalisis dan Memperbarui Statistik

Jika ada perbedaan besar antara perkiraan rows dan actual rows, kemungkinan statistik database Anda sudah usang.

ANALYZE TABLE_NAME; atau VACUUM ANALYZE TABLE_NAME; akan memperbarui statistik untuk tabel tersebut, sehingga Query Planner memiliki informasi yang lebih akurat untuk membuat plan.

5. Penyesuaian Konfigurasi PostgreSQL

Beberapa parameter konfigurasi PostgreSQL dapat memengaruhi Query Planner dan Executor.

• shared_buffers: Ukuran cache memori utama untuk data database. Meningkatkannya dapat mengurangi I/O disk (lebih banyak shared hit).
• work_mem: Memori yang digunakan untuk operasi sort, hash join, dan operasi sementara lainnya per sesi. Meningkatkannya dapat mencegah operasi ini tumpah ke disk.
• random_page_cost dan seq_page_cost: Biaya relatif untuk membaca halaman secara acak vs. sekuensial. Menyesuaikannya dapat memengaruhi preferensi planner terhadap Index Scan atau Seq Scan.

Perubahan konfigurasi harus dilakukan dengan hati-hati dan setelah pengujian menyeluruh.

Masalah yang Sering Terjadi

Sebagai developer, beberapa masalah performa yang sering muncul saat membaca execution plan adalah:

1. Missing Indexes

Gejala: Seq Scan pada tabel yang sangat besar dengan actual time tinggi, padahal ada klausa WHERE yang sangat selektif.
Penyebab: Tidak ada indeks pada kolom yang digunakan dalam klausa WHERE, atau indeks yang ada tidak cocok.
Solusi: Buat indeks yang sesuai pada kolom tersebut. Contoh: CREATE INDEX ON tabel (kolom_filter);

2. Inefficient Joins

Gejala: Nested Loop Join dengan loops tinggi dan tabel bagian dalam yang di-Seq Scan berulang kali, atau Hash Join yang tumpah ke disk (ada temp write yang signifikan di Buffers).
Penyebab: Kolom join tidak terindeks, atau memori work_mem tidak cukup untuk Hash Join.
Solusi: Buat indeks pada kolom join. Jika Hash Join, coba tingkatkan nilai work_mem di konfigurasi PostgreSQL. Pertimbangkan juga untuk menulis ulang join jika ada kompleksitas yang tidak perlu.

3. Bad Statistics / Outdated Statistics

Gejala: Perbedaan yang sangat mencolok antara rows (perkiraan) dan actual rows pada node-node penting. Ini seringkali menyebabkan planner memilih strategi yang kurang optimal.
Penyebab: Statistik distribusi data di tabel tidak akurat atau sudah usang karena banyak perubahan data (INSERT, UPDATE, DELETE).
Solusi: Jalankan ANALYZE TABLE_NAME; atau VACUUM ANALYZE TABLE_NAME; secara berkala. Pastikan autovacuum juga berjalan dengan baik.

4. Excessive Sorting

Gejala: Node Sort dengan actual time yang tinggi, terutama jika ada indikasi temp write yang banyak.
Penyebab: Query memerlukan pengurutan data (ORDER BY, GROUP BY) yang tidak dapat dipenuhi oleh indeks yang ada, dan jumlah data yang diurutkan melebihi work_mem.
Solusi: Buat indeks yang mencakup kolom yang diurutkan. Atau, jika tidak memungkinkan, tingkatkan work_mem (jika resource tersedia) untuk mencegah tumpahan ke disk.

5. Filtering Data yang Terlalu Banyak

Gejala: Banyak Rows Removed by Filter setelah scan mahal seperti Seq Scan. Ini berarti database membaca banyak data yang tidak perlu sebelum membuangnya.
Penyebab: Kondisi filter tidak memanfaatkan indeks yang ada, atau filter diaplikasikan pada hasil dari operasi yang sudah mahal.
Solusi: Pastikan ada indeks yang mencakup kondisi filter, terutama jika filter tersebut sangat selektif. Evaluasi kembali urutan operasi jika memungkinkan.

Pengalaman dan Pertimbangan Praktis

Dalam praktik sehari-hari sebagai software engineer, optimasi query bukan hanya tentang membuat query secepat mungkin, tetapi juga tentang menemukan keseimbangan. Beberapa hal penting yang sering saya temui:

• Tidak semua query lambat perlu dioptimasi secara ekstrem. Fokus pada query yang paling sering dieksekusi atau yang paling memengaruhi pengalaman pengguna. Optimasi yang berlebihan bisa memakan waktu pengembangan yang tidak sebanding dengan manfaatnya.
• Indeks memiliki trade-off. Semakin banyak indeks, semakin cepat operasi baca (SELECT), tetapi semakin lambat operasi tulis (INSERT, UPDATE, DELETE) karena indeks juga harus diperbarui. Buatlah indeks secukupnya, yang benar-benar dibutuhkan oleh query penting.
• Selalu uji di lingkungan staging. Jangan pernah langsung menerapkan perubahan optimasi (terutama penambahan indeks atau perubahan konfigurasi) langsung di lingkungan produksi tanpa pengujian yang memadai. Plan bisa berbeda di lingkungan produksi karena data yang berbeda.
• Gunakan visualizer. Ada banyak tools yang bisa membantu memvisualisasikan execution plan (misalnya, explain.dalibo.com atau ekstensi pganalyze). Ini membuat plan lebih mudah dibaca daripada teks mentah.
• Belajar dari pola. Seiring waktu, Anda akan mulai mengenali pola-pola umum masalah performa dan solusi yang efektif. Catat dan pelajari dari setiap kasus optimasi yang Anda lakukan.
• Pahami konteks data. Pengetahuan tentang distribusi data di tabel Anda (berapa banyak baris, seberapa unik nilai di sebuah kolom, dll.) sangat membantu dalam memahami mengapa planner memilih plan tertentu.

FAQ

Apa bedanya EXPLAIN dan EXPLAIN ANALYZE?

EXPLAIN hanya menunjukkan perkiraan rencana eksekusi tanpa menjalankan query, sedangkan EXPLAIN ANALYZE benar-benar menjalankan query, mengumpulkan statistik waktu dan baris aktual, lalu menampilkannya bersama rencana.

Kenapa EXPLAIN ANALYZE saya tidak menunjukkan indeks yang saya buat?

Ada beberapa kemungkinan:

1. Indeks yang dibuat tidak sesuai dengan kondisi WHERE atau ORDER BY di query.
2. Tabel terlalu kecil, sehingga Seq Scan dianggap lebih cepat oleh Query Planner.
3. Statistik database usang, sehingga planner tidak tahu indeks tersebut ada atau efektif. Coba jalankan ANALYZE TABLE_NAME;
4. Ada fungsi pada kolom yang diindeks dalam klausa WHERE, yang mencegah penggunaan indeks.

Apa itu “cost” dalam execution plan?

“Cost” adalah unit arbitrer yang digunakan oleh Query Planner untuk membandingkan biaya relatif dari berbagai rencana eksekusi. Ini bukan waktu dalam milidetik, melainkan metrik internal yang menggabungkan faktor-faktor seperti I/O disk, CPU, dan memori. Semakin rendah cost, semakin baik estimasi plan tersebut.

Bisakah saya menggunakan EXPLAIN ANALYZE untuk query INSERT/UPDATE/DELETE?

Bisa, tetapi hati-hati! EXPLAIN ANALYZE akan benar-benar menjalankan operasi DML tersebut, yang berarti data di database Anda akan berubah. Untuk menguji DML tanpa perubahan data, Anda bisa membungkusnya dalam transaksi dan kemudian melakukan ROLLBACK:

BEGIN; EXPLAIN ANALYZE INSERT INTO ...; ROLLBACK;

Berapa nilai work_mem yang ideal?

Nilai work_mem sangat tergantung pada spesifikasi server dan beban kerja database Anda. Tidak ada satu nilai “ideal” yang cocok untuk semua orang. Terlalu rendah akan menyebabkan operasi tumpah ke disk, terlalu tinggi akan memakan terlalu banyak memori per sesi dan bisa menyebabkan OOM (Out Of Memory). Mulailah dengan nilai yang wajar (misalnya 64MB atau 128MB) dan pantau penggunaan sumber daya server saat Anda meningkatkannya. Selalu uji setelah perubahan.

Kesimpulan

Membaca Query Execution Plan di PostgreSQL adalah salah satu kemampuan paling berharga bagi siapa pun yang serius dalam mengembangkan aplikasi berbasis data. Ini memberikan transparansi tentang bagaimana database Anda bekerja dan mengarahkan Anda langsung ke akar masalah performa.

Proses ini mungkin terasa menantang di awal, dengan banyaknya istilah dan angka yang harus diinterpretasi. Namun, dengan latihan dan pemahaman yang sistematis, Anda akan segera mampu mengidentifikasi bottleneck, menerapkan strategi optimasi yang tepat, dan membangun sistem yang lebih cepat dan efisien. Ingat, kuncinya adalah konsistensi, eksperimen, dan selalu belajar dari setiap plan yang Anda analisis. Selamat mengoptimasi!

TAGS: PostgreSQL, Query Plan, EXPLAIN ANALYZE, Optimasi Database, Performa SQL, Database Performance, Indeks, SQL, Developer Tools, Backend Engineering

---

Baca Juga

• Panduan Lengkap Belajar Coding untuk Pemula hingga Profesional