Output SQL EXPLAIN Tidak Perlu Terlihat Seperti Teks Asing

Kamu menjalankan EXPLAIN pada query lambat. Database mengembalikan sesuatu seperti ini:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000

Kamu memandangnya selama sepuluh detik. Kamu menambahkan indeks secara acak. Kamu memulai ulang aplikasi dan berharap. Itu bukan strategi debugging — itu kepercayaan yang ditambahkan langkah-langkah.

EXPLAIN sebenarnya memberi tahu kamu sesuatu yang spesifik dan dapat diambil tindakan. Berikut cara membacanya.

EXPLAIN vs EXPLAIN ANALYZE: Mana yang harus digunakan

Polos EXPLAIN menunjukkan apa yang dimaksudkan untuk dilakukan. Ini tidak menjalankan query — hanya menampilkan rencana dengan biaya yang diestimasi. Cepat, tetapi estimasi bisa salah.

EXPLAIN ANALYZE sebenarnya menjalankan query dan menambahkan waktu nyata. Kamu mendapatkan baik rencana maupun data aktual:

-- PostgreSQL: run the query and show real timings
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

-- MySQL equivalent
EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

Itu BUFFERS opsi ini hanya tersedia di PostgreSQL dan menambahkan jumlah hit/kegagalan buffer — berguna untuk mendiagnosis masalah I/O, tetapi abaikan ini saat ini.

Satu peringatan: EXPLAIN ANALYZE melakukan query secara nyata. Pada DELETE atau UPDATE, sarankan untuk menutupnya dalam transaksi dan membatalkan:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

Angka biaya: apa artinya dan apa yang tidak dimaksudkan

Setiap node dalam rencana menunjukkan (cost=X..Y rows=N width=W). Orang-orang melihat angka ini dan menganggapnya sebagai milidetik. Tidak benar.

• cost=X..Y — X adalah biaya startup (kerja yang dilakukan sebelum baris pertama dikembalikan), Y adalah biaya total untuk memproses semua baris. Satuan ini adalah "biaya halaman" yang sembarang — sebanding dengan pembacaan halaman disk, tetapi disesuaikan oleh konstanta biaya PostgreSQL. Biaya 1.0 adalah satu pembacaan halaman secara berurutan.
• rows=N — jumlah baris yang diestimasi oleh rencana. Bisa sangat salah jika statistik tabel sudah usang.
• width=W — ukuran rata-rata baris dalam byte. Ukuran baris yang lebih besar memperlambat join dan sort.

Ketika kamu menjalankan EXPLAIN ANALYZE, setiap node juga mendapatkan (actual time=X..Y rows=N loops=L). Ini konsisten — secara geometris. Bandingkan dengan milidetik. Perbedaan besar antara jumlah yang diestimasi dan aktual adalah sinyal paling andal bahwa rencana membuat keputusan yang buruk.

Panduan langkah demi langkah untuk EXPLAIN ANALYZE yang diberi annotasi

Berikut ini adalah query yang sama dari bagian awal, kali ini dengan ANALYZE dan setiap baris dijelaskan:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
           (actual time=2.451..12.879 rows=3168 loops=1)
-- Top-level node. Joins orders and customers via hashing.
-- cost estimate: 145..578 | actual: 2.4ms startup, 12.9ms total
-- rows estimate: 3200 | actual: 3168 — pretty close here

  Hash Cond: (o.customer_id = c.id)
  -- This is the join key. The planner built a hash table on customers.id
  -- then probed it with every row from orders.

  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
                          (actual time=0.019..3.125 rows=16000 loops=1)
  -- Full table scan on orders. 16,000 rows scanned.
  -- No filter here — we pull all orders and join them below.
  -- This is expected: we need all orders, so no index would help here.

  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
            (actual time=2.412..2.413 rows=1000 loops=1)
            Buckets: 1024  Batches: 1  Memory Usage: 64kB
  -- Builds the in-memory hash table from the customers result.
  -- 1 batch = fits in work_mem. Multiple batches = spilling to disk (bad).

        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
                                   (actual time=0.012..1.345 rows=1000 loops=1)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000
        -- Scanned all 10,000 customer rows. Kept 1,000 where country='US'.
        -- *** This is the expensive part. An index on customers.country
        --     would let PostgreSQL skip the 9,000 discarded rows entirely. ***

Planning Time: 0.187 ms
Execution Time: 13.451 ms

Tindakan yang harus dilakukan langsung terlihat: Rows Removed by Filter: 9000 pada pemindaian tabel penuh adalah pola paling umum untuk "kamu butuh indeks di sini."

Jenis node yang akan kamu temui sebenarnya

Seq Scan

Membaca setiap baris dalam tabel, dalam urutan heap. Orang melihat ini dan langsung ingin menambahkan indeks. Tetapi Seq Scan tidak selalu salah — jika filternya cocok dengan 20%+ dari baris, rencana secara tepat memutuskan bahwa pencarian acak dengan indeks akan lebih lambat. Seq Scan pada tabel 500 baris adalah baik. Seq Scan pada tabel 10-juta baris dengan filter yang sangat selektif adalah masalah.

Index Scan

Menggunakan indeks B-tree untuk mencari baris yang sesuai, lalu mengambil setiap baris dari heap. Bagian pengambilan dari heap adalah bagian yang mahal — setiap baris adalah akses acak. Baik digunakan ketika filternya selektif (yaitu, kamu mengambil fraksi kecil dari baris).

Index Only Scan

Pertanyaan dapat dijawab sepenuhnya dari indeks tanpa menyentuh heap. Ini memerlukan bahwa setiap kolom dalam SELECT dan WHERE klausul ditanggung oleh indeks. Jenis scan yang paling cepat — jika kamu melihat ini, desain indeks sedang bekerja dengan baik.

Bitmap Heap Scan

Sebuah titik tengah. Rencana membangun peta bit dari halaman heap mana yang mengandung baris yang sesuai (melalui Bitmap Index Scan), lalu hanya mengambil halaman-halaman tersebut secara berurutan, mengurangi akses acak. Umum terjadi ketika Index Scan akan memiliki terlalu banyak akses acak tetapi Seq Scan akan memindai terlalu banyak baris yang tidak perlu.

Hash Join

Membangun tabel hash dari input yang lebih kecil, lalu memproyeksinya dengan setiap baris dari input yang lebih besar. Baik digunakan untuk join besar tanpa urutan sort yang berguna. Perhatikan Batches: > 1 di node Hash — itu berarti tabel hash melebihi work_mem. Meningkatkan work_mem untuk sesi tersebut sering kali memperbaikinya.

Nested Loop

Untuk setiap baris di sisi luar, memproyeksinya ke sisi dalam (biasanya melalui indeks). Sangat baik ketika satu sisi sangat kecil — O(n) daripada O(n log n). Buruk ketika kedua sisi besar, karena menjalankan skan dalam sekali per baris luar. Jika kamu melihat Nested Loop dengan loops=50000, maka skan dalam berjalan 50.000 kali.

Merge Join

Kedua input tiba dalam urutan terurut berdasarkan kunci join; rencana berjalan secara paralel. Efisien tetapi membutuhkan pengurutan sebelumnya. Kamu akan melihat ini ketika kedua sisi sudah memiliki indeks pada kunci join, atau ketika rencana memutuskan bahwa node Sort lebih murah daripada hashing.

Mengapa rencana memilih Seq Scan daripada Index Scan

Ini yang paling membingungkan orang. Kamu memiliki indeks. Query lambat. EXPLAIN menunjukkan Seq Scan saja.

Rencana menggunakan statistik kolom — disimpan dalam pg_statistic, diperbarui oleh ANALYZE — untuk memperkirakan berapa banyak baris filter akan mengembalikan. Jika diperkirakan 30% dari tabel akan cocok, Seq Scan benar-benar lebih murah daripada 300.000 pencarian acak ke indeks. Akses acak sangat mahal.

Ambang batas di mana rencana memilih Seq Scan adalah sekitar 10–20% selektivitas, tergantung pada perangkat keras dan konstanta biaya Anda. Kamu bisa memeriksa perkiraan rencana dibandingkan dengan realitas:

-- Check the planner's selectivity estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Compare with actual count
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders;

Jika rencana memperkirakan 8.000 baris tetapi hanya ada 80, itu adalah masalah statistik yang usang. Jalankan ANALYZE orders; dan periksa kembali rencana. Ini memperbaiki rencana buruk lebih sering daripada menambahkan indeks.

Membaca output EXPLAIN dalam format JSON

PostgreSQL dapat menghasilkan rencana dalam format JSON, yang lebih mudah diproses secara programatis atau dieksplorasi dalam tampilan pohon:

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

Output JSON sangat padat. Jika kamu menempelkannya ke IO Tools’ JSON Formatter, maka menjadi pohon yang dapat diperiksa — berguna ketika menghadapi rencana yang sangat terkandung dari query kompleks dengan banyak subquery atau CTE.

Alur debugging praktis

Ketika query lambat, ikuti urutan ini daripada menebak:

1. sebelum menambahkan indeks apa pun. Pastikan masalahnya terlebih dahulu. EXPLAIN (ANALYZE, BUFFERS) pada query yang tepat dengan parameter produksi nyata (bukan $1 placeholder — gunakan nilai aktual).
2. Temukan node yang paling mahal. Cari nilai yang paling tinggi actual time, bukan perkiraan biaya teratas. Keduanya bisa berbeda.
3. Bandingkan jumlah yang diestimasi dengan jumlah aktual pada node tersebut. Discrepansi 10x atau lebih berarti rencana bekerja dengan informasi yang buruk. Jalankan ANALYZE <table>; terlebih dahulu.
4. Cari Seq Scan pada tabel besar dengan filter selektif. Rows Removed by Filter: <large number> di bawah Seq Scan adalah kandidat indeksmu.
5. Periksa node Hash untuk Batches > 1. Jika ada, join telah spill ke disk. Tingkatkan work_mem untuk sesi tersebut dan uji ulang.
6. Periksa Nested Loop dengan jumlah loop tinggi. Jumlah loop dalam ribuan berarti skan dalam sedang terbebani. Indeks pada kolom join di tabel dalam biasanya memperbaikinya.

Sebelum menulis indeks baru, juga jalankan querymu melalui IO Tools’ SQL Formatter — query yang mudah dibaca lebih mudah dianalisis, dan kadang-kadang penulisan ulang menghilangkan masalah secara total.

Memahami rencana adalah langkah pertama, bukan indeks. Setelah kamu bisa membaca output EXPLAIN, kamu akan menghabiskan waktu lebih sedikit untuk menebak dan lebih banyak waktu untuk membuat perubahan yang benar-benar efektif.

Anda mungkin juga menyukai