KAL
Menu

Performa query

Membaca rencana eksekusi EXPLAIN dan memahami indeks dari sudut pandang insinyur data.

Daftar isi

Topik ini membahas cara membaca rencana eksekusi dengan EXPLAIN, yaitu keluaran yang menggambarkan langkah-langkah yang akan dijalankan mesin SQL atas sebuah query, beserta peran indeks dan statistik dari sudut pandang insinyur data. Contoh dijalankan dengan DuckDB 2.0 melalui Python pada tabel 200000 baris, sehingga setiap rencana yang ditampilkan berasal dari eksekusi nyata. Bentuk rencana dapat sedikit berbeda antarversi mesin, tetapi cara membacanya tetap sama. Setelah membaca, penurunan performa query dapat dicari penyebabnya dari operator rencananya, bukan dari dugaan.

Dataset contoh#

Data contohnya berupa tabel transaksi 200000 baris yang dibuat secara sintetis dari fungsi range, sehingga isinya deterministik dan dapat dibangun ulang tanpa berkas eksternal. Kolom id berjalan dari 1 sampai 200000, pelanggan_id berputar dari 1 sampai 50, tanggal menyebar sepanjang 180 hari, dan total berputar pada tujuh nilai dari 5000 sampai 11000. Distribusi yang sederhana ini cukup untuk memicu perilaku rencana yang ingin diamati. Skrip berikut membangun ulang basis data toko.duckdb setiap kali dijalankan.

python buat_tabel.py 
import os
import duckdb

# tabel besar sintetis dibangun ulang setiap kali skrip dijalankan
if os.path.exists("toko.duckdb"):
    os.remove("toko.duckdb")

con = duckdb.connect("toko.duckdb")
con.execute("""
CREATE TABLE transaksi AS
SELECT i::INTEGER                                          AS id,
       (i % 50 + 1)::INTEGER                               AS pelanggan_id,
       CAST(DATE '2024-01-01' + INTERVAL (i % 180) DAY AS DATE) AS tanggal,
       ((i % 7) * 1000 + 5000)::INTEGER                    AS total
FROM range(1, 200001) AS t(i)
""")
jumlah = con.execute("SELECT COUNT(*) FROM transaksi").fetchone()[0]
rentang = con.execute("SELECT MIN(tanggal), MAX(tanggal) FROM transaksi").fetchone()
print(f"transaksi: {jumlah} baris, tanggal {rentang[0]} s.d. {rentang[1]}")
con.close()
text Keluaran 
transaksi: 200000 baris, tanggal 2024-01-01 s.d. 2024-06-28

Membaca rencana dengan EXPLAIN#

EXPLAIN tidak menjalankan query, melainkan menampilkan pohon operator yang akan dieksekusi beserta estimasi kardinalitasnya. Rencana DuckDB dibaca dari bawah ke atas, yaitu data masuk dari operator paling bawah dan hasil keluar dari operator paling atas. Angka yang diawali tanda ~ adalah estimasi dari statistik, bukan hasil pengukuran. Skrip berikut menampilkan rencana query titik pada kolom id.

python jelaskan_rencana.py 
import duckdb

con = duckdb.connect("toko.duckdb")

print("Rencana eksekusi query titik dengan EXPLAIN:")
print(con.execute("EXPLAIN SELECT * FROM transaksi WHERE id = 12345").fetchone()[1])
con.close()
text Keluaran 
Rencana eksekusi query titik dengan EXPLAIN:
╭─ Seq Scan ─────────────────╮
│ Table: toko.main.transaksi │
│ Type: Sequential Scan      │
│ Projections: id,           │
│              pelanggan_id, │
│              tanggal,      │
│              total         │
│ Filters: id = 12345        │
│ ~2 rows                    │
╰────────────────────────────╯

Operator satu-satunya adalah Seq Scan, yaitu pemindaian berurutan (sequential scan) yang membaca tabel kolom demi kolom. Baris Projections menunjukkan kolom yang dibaca, sedangkan baris Filters menunjukkan syarat yang dievaluasi saat pemindaian. Estimasi ~2 rows berasal dari statistik yang tahu bahwa id unik, sehingga satu nilai tepat diperkirakan menghasilkan sekitar satu baris. Query titik seperti inilah yang nantinya menjadi alat uji peran indeks.

Rencana agregasi dan join#

Rencana agregasi dan join memperlihatkan operator yang lebih berlapis, dan keduanya menjadi bahan latihan membaca estimasi. Query pertama mengagregasi transaksi dengan total minimal 10000 per pelanggan, sedangkan query kedua menggabungkan tabel transaksi dengan tabel itu sendiri untuk tiga id pertama.

python rencana_agregasi.py 
import duckdb

con = duckdb.connect("toko.duckdb")

print("Rencana query agregasi (dibaca dari bawah ke atas):")
print(con.execute("""
EXPLAIN SELECT pelanggan_id, COUNT(*) AS jumlah
FROM transaksi
WHERE total >= 10000
GROUP BY pelanggan_id
""").fetchone()[1])
con.close()
text Keluaran 
Rencana query agregasi (dibaca dari bawah ke atas):
╭─ Projection ──────────────────────╮
│ Projections: CAST(#0 AS INTEGER), │
│              #1                   │
│ ~45 rows                          │
╰─────────────────┬─────────────────╯
╭─ Perfect Hash Group By ───────────╮
│ Groups: #0                        │
│ Aggregates: count_star()          │
│ ~45 rows                          │
╰─────────────────┬─────────────────╯
╭─ Projection ────┴─────────────────╮
│ Projections: pelanggan_id         │
│ ~40,000 rows                      │
╰─────────────────┬─────────────────╯
╭─ Projection ────┴─────────────────╮
│ Projections: CAST(#0 AS TINYINT)  │
│ ~40,000 rows                      │
╰─────────────────┬─────────────────╯
╭─ Seq Scan ──────┴─────────────────╮
│ Table: toko.main.transaksi        │
│ Type: Sequential Scan             │
│ Projections: pelanggan_id         │
│ Filters: total >= 10000           │
│ ~40,000 rows                      │
╰───────────────────────────────────╯

Dari bawah ke atas, Seq Scan menyaring total >= 10000 dan memperkirakan 40000 baris, kemudian Perfect Hash Group By membentuk kelompok pelanggan_id dengan estimasi 45 kelompok, dan Projection terakhir menyiapkan kolom keluaran. Nama Perfect Hash Group By menandakan strategi agregasi yang memanfaatkan domain nilai yang kecil dan sudah diketahui. Estimasi di sini tergolong tepat karena statistik kolomnya lengkap, tetapi ketepatan itu tidak dijamin pada query lain.

python rencana_join.py 
import duckdb

con = duckdb.connect("toko.duckdb")

print("Rencana query join dengan estimator kasar:")
print(con.execute("""
EXPLAIN SELECT t.id, t.total
FROM transaksi t
JOIN transaksi k ON k.id = t.id
WHERE k.id <= 3
""").fetchone()[1])
con.close()
text Keluaran 
Rencana query join dengan estimator kasar:
╭─ Projection ───────────────╮
│ Projections: id, total     │
│ ~9,026 rows                │
╰──────────────┬─────────────╯
╭─ Hash Join ──┴─────────────╮
│ Join Type: INNER           │
│ Conditions: id = id        │
│ ~9,026 rows                │
╰──────────────┬─────────────╯
               ├───────────────────────────────╮
╭─ Seq Scan ───┴─────────────╮  ╭─ Seq Scan ───┴─────────────╮
│ Table: toko.main.transaksi │  │ Table: toko.main.transaksi │
│ Type: Sequential Scan      │  │ Type: Sequential Scan      │
│ Projections: id, total     │  │ Projections: id            │
│ Filters: id <= 3           │  │ Filters: id <= 3           │
│ ~40,000 rows               │  │ ~40,000 rows               │
╰────────────────────────────╯  ╰────────────────────────────╯

Hash Join memproses kedua sisi dengan membangun tabel hash dari satu sisi lalu menyelidiki sisi lainnya, dan perkiraan hasilnya tertulis sekitar 9000 baris. Yang menarik justru estimasi kedua Seq Scan, yaitu 40000 baris untuk syarat id <= 3 yang sebenarnya hanya cocok dengan 3 baris. Estimator memakai selektivitas bawaan untuk syarat rentang ketika tidak ada informasi yang lebih baik, sehingga angkanya kasar. Jumlah nyata baru terlihat melalui EXPLAIN ANALYZE, yang keluarannya memuat waktu eksekusi sehingga berguna untuk diagnosis, bukan untuk dikutip sebagai angka tetap.

Indeks dan statistik kolom#

Indeks bukanlah tombol yang selalu mempercepat query pada mesin analitik kolom, dan rencana berikut memperlihatkan buktinya. Skrip membuat indeks ART (Adaptive Radix Tree), struktur pohon radix adaptif yang dipakai DuckDB untuk menegakkan PRIMARY KEY dan mempercepat pencarian titik, lalu menampilkan rencana query yang sama sebelum dan sesudah indeks terpasang.

python indeks_dan_rencana.py 
import duckdb

con = duckdb.connect("toko.duckdb")

sql = "EXPLAIN SELECT * FROM transaksi WHERE id = 12345"
print("=== rencana sebelum indeks ===")
print(con.execute(sql).fetchone()[1])

con.execute("CREATE INDEX idx_transaksi_id ON transaksi (id)")

print("=== rencana sesudah indeks ===")
print(con.execute(sql).fetchone()[1])
con.close()
text Keluaran 
=== rencana sebelum indeks ===
╭─ Seq Scan ─────────────────╮
│ Table: toko.main.transaksi │
│ Type: Sequential Scan      │
│ Projections: id,           │
│              pelanggan_id, │
│              tanggal,      │
│              total         │
│ Filters: id = 12345        │
│ ~2 rows                    │
╰────────────────────────────╯

=== rencana sesudah indeks ===
╭─ Seq Scan ─────────────────╮
│ Table: toko.main.transaksi │
│ Type: Sequential Scan      │
│ Projections: id,           │
│              pelanggan_id, │
│              tanggal,      │
│              total         │
│ Filters: id = 12345        │
│ ~2 rows                    │
╰────────────────────────────╯

Kedua rencana identik, yaitu tetap Seq Scan dengan estimasi yang sama, karena pengoptimal DuckDB tidak memilih pemindaian indeks untuk query ini. Dokumentasi DuckDB menyebut indeks ART terutama menegakkan constraint dan mempercepat query titik yang sangat selektif, sedangkan untuk pemindaian besar mesin mengandalkan penyimpanan kolom dan zonemap, yaitu indeks min-max per potong baris yang dibuat otomatis. Efek zonemap justru terlihat pada rencana filter yang mustahil terpenuhi, seperti COUNT(*) dengan syarat total > 12000 padahal nilai total maksimal 11000.

python pruning_zonemap.py 
import duckdb

con = duckdb.connect("toko.duckdb")

print("Rencana filter yang mustahil terpenuhi menurut statistik kolom:")
print(con.execute("EXPLAIN SELECT COUNT(*) FROM transaksi WHERE total > 12000").fetchone()[1])
con.close()
text Keluaran 
Rencana filter yang mustahil terpenuhi menurut statistik kolom:
╭─ Ungrouped Aggregate ────╮
│ Aggregates: count_star() │
│ ~1 row                   │
╰─────────────┬────────────╯
╭─ Empty Result ───────────╮
╰──────────────────────────╯

Operator Empty Result menandakan seluruh pemindaian dipangkas oleh statistik sebelum satu baris pun dibaca, sehingga query selesai nyaris tanpa kerja. Pelajaran untuk insinyur data berbunyi, ukur dahulu lewat EXPLAIN sebelum menambah indeks, karena indeks memperlambat pemuatan dan pembaruan data di sisi lain. Pada mesin penyimpan baris seperti PostgreSQL, syarat selektif memang kerap memunculkan pemindaian indeks di rencananya, sehingga kebiasaan menambah indeks dari basis data transaksional tidak selalu terbawa ke basis data analitik.

Kesalahan umum#

Tiga kekeliruan berikut paling sering muncul saat menilai performa query, beserta penyebab dan perbaikannya.

  • Menilai cepat dari angka estimasi rencana. Estimasi memakai selektivitas bawaan dan bisa jauh dari kenyataan. Perlakukan angka berawalan ~ sebagai petunjuk, dan verifikasi dengan eksekusi nyata bila dampaknya besar.
  • Menambah indeks pada semua kolom yang sering difilter. Setiap indeks memperberat pemuatan dan pembaruan, sementara mesin analitik kolom sering memilih pemindaian berurutan. Buat indeks hanya setelah rencana menunjukkan kebutuhan yang nyata.
  • Membandingkan bentuk rencana antarversi mesin tanpa mencantumkan versinya. Nama operator dan bentuk keluaran dapat berubah antarversi. Catat versi mesin bersama rencana yang dikutip.

Ringkasan#

  • EXPLAIN menampilkan pohon operator beserta estimasi kardinalitas, dan rencana DuckDB dibaca dari bawah ke atas.
  • Seq Scan, Hash Join, dan Perfect Hash Group By adalah operator yang paling sering ditemui pada rencana analitik.
  • Estimasi berawalan ~ bersifat kasar, sedangkan EXPLAIN ANALYZE menambahkan angka nyata beserta waktu eksekusi.
  • Pada DuckDB, indeks ART terutama melayani constraint dan query titik, sedangkan zonemap otomatis memangkas pemindaian dari statistik kolom.

Referensi#