Masalah dan konteks
Tabel peristiwa multi-tenant memiliki indeks B-tree pada (tenant_id, created_at). Kueri baru hanya menyediakan rentang created_at, dan versi yang lebih lama sering kali memilih sequential scan. Menggunakan skip scan pada PostgreSQL 18, jelaskan bagaimana pengoptimal dapat menggunakan kolom sufiks, cara mengukur keuntungannya, dan mengapa ini bukan pengganti universal untuk indeks yang dibuat khusus.
Apa yang dievaluasi oleh pewawancara
Pembeda utamanya adalah aturan leftmost-prefix pada B-tree dan strategi skip-scan. PostgreSQL 18 dapat menghitung nilai kolom utama yang unik (distinct leading-column values) dan melakukan pencarian sufiks untuk masing-masing nilai tersebut. Biaya bergantung pada kardinalitas prefiks, selektivitas sufiks, korelasi tabel/indeks, dan statistik. Kandidat harus membuktikan hasilnya dengan EXPLAIN (ANALYZE, BUFFERS).
Pertanyaan klarifikasi untuk diajukan terlebih dahulu
Distribusi data
Tanyakan jumlah tenant, baris per tenant, lebar rentang waktu, dan apakah data dikelompokkan berdasarkan waktu. Banyaknya nilai prefiks yang berbeda dapat membuat pencarian berulang menjadi lebih mahal daripada sequential scan.
Beban kerja dan versi
Konfirmasikan bahwa server adalah PostgreSQL 18, frekuensi kueri, izin untuk menambahkan indeks, dan perilaku penulisan bersamaan (concurrent-write). Skip scan adalah pilihan rencana eksekusi, bukan jaminan SQL.
Garis dasar pengukuran
Konfirmasikan output EXPLAIN yang ada, tingkat hit buffer (buffer hit rate), waktu eksekusi, dan garis dasar cold-cache. Bandingkan rencana aktual daripada estimasi biaya atau satu kali eksekusi warm-cache.
Kerangka jawaban 30 detik
“Aturan tradisional untuk (tenant_id, created_at) membutuhkan predikat tenantid terlebih dahulu. PostgreSQL 18 dapat, ketika biayanya menguntungkan, mencoba setiap tenantid yang berbeda dan menggunakan rentang created_at untuk melompati rentang indeks yang tidak terkait. Saya akan menyegarkan statistik dan membandingkan skip scan, sequential scan, dan indeks (created_at) khusus menggunakan EXPLAIN ANALYZE BUFFERS. Kardinalitas prefiks yang tinggi, rentang yang lebar, atau korelasi yang buruk dapat membuat skip scan menjadi lebih lambat.”
Langkah solusi terperinci
Langkah 1: Petakan kolom indeks ke predikat
Buat daftar urutan indeks, predikat kesetaraan, predikat rentang, dan persyaratan pengurutan. Skip scan membantu B-tree multikolom ketika kolom awal tidak memiliki batasan yang berguna tetapi kolom berikutnya memiliki predikat yang selektif; hal ini tidak mengubah urutan kunci fisik.
Langkah 2: Jelaskan biaya enumerasi prefiks
Pengoptimal dapat memperlakukan setiap nilai awal yang berbeda sebagai entri pencarian implisit dan mencari rentang sufiks. Jumlah probe dipengaruhi oleh kardinalitas prefiks dan kesalahan estimasi; kardinalitas yang lebih tinggi berarti lebih banyak akses acak dan pemosisian berulang.
Langkah 3: Segarkan statistik dan periksa rencana
Jalankan ANALYZE agar jumlah nilai unik, histogram, dan statistik korelasi mencerminkan data saat ini. Tangkap EXPLAIN (ANALYZE, BUFFERS, SETTINGS) dengan baris aktual, shared hit, pembacaan, node rencana, dan pengaturan pengoptimal yang relevan.
Langkah 4: Bangun garis dasar yang dapat dibandingkan
Pada snapshot data yang sama, bandingkan indeks yang ada dengan skip scan, sequential scan, dan indeks kolom sufiks yang baru. Uji cold dan warm cache, rentang sempit dan lebar, serta kemiringan tenant (tenant skew) daripada mengandalkan satu sampel saja.
Langkah 5: Perhitungkan biaya covering dan heap
Jika kueri memproyeksikan banyak kolom non-indeks, kunjungan heap setelah skip scan mungkin mendominasi. Periksa apakah indeks mencakup proyeksi (covering), apakah peta visibilitas memungkinkan index-only scan, dan apakah akses heap acak menghapus keuntungan pemfilteran.
Langkah 6: Kelola stabilitas rencana
Pertumbuhan data mengubah kardinalitas dan selektivitas prefiks, sehingga pengoptimal dapat beralih antara skip scan, sequential scan, dan indeks lainnya. Catat sidik jari rencana dan latensi p95; sesuaikan target statistik atau tambahkan indeks yang cocok dengan jalur akses dominan saat diperlukan.
Langkah 7: Rencanakan peningkatan versi dan rollback
Setelah meningkatkan versi ke PostgreSQL 18, kumpulkan kembali statistik dan putar ulang (replay) lalu lintas yang representatif. Pantau pembacaan buffer, CPU, waktu tunggu kunci, dan latensi ekor (tail latency); jika rencana mengalami penurunan performa, kembali ke indeks atau bentuk kueri yang stabil sebelum memutuskan apakah akan mempertahankan rencana skip-scan.
Contoh jawaban berkualitas tinggi
Untuk (tenant_id, created_at), saya memperlakukan skip scan sebagai rencana berbasis biaya: menghitung nilai tenantid, lalu mencari rentang createdat untuk masing-masing nilai. Saya akan menjalankan ANALYZE dan membandingkan EXPLAIN ANALYZE BUFFERS terhadap sequential scan dan indeks (created_at) dalam kondisi cold dan warm cache serta lebar rentang yang berbeda. Jika kardinalitas prefiks, kunjungan heap, atau lebar rentang membuat probe berulang menjadi mahal, saya akan menambahkan indeks sufiks atau covering dan memantau stabilitas rencana setelah peningkatan versi.
Kesalahan umum
- Kesalahan: Mengasumsikan indeks multikolom apa pun memfilter sufiksnya secara efisien. → Mengapa: Aturan leftmost-prefix tetap berlaku dan skip scan berbasis biaya. → Perbaikan: Validasi rencana aktual dan distribusi data.
- Kesalahan: Memperlakukan skip scan sebagai tipe indeks baru. → Mengapa: Ini adalah strategi akses pengoptimal. → Perbaikan: Nyatakan bahwa indeks fisik tidak berubah.
- Kesalahan: Hanya membandingkan perkiraan biaya. → Mengapa: Statistik bisa saja salah. → Perbaikan: Ukur ANALYZE BUFFERS di berbagai kondisi cache.
- Kesalahan: Mengabaikan biaya heap dan covering. → Mengapa: Pemfilteran cepat mungkin masih memerlukan banyak pengambilan baris (row fetch). → Perbaikan: Evaluasi kelayakan index-only dan akses heap.
Pertanyaan lanjutan dan jawaban
Lanjutan 1: Jika hanya ada dua tenant, apakah skip scan akan selalu dipilih?
Tidak. Selektivitas sufiks, korelasi halaman, status cache, dan estimasi biaya tetap berpengaruh; sequential scan mungkin lebih murah.
Lanjutan 2: Apakah skip scan melompati setiap halaman leaf yang tidak cocok?
Metode ini melakukan beberapa pencarian menggunakan nilai prefiks yang berbeda dan menghindari rentang yang tidak terkait, tetapi setiap pencarian masih memiliki biaya pemosisian dan kemungkinan pengambilan heap. Lompatan tersebut tidak gratis.
Lanjutan 3: Mengapa rencana masih bisa salah setelah ANALYZE?
Korelasi multikolom, kemiringan data (skew), nilai parameter, dan status cache melebihi model statistik dasar. Gunakan statistik yang diperluas (extended statistics), pemutaran ulang parameter yang representatif, dan pemantauan p95 jangka panjang.
Lanjutan 4: Kapan indeks langsung (created_at) lebih baik?
Ketika kueri khusus sufiks adalah jalur utama yang stabil, kardinalitas prefiks tinggi, rentang lebar, atau pengambilan heap mendominasi, indeks khusus menghindari probe prefiks yang berulang. Seimbangkan hal itu terhadap amplifikasi penulisan, penyimpanan, dan kueri lain yang menggunakan indeks asli.