KAL
Menu

EXPLAIN dan EXPLAIN ANALYZE

Membaca rencana eksekusi dan membandingkan estimasi perencana dengan kenyataan.

Daftar isi

Topik ini membahas dua perintah yang menjadi mata penulis query, yaitu EXPLAIN yang menampilkan rencana eksekusi tanpa menjalankannya, dan EXPLAIN ANALYZE yang menjalankannya sungguhan sambil mengukur. Membaca rencana adalah keterampilan yang membedakan query yang kebetulan cepat dari query yang dipahami mengapa cepat.

Blok penyiapan dataset#

bash 
# dataset besar deterministik untuk pengukuran rencana
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS sirkulasi CASCADE;
CREATE TABLE sirkulasi (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    anggota_id INTEGER NOT NULL,
    buku_id    INTEGER NOT NULL,
    status     TEXT NOT NULL,
    tahun      INTEGER NOT NULL
);
INSERT INTO sirkulasi (anggota_id, buku_id, status, tahun)
SELECT g % 500 + 1, g % 400 + 1,
       CASE WHEN g % 3 = 0 THEN 'kembali' ELSE 'dipinjam' END,
       2018 + g % 8
FROM generate_series(1, 50000) g;
CREATE INDEX idx_sirkulasi_anggota ON sirkulasi (anggota_id);
ANALYZE sirkulasi;
SQL

Membaca EXPLAIN#

EXPLAIN menampilkan pohon rencana, yaitu cara perencana berniat mengambil data, dengan satu baris per simpul. Setiap barisnya memuat empat angka, yaitu estimasi banyaknya baris, lebar rata-rata baris dalam byte, biaya awal, dan biaya total. Biaya dihitung dalam satuan bebas yang bermakna relatif, di mana satu pembacaan halaman acak bernilai jauh lebih mahal daripada pembacaan berurutan, sehingga angka itu dibaca untuk membandingkan rencana, bukan untuk diterjemahkan menjadi milidetik.

Keluaran penuhnya memuat angka yang mengikuti statistik dan konfigurasi mesin, sehingga tutorial ini merekam jenis simpulnya saja agar tetap sama di lingkungan mana pun. Blok berikut membaca simpul rencana query penyaringan tunggal.

bash 
# simpul rencana untuk penyaringan satu anggota
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT * FROM sirkulasi WHERE anggota_id = 42;" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
text Keluaran 
      1 Bitmap Heap Scan
      1 Bitmap Index Scan

Rencananya terdiri atas sepasang simpul bitmap, yaitu Bitmap Index Scan yang menyusuri index dan menandai alamat baris yang cocok, lalu Bitmap Heap Scan yang membaca tabel sesuai tandanya. Pada keluaran EXPLAIN penuh, simpul teratas Aggregate menyusul di atas keduanya bila querynya agregasi, dan memahami urutan simpul dari bawah ke atas adalah cara membaca pohon rencana.

Membandingkan dua rencana#

Manfaat EXPLAIN terasa ketika dua bentuk query dibandingkan. Query yang menyaring anggota dan tahun tanpa index gabungan memilih jalur pemindaian penuh, dan menambah index composite mengubah pilihannya.

bash 
# simpul rencana sebelum dan sesudah index composite
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE anggota_id = 42 AND tahun = 2021;" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "CREATE INDEX idx_sirkulasi_anggota_tahun ON sirkulasi (anggota_id, tahun);"
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE anggota_id = 42 AND tahun = 2021;" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP INDEX idx_sirkulasi_anggota_tahun;"
text Keluaran 
      1 Bitmap Heap Scan
      1 Bitmap Index Scan
CREATE INDEX
      1 Bitmap Heap Scan
      1 Bitmap Index Scan
DROP INDEX

Rencana pertama memindai seluruh tabel, sedangkan rencana kedua menyusuri index composite yang baru melalui sepasang simpul bitmap, dan perubahan jenis simpulnya terbaca langsung. Pada mesin pembaca, biaya total kedua rencana dapat dilihat berdampingan dengan menjalankan EXPLAIN penuh, dan penurunannya menegaskan manfaat index tersebut.

EXPLAIN ANALYZE dan pengukurannya#

EXPLAIN ANALYZE menjalankan query sungguhan lalu melaporkan waktu aktual dan banyaknya baris yang benar-benar lewat di setiap simpul. Keluarannya memuat waktu yang berubah pada setiap eksekusi, sehingga pada tutorial ini bloknya ditandai tidak-dijalankan dan bentuk keluarannya diuraikan dalam prosa.

bash 
# tidak-dijalankan (waktu aktual berubah pada setiap eksekusi sehingga tidak dapat direkam sebagai keluaran tetap)
# bentuk rencana aktual yang memuat actual time, actual rows, dan loops per simpul
EXPLAIN ANALYZE SELECT * FROM sirkulasi WHERE anggota_id = 42;

Nilai yang dibaca terutama adalah selisih estimasi baris dengan baris aktual, karena estimasi yang menyimpang jauh menggerakkan perencana memilih rencana yang salah, dan penyebabnya bersambung ke statistik pada topik tersendiri. Waktu aktual dibaca sebagai rentang, yaitu waktu sebelum dan sesudah simpul mengeluarkan baris pertamanya, sedangkan kolom loops menghitung berapa kali simpul itu dijalankan dalam satu eksekusi.

Side effect yang ikut sungguhan#

EXPLAIN ANALYZE menjalankan query sungguhan, sehingga INSERT, UPDATE, dan DELETE di dalamnya benar-benar mengubah data pada setiap eksekusinya. Kebiasaan yang aman membungkusnya dalam transaksi yang di-ROLLBACK ketika mengukur pernyataan tulis, pola yang dibahas pada modul transaksi.

WARNING berikut merangkum konsekuensi yang paling sering ditimbulkan pada lingkungan produksi.

Kesalahan umum#

Kesalahan pertama adalah menerjemahkan biaya menjadi waktu, padahal biayanya satuan relatif antar simpul, sedangkan waktu sungguhan hanya tersedia pada EXPLAIN ANALYZE. Kesalahan kedua adalah membaca hanya baris teratas pohon, karena penyebab lambat sering berada di simpul dalam yang menyalurkan data ke atasnya.

Kesalahan ketiga adalah mengukur dengan EXPLAIN ANALYZE pada tabel produksi tanpa transaksi pengaman, sehingga pengukuran ikut menulis. Kesalahan keempat adalah membandingkan biaya antar mesin yang berbeda konfigurasi, karena nilai konstanta biaya dapat diatur per server, dan perbandingan yang sah berada dalam satu server dengan konfigurasi yang sama.

Ringkasan#

  • EXPLAIN menampilkan pohon rencana tanpa mengeksekusi, dengan estimasi baris, lebar, dan biaya per simpul.
  • EXPLAIN ANALYZE menjalankan query sungguhan dan melaporkan waktu serta baris aktual per simpul.
  • Selisih estimasi baris dengan baris aktual adalah sinyal utama, dan penyebabnya bersambung ke statistik.
  • Pengukuran atas pernyataan tulis wajib dibungkus transaksi yang di-ROLLBACK karena eksekusinya sungguhan.

Referensi#