Lagi utak-atik kasus performa di Odoo yang menarik buat dicatat. Satu proses approve yang harusnya cuma hitungan detik, bisa jalan sampai dua-tiga menit, bahkan kadang gagal total karena keburu timeout. Dugaan awal biasanya soal kode aplikasi, logicnya terlalu berat, atau terlalu banyak record relasi yang ikut diproses. Ternyata bukan itu sama sekali.
Akar masalahnya: foreign key tanpa index.
Proses approve ini di baliknya menghapus beberapa baris di satu tabel besar (jutaan row, lebih dari 1GB). Begitu satu baris dihapus, database-nya wajib cek semua tabel lain yang punya foreign key nunjuk ke baris itu, buat mastiin referential integrity-nya nggak rusak.
Masalahnya, kolom yang jadi foreign key itu nggak di-index. Jadi tiap kali ada baris dihapus, database-nya full table scan ke tabel besar itu, dua kali per baris yang dihapus. Biaya scan per baris ini naik sebanding ukuran tabel, jadi makin besar tabelnya, makin mahal tiap baris yang dihapus. Begitu yang dihapus puluhan baris sekaligus dalam satu approve, biaya yang sudah mahal per baris itu numpuk, dan totalnya kerasa banget.
Fix buat bagian ini simpel, tambah index=True di dua field itu. Satu baris delete yang tadinya 2,4 detik, jadi 16 milidetik. Ini baru dua dari tiga index yang akhirnya saya tambahkan, satu lagi ceritanya di bawah.
Yang bikin saya mikir lebih lama: ternyata sudah ada index di kolom itu, tapi nggak kepake.
Ini bagian yang paling menarik buat saya. Sebelum nemu masalah di atas, saya cek dulu index yang ada di tabel itu. Ternyata ada, composite index yang nyentuh kolom yang sama, dibuat buat keperluan report lain. Tapi index itu partial, cuma aktif kalau kondisi tertentu terpenuhi (status aktif, bukan status cancel, tipe tertentu).
Database-nya nggak bisa pakai index partial itu buat cek referential integrity, karena kondisi delete yang dijalankan nggak otomatis membuktikan predikat partial-nya pasti terpenuhi. Index itu nggak relevan sama sekali di konteks ini, walau kolomnya sama persis.
Pelajarannya: punya index di kolom yang tepat itu nggak cukup. Kalau index-nya partial atau composite, predikat dan urutan kolomnya harus match sama kondisi query yang bakal dijalankan. Index yang “kelihatannya” nyentuh masalah bisa sama sekali nggak kepake kalau bentuknya nggak pas.
Masalah kedua, baru kelihatan setelah masalah pertama beres.
Setelah index pertama ditambah, di volume data lebih besar muncul bottleneck baru yang sebelumnya ketutupan sama masalah pertama. Satu query inti Odoo yang jalan tiga kali per baris yang dihapus, buat cek urutan nomor dokumen, tiap panggilannya butuh 753 milidetik.
Ada composite index lain di tabel itu, tiga kolom gabungan, tapi salah satu kolomnya nggak disebut sama sekali di WHERE query ini, jadi index itu nggak kepake buat nyaring hasil lebih awal, keburu scan ratusan ribu baris dulu sebelum nemu yang dicari.
Fix-nya bukan ngurutin ulang kolom index yang lama, tapi bikin index gabungan (composite) baru yang isinya cuma dua kolom yang benar-benar dipakai query ini, kolom yang nggak kepake itu nggak diikutkan sama sekali. Dengan index baru ini, 753 milidetik per panggilan jadi 48 milidetik.
Hasil akhirnya.
Ketiga index ini digabung, dua index biasa plus satu index gabungan, proses yang tadinya 2,8 menit jadi 9 detik. Delapan belas kali lebih cepat, tanpa ubah satu baris pun logic aplikasi. Hasil akhirnya persis sama, cuma kecepatannya beda.
Yang paling nempel buat saya bukan soal “tambah index biar cepat”, itu udah pengetahuan umum. Yang baru buat saya justru soal index yang sudah ada tapi nggak kepake karena bentuknya nggak pas sama query. Sebelum nambah index baru, sekarang saya selalu cek dulu lewat EXPLAIN ANALYZE, apakah index yang sudah ada itu benar-benar terpakai atau cuma kelihatan relevan dari luar.