Wawancara Data: Bagaimana cara mendiagnosis generic plan PostgreSQL untuk kueri berparameter?

Petunjuk dan skenario penggunaan

Sebuah API yang menggunakan prepared statement mengalami long-tail latency setelah diluncurkan: tenant kecil berjalan cepat, sedangkan tenant besar tiba-tiba mengalami sequential scan. Jelaskan bagaimana PostgreSQL memilih antara custom plan dan generic plan, cara membandingkannya dengan EXPLAIN (GENERIC_PLAN), cara memverifikasi statistik, kecondongan (skew) parameter, caching, dan plan_cache_mode, serta cara memperbaiki masalah tersebut tanpa merusak transaksi atau connection pool.

Hal yang diuji oleh pewawancara

Memisahkan biaya planning, execution, dan serialisasi hasil.
Memahami bahwa generic plan mengabaikan nilai parameter konkret sedangkan custom plan dapat memanfaatkan selektivitas.
Menggunakan EXPLAIN ANALYZE secara aman tanpa mengirimkan efek samping penulisan ke produksi.
Menggabungkan statistik, indeks, connection pool, dan parameterisasi untuk menemukan regresi.
Memilih auto, force_generic_plan, atau force_custom_plan berdasarkan bukti.

Pertanyaan klarifikasi awal

Apakah kueri dijalankan melalui prepared statement, ORM, atau proxy, dan apakah koneksi digunakan kembali?
Apakah parameter mengalami skew, dengan perbedaan signifikan dalam ukuran tenant dan suhu (temperatur) data?
Apakah regresi terjadi pada planning, execution, lock wait, IO, atau serialisasi?
Apakah Anda diizinkan mengubah indeks, statistics target, SQL, pool, atau konfigurasi tingkat sesi?

Jawaban 30 detik

Mulailah dengan EXPLAIN (GENERIC_PLAN) untuk melihat rencana yang tidak bergantung pada nilai parameter, lalu gunakan nilai representatif dengan EXPLAIN ANALYZE EXECUTE untuk memeriksa custom plan dan baris data sebenarnya. Generic plan menghemat pekerjaan planning tetapi dapat tetap tidak efisien ketika selektivitas timpang. Verifikasi statistik dan perilaku plan cache, lakukan tolok ukur (benchmark) pada planning, eksekusi, dan tail latency, lalu ubah plan_cache_mode atau kueri dalam sesi terkontrol dan validasi setiap koneksi pool.

Jawaban mendalam, langkah demi langkah

1. Pisahkan planning dari eksekusi

Planner memilih scan dan join berdasarkan SQL, statistik, dan parameter; executor membaca halaman, memfilter baris, dan mengembalikan hasil. Waktu respons aplikasi (wall time) saja tidak dapat membuktikan bahwa generic plan adalah penyebabnya.

2. Jelaskan custom plan

Custom plan dibuat untuk parameter saat ini dan dapat memanfaatkan selektivitas. Tenant kecil mungkin lebih cocok dengan index scan sedangkan tenant besar lebih cocok dengan sequential scan atau urutan join yang berbeda; biayanya adalah planning yang berulang.

3. Jelaskan generic plan

Generic plan menggunakan placeholder dan mengabaikan nilai saat ini. Ini mengamortisasi overhead planning, tetapi satu rencana bisa menjadi buruk untuk sebagian besar nilai jika distribusinya sangat timpang. EXPLAIN (GENERIC_PLAN) tidak dapat digabungkan dengan ANALYZE.

4. Periksa generic plan terlebih dahulu

sql

EXPLAIN (GENERIC_PLAN)
SELECT sum(amount)
FROM invoices
WHERE tenant_id = $1 AND status = $2;

Periksa tipe scan, estimasi baris, kondisi indeks, urutan join, dan total biaya (cost). Tambahkan type cast eksplisit jika tipe parameter tidak dapat disimpulkan, sehingga masalah tipe tidak disalahartikan sebagai masalah planning.

5. Periksa custom plan yang representatif

Di lingkungan terisolasi, jalankan EXPLAIN (ANALYZE, BUFFERS) EXECUTE dengan nilai dari berbagai ukuran tenant. Bandingkan estimasi dan baris aktual, shared hit dan pembacaan, waktu planning, waktu eksekusi, serta penyortiran disk; jangan hanya membandingkan angka biaya (cost).

6. Periksa statistik dan distribusi

Pastikan bahwa autovacuum atau ANALYZE manual mencakup perubahan terkini, lalu periksa kardinalitas, korelasi, dan most-common values (MCV). Statistics target yang lebih tinggi dapat membantu kolom yang timpang, tetapi ukur akurasi planning sekaligus overhead planning.

7. Tentukan batasan perbaikan

plan_cache_mode=force_custom_plan tingkat sesi dapat menguji apakah custom plan menghilangkan regresi; force_generic_plan cocok untuk kueri stabil dengan planning yang mahal. Perbaikan jangka panjang mungkin berupa indeks, pemisahan kueri, tipe data eksplisit, atau menghindari prepared statement yang tidak perlu di ORM, bukan hanya perubahan setelan global.

8. Validasi pool dan peluncuran

Keberadaan pool membuat pengaturan sesi, masa pakai prepared statement, dan perbedaan versi PostgreSQL menjadi penting. Selama canary rollout, bandingkan p95/p99, waktu planning, buffer hit, dan tingkat kesalahan berdasarkan persentil parameter, ukuran tenant, instans pool, dan node database, dengan sakelar rollback yang siap digunakan.

Pertukaran (trade-offs) dan batasan

Generic plan menghemat pekerjaan planning tetapi kehilangan selektivitas dari nilai saat ini; custom plan dapat membuang CPU planning untuk kueri singkat yang sering dipanggil. EXPLAIN ANALYZE mengeksekusi statement, sehingga statement penulisan memerlukan transaksi rollback atau replika hanya-baca. Biaya rencana adalah estimasi, bukan milidetik; statistik diambil dari sampel, dan rencana dapat berubah seiring data, versi PostgreSQL, dan ANALYZE.

Rencana peluncuran dan bukti

Catat teks kueri, tipe parameter, mode pool, versi PostgreSQL, dan perilaku plan cache.
Buat baseline rencana GENERIC_PLAN dan ANALYZE untuk nilai parameter yang representatif.
Periksa usia statistik, galat estimasi, hit indeks, IO, dan waktu planning.
Uji plan_cache_mode dalam satu koneksi atau sesi canary, jangan langsung mengubah konfigurasi global.
Terima berdasarkan p95/p99, CPU planning, shared read, lock wait, dan galat, sambil tetap menyediakan sakelar rollback.

Kesalahan umum dan tindak lanjut

Kesalahan 1: Menghapus generic plan setelah melihat sequential scan

Sequential scan mungkin merupakan pilihan yang tepat untuk hasil yang besar. Bandingkan baris aktual, IO, dan tail latency untuk nilai-nilai representatif terlebih dahulu.

Kesalahan 2: Menganggap biaya (cost) sebagai waktu nyata

Cost adalah estimasi relatif dari planner. Gabungkan waktu sebenarnya dari ANALYZE, buffer, dan metrik produksi.

Kesalahan 3: Menjalankan EXPLAIN ANALYZE tulis langsung di produksi

ANALYZE mengeksekusi statement tersebut. Validasi operasi penulisan dalam transaksi rollback atau replika terisolasi.

Kesalahan 4: Hanya menaikkan statistics target

Target yang lebih tinggi menambah beban kerja analisis dan planning serta mungkin tidak memperbaiki masalah pool atau tipe parameter. Buktikan manfaatnya dengan pengujian tolok ukur (benchmark).

Kesalahan 5: Mengabaikan batasan sesi pool

plan_cache_mode tingkat sesi dan prepared statement mungkin hanya memengaruhi sebagian koneksi. Pastikan mencakup setiap koneksi pool dan kebijakan daur ulangnya sebelum peluncuran.