EXPLAIN menampilkan rencana di atas kertas: perkiraan biaya, perkiraan baris, perkiraan lebar — semuanya hasil pemodelan planner. Perkiraan bisa salah, dan rencana yang dibangun di atas perkiraan yang salah bisa jauh lebih buruk dari yang dibayangkan. Untuk membandingkan perkiraan dengan kenyataan, PostgreSQL menyediakan EXPLAIN ANALYZE: perintah yang benar-benar menjalankan query, lalu mencetak rencana yang sama dengan tambahan data lapangan — waktu nyata per node, jumlah baris sebenarnya, jumlah perulangan, bahkan halaman yang disentuh. Chapter ini membedah setiap angka tambahan itu, opsi BUFFERS yang mengungkap kerja cache, dan teknik aman untuk menguji DELETE tanpa kehilangan data.

sql buat-tabel.sql 
-- Tabel 50.000 order dengan index pada kolom total.
-- Dipakai untuk membandingkan perkiraan EXPLAIN dengan kenyataan EXPLAIN ANALYZE.
CREATE TABLE order_penjualan (
    id           bigint PRIMARY KEY,
    pelanggan_id integer NOT NULL,
    tanggal      date NOT NULL,
    total        numeric(12,2) NOT NULL,
    status       text NOT NULL
);

INSERT INTO order_penjualan (id, pelanggan_id, tanggal, total, status)
SELECT g,
       1 + (g * 37) % 5000,
       DATE '2026-01-01' + (g % 180),
       ((g % 900) * 1000 + 25000)::numeric(12,2),
       (ARRAY['lunas','diproses','menunggu','batal'])[1 + g % 4]
FROM generate_series(1, 50000) AS g;

CREATE INDEX idx_order_total ON order_penjualan (total);

ANALYZE order_penjualan;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 50000
CREATE INDEX
ANALYZE

Menjalankan Query Sungguhan #

Sintaksnya hanya menambah kata ANALYZE setelah EXPLAIN. Query rentang harga berikut membaca sekitar seperlima tabel lewat index; perhatikan angka yang sebelumnya tidak pernah ada — actual time, rows nyata, dan loops:

sql analisis-nyata.sql 
-- EXPLAIN ANALYZE menjalankan query sungguhan lalu melaporkan
-- waktu nyata, jumlah baris nyata, dan jumlah perulangan per node.
-- Seluruh angka waktu akan berbeda di mesin Anda.
EXPLAIN ANALYZE
SELECT count(status)
FROM order_penjualan
WHERE total BETWEEN 25000 AND 200000;

Output yang Dihasilkan:

text 
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=753.10..753.11 rows=1 width=8) (actual time=1.807..1.808 rows=1 loops=1)
   ->  Bitmap Heap Scan on order_penjualan  (cost=213.06..728.52 rows=9831 width=7) (actual time=0.425..1.224 rows=9855 loops=1)
         Recheck Cond: ((total >= '25000'::numeric) AND (total <= '200000'::numeric))
         Heap Blocks: exact=128
         ->  Bitmap Index Scan on idx_order_total  (cost=0.00..210.60 rows=9831 width=0) (actual time=0.406..0.406 rows=9855 loops=1)
               Index Cond: ((total >= '25000'::numeric) AND (total <= '200000'::numeric))
 Planning Time: 0.446 ms
 Execution Time: 1.839 ms
(8 rows)

Bentuk pohonnya sama dengan EXPLAIN biasa; yang baru adalah tanda kurung kedua di setiap node. Mari kita baca satu per satu pada node Bitmap Heap Scan:

  1. (cost=213.06..728.52 rows=9831): perkiraan planner — biaya dan jumlah baris yang diperkirakan keluar.
  2. (actual time=0.425..1.224 rows=9855 loops=1): kenyataan lapangan. Waktu 0,425 ms adalah waktu sampai baris pertama keluar; 1,224 ms adalah waktu sampai baris terakhir. Baris nyata yang keluar: 9.855.
  3. Perkiraan 9.831 vs nyata 9.855: selisih 0,2% — statistik tabel ini masih segar. Selisih besar justru menjadi sinyal, dan kita akan melihat kasus itu di bagian akhir chapter.
  4. Heap Blocks: exact=128: bitmap menandai 128 halaman tabel secara tepat (mode exact), lalu halaman-halaman itu dikunjungi berurutan.
  5. Planning Time dan Execution Time: dua angka penutup yang memisahkan lama menyusun rencana (0,446 ms) dari lama mengeksekusinya (1,839 ms).

Membaca loops dengan Hati-hati #

Angka loops paling sering disalahpahami. Node pada contoh di atas menjalankan loops=1 — sekali saja — sehingga rows=9855 adalah total barisnya. Ketika sebuah node dijalankan berulang (misalnya sisi dalam Nested Loop yang diprobe sekali per baris sisi luar), actual time dan rows dilaporkan per perulangan sebagai rata-rata. Total baris yang dihasilkan node adalah rows × loops. Salah membaca aturan ini membuat query join tampak “hanya memproses 6 baris” padahal 6 baris itu diulang 1.000 kali.

Mematikan TIMING, Menyisakan Baris #

Pengukuran waktu per node tidak gratis: PostgreSQL harus memanggil jam sistem untuk setiap baris yang lewat. Pada query yang memroses jutaan baris, overhead TIMING bisa mengubah perilaku yang diukur. Opsi TIMING OFF mematikan pengukuran waktu per node sambil tetap menjalankan query dan menghitung baris nyata:

sql tanpa-timing.sql 
-- TIMING OFF: waktu per node tidak diukur, angka aktual baris tetap ada.
-- Berguna saat kita hanya butuh jumlah baris nyata tanpa overhead pengukuran.
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
SELECT count(status)
FROM order_penjualan
WHERE total BETWEEN 25000 AND 200000;

Output yang Dihasilkan:

text 
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Bitmap Heap Scan on order_penjualan (actual rows=9855 loops=1)
         Recheck Cond: ((total >= '25000'::numeric) AND (total <= '200000'::numeric))
         Heap Blocks: exact=128
         ->  Bitmap Index Scan on idx_order_total (actual rows=9855 loops=1)
               Index Cond: ((total >= '25000'::numeric) AND (total <= '200000'::numeric))
(6 rows)

Output di atas memakai kombinasi TIMING OFF, COSTS OFF, SUMMARY OFF sehingga yang tersisa murni fakta struktural: pohon node, rows nyata, dan loops. Pola seperti ini berguna ketika Anda hanya ingin tahu “apakah estimasi cocok dengan kenyataan?” tanpa peduli berapa milidetiknya — misalnya saat membandingkan beberapa query pada data yang sama.

Membaca Kerja Cache dengan BUFFERS #

Opsi BUFFERS menghitung halaman yang disentuh selama eksekusi. Ini satu-satunya cara melihat apakah query Anda bekerja dari cache atau memukul disk:

sql buffers.sql 
-- BUFFERS: hitung halaman yang disentuh saat eksekusi.
-- shared hit = halaman sudah di cache; shared read = dibaca dari disk.
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, COSTS OFF, SUMMARY OFF)
SELECT count(status)
FROM order_penjualan
WHERE total < 700000;

Output yang Dihasilkan:

text 
                          QUERY PLAN                           
---------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=368
   ->  Seq Scan on order_penjualan (actual rows=37625 loops=1)
         Filter: (total < '700000'::numeric)
         Rows Removed by Filter: 12375
         Buffers: shared hit=368
 Planning:
   Buffers: shared hit=83
(8 rows)

Dua angka yang perlu dikenali: shared hit berarti halaman sudah ada di shared buffers (cache memori PostgreSQL) sehingga pembacaan seketika; shared read berarti halaman harus diambil dari sistem operasi atau disk. Query di atas menampilkan shared hit=368 — seluruh 368 halaman tabel sudah hangat di cache karena baru saja ditulis. Eksekusi pertama pada server yang baru menyala umumnya menampilkan angka read yang besar, lalu berubah menjadi hit pada eksekusi berikutnya. Angka temp yang muncul menandakan data tumpah ke disk — misalnya sort yang tidak muat di work_mem.

Menguji DELETE Tanpa Kehilangan Data #

EXPLAIN ANALYZE menjalankan query secara sungguhan — termasuk query yang menulis data. Tidak ada kata “keringanan” untuk DELETE, UPDATE, atau INSERT: barisnya benar-benar terhapus, berubah, atau bertambah. Cara aman mengujinya adalah membungkus dalam transaksi lalu membatalkan:

sql aman-rollback.sql 
-- EXPLAIN ANALYZE pada DELETE benar-benar menghapus baris.
-- Bungkus dalam transaksi lalu ROLLBACK agar tabel tetap utuh.
BEGIN;

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
DELETE FROM order_penjualan
WHERE total > 900000;

ROLLBACK;

-- Bukti: tidak ada baris yang hilang.
SELECT count(*) AS total_baris_tersisa FROM order_penjualan;

Output yang Dihasilkan:

text 
BEGIN
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Delete on order_penjualan (actual rows=0 loops=1)
   ->  Bitmap Heap Scan on order_penjualan (actual rows=1320 loops=1)
         Recheck Cond: (total > '900000'::numeric)
         Heap Blocks: exact=65
         ->  Bitmap Index Scan on idx_order_total (actual rows=1320 loops=1)
               Index Cond: (total > '900000'::numeric)
(6 rows)

ROLLBACK
 total_baris_tersisa 
---------------------
               50000
(1 row)

Node Delete on order_penjualan melaporkan actual rows=0 karena tanpa klausa RETURNING node tersebut tidak mengeluarkan baris apa pun; pekerjaan sebenarnya terlihat di anaknya — Bitmap Heap Scan menemukan 1.320 baris dengan total di atas Rp900.000 dan mengirimkannya ke node Delete untuk dihapus. Setelah ROLLBACK, hitungan baris tabel kembali utuh 50.000. Transaksi adalah sabuk pengaman; tanpa transaksi, satu EXPLAIN ANALYZE DELETE ... tanpa WHERE akan mengosongkan tabel sungguhan.

Perkiraan vs Kenyataan sebagai Sinyal #

Sekarang kasus yang menunjukkan betapa berharganya angka aktual. Skenarionya: tabel order_bulanan dianalisis saat berisi 1.000 order yang tersebar merata, lalu tumbuh menjadi 50.000 order — hampir semuanya dari satu pelanggan — tanpa pernah di-ANALYZE ulang:

sql statistik-usang.sql 
-- Demonstrasi statistik usang: tabel dianalisis saat berisi 1.000 baris,
-- lalu tumbuh menjadi 50.000 baris tanpa ANALYZE ulang.
-- Seluruh angka waktu akan berbeda di mesin Anda.
CREATE TABLE order_bulanan (
    id           bigint PRIMARY KEY,
    pelanggan_id integer NOT NULL,
    total        numeric(12,2) NOT NULL
);

-- Awal bulan: 1.000 order tersebar merata ke 1.000 pelanggan.
INSERT INTO order_bulanan (id, pelanggan_id, total)
SELECT g, g, ((g % 900) * 1000 + 25000)::numeric(12,2)
FROM generate_series(1, 1000) AS g;

ANALYZE order_bulanan;

CREATE INDEX idx_bulanan_pelanggan ON order_bulanan (pelanggan_id);

-- Akhir bulan: 49.000 order baru, semuanya dari pelanggan 777
-- (promo besar-besaran). Statistik tidak pernah diperbarui.
INSERT INTO order_bulanan (id, pelanggan_id, total)
SELECT 1000 + g, 777, (((1000 + g) % 900) * 1000 + 25000)::numeric(12,2)
FROM generate_series(1, 49000) AS g;

-- Estimasi menyebut 1 baris; kenyataannya 49.001 baris.
EXPLAIN ANALYZE
SELECT count(*) FROM order_bulanan WHERE pelanggan_id = 777;

-- Setelah ANALYZE, perkiraan kembali akurat dan rencana berubah.
ANALYZE order_bulanan;

EXPLAIN ANALYZE
SELECT count(*) FROM order_bulanan WHERE pelanggan_id = 777;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 1000
ANALYZE
CREATE INDEX
INSERT 0 49000
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8.31..8.32 rows=1 width=8) (actual time=8.700..8.701 rows=1 loops=1)
   ->  Index Only Scan using idx_bulanan_pelanggan on order_bulanan  (cost=0.29..8.31 rows=1 width=0) (actual time=0.017..6.566 rows=49001 loops=1)
         Index Cond: (pelanggan_id = 777)
         Heap Fetches: 49001
 Planning Time: 0.136 ms
 Execution Time: 8.722 ms
(6 rows)

ANALYZE
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1066.47..1066.48 rows=1 width=8) (actual time=6.063..6.064 rows=1 loops=1)
   ->  Seq Scan on order_bulanan  (cost=0.00..944.00 rows=48990 width=0) (actual time=0.050..3.960 rows=49001 loops=1)
         Filter: (pelanggan_id = 777)
         Rows Removed by Filter: 999
 Planning Time: 0.114 ms
 Execution Time: 6.091 ms
(6 rows)

Rencana pertama memuat kebohongan angka: planner memperkirakan rows=1 (warisan statistik lama), memilih Index Only Scan yang tampak murah di atas kertas, lalu harus melakukan Heap Fetches: 49001 — 49.001 lompatan ke tabel. Baris nyata (rows=49001) terpaut 49.000 kali dari perkiraan. Setelah ANALYZE dijalankan, perkiraan melonjak menjadi 48.990, planner beralih ke Seq Scan yang memang tepat untuk membaca hampir seluruh tabel, dan waktu eksekusi turun.

Mengapa Seq Scan justru menang untuk 49.001 dari 50.000 baris? Jawabannya ada pada konsep selectivity — fraksi baris yang dikembalikan query — dan itulah pokok bahasan chapter berikutnya.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

aman-rollback.sql

Unduh berkas
sql 
-- EXPLAIN ANALYZE pada DELETE benar-benar menghapus baris.
-- Bungkus dalam transaksi lalu ROLLBACK agar tabel tetap utuh.
BEGIN;

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
DELETE FROM order_penjualan
WHERE total > 900000;

ROLLBACK;

-- Bukti: tidak ada baris yang hilang.
SELECT count(*) AS total_baris_tersisa FROM order_penjualan;

analisis-nyata.sql

Unduh berkas
sql 
-- EXPLAIN ANALYZE menjalankan query sungguhan lalu melaporkan
-- waktu nyata, jumlah baris nyata, dan jumlah perulangan per node.
-- Seluruh angka waktu akan berbeda di mesin Anda.
EXPLAIN ANALYZE
SELECT count(status)
FROM order_penjualan
WHERE total BETWEEN 25000 AND 200000;

buat-tabel.sql

Unduh berkas
sql 
-- Tabel 50.000 order dengan index pada kolom total.
-- Dipakai untuk membandingkan perkiraan EXPLAIN dengan kenyataan EXPLAIN ANALYZE.
CREATE TABLE order_penjualan (
    id           bigint PRIMARY KEY,
    pelanggan_id integer NOT NULL,
    tanggal      date NOT NULL,
    total        numeric(12,2) NOT NULL,
    status       text NOT NULL
);

INSERT INTO order_penjualan (id, pelanggan_id, tanggal, total, status)
SELECT g,
       1 + (g * 37) % 5000,
       DATE '2026-01-01' + (g % 180),
       ((g % 900) * 1000 + 25000)::numeric(12,2),
       (ARRAY['lunas','diproses','menunggu','batal'])[1 + g % 4]
FROM generate_series(1, 50000) AS g;

CREATE INDEX idx_order_total ON order_penjualan (total);

ANALYZE order_penjualan;

buffers.sql

Unduh berkas
sql 
-- BUFFERS: hitung halaman yang disentuh saat eksekusi.
-- shared hit = halaman sudah di cache; shared read = dibaca dari disk.
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, COSTS OFF, SUMMARY OFF)
SELECT count(status)
FROM order_penjualan
WHERE total < 700000;

statistik-usang.sql

Unduh berkas
sql 
-- Demonstrasi statistik usang: tabel dianalisis saat berisi 1.000 baris,
-- lalu tumbuh menjadi 50.000 baris tanpa ANALYZE ulang.
-- Seluruh angka waktu akan berbeda di mesin Anda.
CREATE TABLE order_bulanan (
    id           bigint PRIMARY KEY,
    pelanggan_id integer NOT NULL,
    total        numeric(12,2) NOT NULL
);

-- Awal bulan: 1.000 order tersebar merata ke 1.000 pelanggan.
INSERT INTO order_bulanan (id, pelanggan_id, total)
SELECT g, g, ((g % 900) * 1000 + 25000)::numeric(12,2)
FROM generate_series(1, 1000) AS g;

ANALYZE order_bulanan;

CREATE INDEX idx_bulanan_pelanggan ON order_bulanan (pelanggan_id);

-- Akhir bulan: 49.000 order baru, semuanya dari pelanggan 777
-- (promo besar-besaran). Statistik tidak pernah diperbarui.
INSERT INTO order_bulanan (id, pelanggan_id, total)
SELECT 1000 + g, 777, (((1000 + g) % 900) * 1000 + 25000)::numeric(12,2)
FROM generate_series(1, 49000) AS g;

-- Estimasi menyebut 1 baris; kenyataannya 49.001 baris.
EXPLAIN ANALYZE
SELECT count(*) FROM order_bulanan WHERE pelanggan_id = 777;

-- Setelah ANALYZE, perkiraan kembali akurat dan rencana berubah.
ANALYZE order_bulanan;

EXPLAIN ANALYZE
SELECT count(*) FROM order_bulanan WHERE pelanggan_id = 777;

tanpa-timing.sql

Unduh berkas
sql 
-- TIMING OFF: waktu per node tidak diukur, angka aktual baris tetap ada.
-- Berguna saat kita hanya butuh jumlah baris nyata tanpa overhead pengukuran.
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
SELECT count(status)
FROM order_penjualan
WHERE total BETWEEN 25000 AND 200000;