Wawancara Data: Bagaimana Anda Menggunakan PostgreSQL Extended Statistics untuk Memperbaiki Kesalahan Estimasi Kardinalitas?

Pertanyaan dan cakupan

Sebuah kueri PostgreSQL berjalan cepat dengan filter kolom tunggal, tetapi memilih urutan join yang buruk saat memfilter customer_tier, region, dan status secara bersamaan. Diagnosis kesalahan kardinalitas ini, jelaskan kapan harus menggunakan extended statistics, dan tunjukkan bagaimana Anda memvalidasi peningkatan performa beserta batasannya.

Secara default, PostgreSQL terutama mengumpulkan statistik per kolom. Ketika kolom-kolom saling berkorelasi, asumsi independensi perencana kueri (planner) dapat mengalikan selektivitas secara tidak tepat. CREATE STATISTICS dapat mengumpulkan dependensi fungsional, kombinasi most-common-value, atau jumlah distink multivariat, tetapi fitur ini tidak menggantikan indeks atau secara otomatis membuat setiap predikat menjadi akurat.

Yang dievaluasi oleh pewawancara

• Menggunakan EXPLAIN (ANALYZE, BUFFERS) untuk membandingkan baris estimasi dan baris aktual.
• Menjelaskan mengapa kolom yang berkorelasi merusak asumsi independensi.
• Memilih antara dependencies, mcv, dan ndistinct berdasarkan pola kesalahannya.
• Mengetahui bahwa objek extended statistics membutuhkan ANALYZE untuk mengisi datanya.
• Memvalidasi perubahan rencana eksekusi (plan) dengan beban kerja representatif alih-alih menambahkan objek berdasarkan insting.
• Menyatakan batasan terkait sampling, pemeliharaan, ekspresi, dan lintas tabel.

Pertanyaan klarifikasi yang perlu diajukan

1. Apakah kesalahan terjadi pada pemfilteran, operasi join, atau pengelompokan (grouping)?
2. Berapa ukuran tabel, tingkat kemiringan data (skew), frekuensi pembaruan, dan default_statistics_target?
3. Apakah ketiga kolom berada pada satu tabel dengan korelasi yang stabil dalam predikat yang sama?
4. Apakah masalahnya berupa latensi, memori, algoritma join yang buruk, atau biaya sumber daya?
5. Apakah indeks, partisi, dan statistik kolom tunggal saat ini sudah memadai?

Jawaban 30 detik

Saya akan menemukan kesalahan baris estimasi-versus-aktual utama yang pertama dan memeriksa kesegaran statistik serta distribusi data. Jika kolom-kolom pada tabel yang sama memiliki korelasi stabil, saya akan membuat objek dependencies, mcv, atau ndistinct terkecil yang sesuai, menjalankan ANALYZE, lalu membandingkan kesalahan estimasi, metode join, pembacaan buffer, dan tail latency pada parameter yang representatif. Extended statistics meningkatkan pengetahuan perencana kueri; fitur ini tidak menggantikan indeks, partisi, atau pemodelan data. Hubungan lintas tabel, yang berubah seiring waktu, atau yang kurang tersampel (under-sampled) memerlukan tata kelola data dan rencana kueri yang berkelanjutan.

Pembahasan mendalam langkah demi langkah

1. Temukan kesalahan estimasi

Bandingkan baris estimasi dan baris aktual di setiap node pada EXPLAIN (ANALYZE, BUFFERS) dan temukan divergensi orde magnitudo pertama. Catat predikat, urutan join, waktu perencanaan dan eksekusi, serta buffer hits daripada hanya melihat total latensi.

2. Periksa statistik kolom tunggal dan kesegarannya

Pastikan bahwa ANALYZE terbaru telah mencakup tabel tersebut dan periksa pg_stats untuk most-common values, histogram, dan fraksi null. Setelah perubahan data besar, skew parah, atau target statistik yang terlalu kecil, perbaiki sampling dan frekuensi penyegaran sebelum menambahkan objek multivariat.

3. Pilih jenis statistik

dependencies mendeskripsikan hubungan fungsional di mana satu kolom sangat mengimplikasikan kolom lainnya. mcv menangkap kombinasi umum yang mendominasi selektivitas. ndistinct memperkirakan jumlah kombinasi distink dan berguna untuk pengelompokan atau deduplikasi. Beberapa jenis dapat berbagi satu objek, tetapi pola kesalahan dan beban kerja harus menjustifikasi setiap jenis yang dipilih.

CREATE STATISTICS orders_customer_region_stats
  (dependencies, mcv, ndistinct)
  ON customer_tier, region, status
  FROM orders;

ANALYZE orders;

4. Validasi ulang rencana eksekusi

Jalankan kembali kueri dengan parameter yang menyerupai produksi, kondisi cache, dan konkurensi. Bandingkan kesalahan baris pada node-node kunci, metode join, memori, file temporer, serta p95/p99. Rencana eksekusi yang berubah tidak otomatis lebih baik; pastikan penggunaan sumber daya stabil di berbagai nilai parameter.

5. Kelola sampling dan ukuran target

Extended statistics diperoleh melalui sampling, sehingga kombinasi langka atau data yang berubah cepat mungkin terlewatkan. Ukur waktu ANALYZE, beban, dan manfaat sebelum menaikkan target untuk kolom-kolom yang sering diakses (hot columns); jangan menaikkan target global secara membabi buta. Pertahankan cakupan kolom tetap kecil agar biaya pemeliharaan tetap sepadan.

6. Pahami batasan dan alternatif

Extended statistics mendeskripsikan hubungan di dalam satu tabel dan tidak secara langsung memodelkan korelasi lintas tabel atau mengubah jalur akses (access path). Kesalahan lintas tabel mungkin memerlukan penulisan ulang kueri (query rewrite), pra-agregasi, partisi, hasil yang dimaterialisasi, atau perubahan model data. Korelasi yang bervariasi berdasarkan tenant, musim, atau transisi status memerlukan pemantauan berkelanjutan.

7. Bangun pengujian regresi dan pembersihan

Simpan rencana eksekusi dan kesalahan estimasi yang representatif dalam kumpulan uji regresi, lalu jalankan kembali setelah upgrade PostgreSQL, migrasi, dan perubahan skema. Hapus objek yang melayani kueri yang sudah dihapus, menambah biaya pemeliharaan, atau tidak menghasilkan peningkatan terukur, sambil mendokumentasikan alasannya. Hubungkan query fingerprints, objek statistik, perubahan rencana eksekusi, dan latensi produksi.

Contoh jawaban berkualitas tinggi

Saya akan mencari node rencana eksekusi pertama di mana baris estimasi dan aktual berbeda beberapa orde magnitudo, lalu memastikan bahwa statistik kolom tunggal dalam kondisi segar. Jika ketiga kolom pesanan memiliki korelasi yang stabil pada tabel yang sama, saya akan memulai dengan objek dependencies atau mcv terkecil, menjalankan ANALYZE, dan membandingkan kesalahan estimasi, urutan join, pembacaan buffer, serta tail latency dengan parameter yang representatif. Jika masalahnya adalah jumlah kombinasi yang dikelompokkan atau dideduplikasi, saya akan mengevaluasi ndistinct.

Saya tidak akan memperlakukan extended statistics sebagai pengganti indeks atau menjanjikannya untuk menyelesaikan korelasi lintas tabel. Untuk kombinasi langka, distribusi yang berubah-ubah, atau sampel yang tidak memadai, saya akan mengukur biaya dari target yang lebih tinggi dan mempertimbangkan penulisan ulang kueri, pra-agregasi, atau perubahan model data. Rencana eksekusi dan kesalahan estimasi akan dimasukkan ke dalam kumpulan regresi agar manfaatnya tetap dapat dipantau.

Kesalahan umum

• Hanya melihat total latensi → melewatkan sumber kesalahan → bandingkan baris estimasi dan aktual node demi node.
• Mengaktifkan semua jenis statistik secara default → menambah beban pemeliharaan tanpa bukti kebutuhan → pilih kumpulan terkecil yang dijustifikasi oleh kesalahan.
• Melewatkan ANALYZE → perencana kueri tidak memiliki data baru → sertakan langkah penyegaran dan validasi.
• Memperlakukan statistik sebagai indeks → kueri mungkin masih memindai terlalu banyak data → pisahkan antara estimasi dan jalur akses.
• Membuktikan manfaat hanya dengan satu parameter → distribusi dan rencana eksekusi bervariasi → uji berbagai parameter, konkurensi, dan regresi.
• Mengabaikan korelasi lintas tabel → statistik satu tabel tidak dapat memperbaiki join → tulis ulang kueri atau tata ulang modelnya.

Pertanyaan lanjutan dan jawabannya

Kapan Anda lebih memilih dependencies?

Ketika satu kolom hampir sepenuhnya menentukan nilai kolom lainnya, seperti hubungan stabil antara wilayah (region) dan negara bagian tertentu. Buktikan dependensi tersebut dengan data dan kesalahan rencana eksekusi terlebih dahulu.

Apa perbedaan antara mcv dan ndistinct?

mcv berfokus pada kombinasi multikolom yang umum dan selektivitas filter. ndistinct berfokus pada jumlah kombinasi distink, yang berguna untuk pengelompokan, deduplikasi, atau kardinalitas join.

Apakah objek extended statistics diperbarui secara otomatis?

Datanya dikumpulkan oleh ANALYZE, yang dipicu secara otomatis atau manual. Keberadaan definisi objek tidak berarti datanya selalu segar.

Mengapa menaikkan target statistik masih bisa gagal?

Sampling mungkin melewatkan kombinasi langka, dan hubungan antarkolom dapat berubah seiring waktu. Ukur kesalahan estimasi dan biaya ANALYZE, lalu pertimbangkan strategi model data atau perbaikan kueri.

Bagaimana cara Anda memeriksa adanya regresi?

Simpan rencana eksekusi untuk beberapa parameter, bandingkan kesalahan estimasi, penggunaan sumber daya, p95/p99, dan file temporer, serta jalankan kembali setelah perubahan versi, volume data, dan skema.

Kapan Anda harus menghapus objek extended statistics?

Hapus objek tersebut saat kueri sudah tidak digunakan, estimasi tidak membaik, atau biaya pemeliharaan melebihi manfaatnya. Simpan bukti sebelum dan sesudah agar objek tidak menumpuk tanpa batas.