Temuduga Data: Bagaimanakah anda mendiagnosis pelan generik PostgreSQL untuk pertanyaan berparameter?

Prompt dan kes penggunaan

API yang menggunakan kenyataan tersedia (prepared statements) mengalami kependaman ekor panjang (long-tail latency) selepas pelancaran: penyewa kecil adalah pantas, manakala penyewa besar tiba-tiba menerima imbasan berurutan (sequential scans). Terangkan cara PostgreSQL memilih pelan tersuai dan generik, cara membandingkannya dengan EXPLAIN (GENERIC_PLAN), cara mengesahkan statistik, kepencongan parameter, caching, dan plan_cache_mode, serta cara membetulkan isu tersebut tanpa merosakkan transaksi atau kolam sambungan (connection pool).

Perkara yang diuji oleh penemu duga

Mengasingkan kos perancangan, pelaksanaan, dan penyirikan hasil (result-serialization).
Memahami bahawa pelan generik mengabaikan nilai parameter konkrit manakala pelan tersuai boleh menggunakan kepilihan (selectivity).
Menggunakan EXPLAIN ANALYZE dengan selamat tanpa menghantar kesan sampingan penulisan ke pengeluaran.
Menggabungkan statistik, indeks, kolam sambungan, dan pemparameteran untuk mencari regresi.
Memilih auto, force_generic_plan, atau force_custom_plan daripada bukti.

Soalan untuk dijelaskan terlebih dahulu

Adakah pertanyaan dilaksanakan melalui kenyataan tersedia, ORM, atau proksi, dan adakah sambungan diguna semula?
Adakah parameter condong, dengan perbezaan ketara dalam saiz penyewa dan suhu data?
Adakah regresi berlaku dalam perancangan, pelaksanaan, penantian kunci (lock wait), IO, atau penyirikan?
Bolehkah anda mengubah indeks, sasaran statistik, SQL, kolam sambungan, atau tetapan peringkat sesi?

Jawapan tiga puluh saat

Mulakan dengan EXPLAIN (GENERIC_PLAN) untuk melihat pelan yang tidak bergantung pada nilai parameter, kemudian gunakan nilai representatif dengan EXPLAIN ANALYZE EXECUTE untuk memeriksa pelan tersuai dan baris sebenar. Pelan generik menjimatkan kerja perancangan tetapi boleh kekal tidak cekap apabila kepilihan condong. Sahkan statistik dan tingkah laku cache pelan, buat penanda aras bagi perancangan, pelaksanaan, dan kependaman ekor, kemudian tukar plan_cache_mode atau pertanyaan dalam sesi terkawal dan sahkan setiap sambungan kolam.

Jawapan mendalam, langkah demi langkah

1. Asingkan perancangan daripada pelaksanaan

Perancang memilih imbasan dan percantuman (joins) daripada SQL, statistik, dan parameter; pelaksana membaca halaman, menapis baris, dan mengembalikan hasil. Masa dinding (wall time) aplikasi sahaja tidak dapat membuktikan bahawa pelan generik adalah puncanya.

2. Terangkan pelan tersuai

Pelan tersuai dijana untuk parameter semasa dan boleh mengeksploitasi kepilihan. Penyewa kecil mungkin memilih imbasan indeks manakala penyewa besar memilih imbasan berurutan atau susunan percantuman yang berbeza; kosnya ialah perancangan yang berulang.

3. Terangkan pelan generik

Pelan generik menggunakan pemegang tempat (placeholders) dan mengabaikan nilai semasa. Ia melunaskan overhed perancangan, tetapi satu pelan mungkin lemah untuk kebanyakan nilai apabila taburannya sangat condong. EXPLAIN (GENERIC_PLAN) tidak boleh digabungkan dengan ANALYZE.

4. Periksa pelan generik terlebih dahulu

sql

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

Periksa jenis imbasan, anggaran baris, syarat indeks, susunan percantuman, dan jumlah kos. Tambahkan penukaran jenis (casts) eksplisit apabila jenis parameter tidak dapat disimpulkan, supaya isu jenis tidak disalah anggap sebagai isu perancangan.

5. Periksa pelan tersuai yang representatif

Dalam persekitaran terpencil, laksanakan EXPLAIN (ANALYZE, BUFFERS) EXECUTE dengan nilai daripada penyewa pelbagai saiz. Bandingkan anggaran dan baris sebenar, perkongsian hit dan bacaan, masa perancangan, masa pelaksanaan, dan pengisihan cakera; jangan bandingkan nombor kos sahaja.

6. Periksa statistik dan taburan

Sahkan bahawa autovacuum atau ANALYZE manual merangkumi perubahan terkini, kemudian periksa kekardinalan, korelasi, dan nilai paling lazim (MCVs). Sasaran statistik yang lebih tinggi mungkin membantu lajur yang condong, tetapi ukur ketepatan perancangan dan overhed perancangan sekali.

7. Pilih sempadan pembaikan

plan_cache_mode=force_custom_plan peringkat sesi boleh menguji sama ada pelan tersuai menghapuskan regresi; force_generic_plan sesuai untuk pertanyaan stabil dengan perancangan yang mahal. Pembaikan jangka panjang mungkin merupakan indeks, pemisahan pertanyaan, jenis eksplisit, atau mengelakkan kenyataan tersedia yang tidak perlu dalam ORM, bukannya perubahan tetapan global semata-mata.

8. Sahkan kolam dan pelancaran

Kolam sambungan menjadikan tetapan sesi, jangka hayat kenyataan tersedia, dan perbezaan versi PostgreSQL penting. Semasa pelancaran canary, bandingkan p95/p99, masa perancangan, penimbal hit, dan kadar ralat mengikut persentil parameter, saiz penyewa, tika kolam, dan nod pangkalan data, dengan suis kembali asal (rollback switch) sedia ada.

Pertukaran dan sempadan

Pelan generik menjimatkan kerja perancangan tetapi kehilangan kepilihan daripada nilai semasa; pelan tersuai mungkin membazirkan CPU perancangan untuk pertanyaan pendek yang kerap. EXPLAIN ANALYZE melaksanakan kenyataan tersebut, jadi kenyataan penulisan memerlukan transaksi putar balik (rollback) atau replika baca sahaja. Kos pelan ialah anggaran, bukan milisaat; statistik adalah sampel, dan pelan boleh berubah mengikut data, versi PostgreSQL, dan ANALYZE.

Pelan pelancaran dan bukti

Rakam teks pertanyaan, jenis parameter, mod kolam, versi PostgreSQL, dan tingkah laku cache pelan.
Tetapkan garis dasar pelan GENERIC_PLAN dan ANALYZE untuk nilai parameter yang representatif.
Semak usia statistik, ralat anggaran, hit indeks, IO, dan masa perancangan.
Uji plan_cache_mode dalam satu sambungan atau sesi canary, bukannya mengubah konfigurasi global terlebih dahulu.
Terima berdasarkan p95/p99, CPU perancangan, bacaan dikongsi, penantian kunci, dan ralat, sambil mengekalkan suis kembali asal.

Kesilapan lazim dan tindakan susulan

Kesilapan 1: Mengalih keluar pelan generik selepas melihat imbasan berurutan

Imbasan berurutan mungkin betul untuk hasil yang besar. Bandingkan baris sebenar, IO, dan kependaman ekor untuk nilai representatif terlebih dahulu.

Kesilapan 2: Menganggap kos sebagai masa nyata

Kos ialah anggaran relatif perancang. Gabungkan masa sebenar ANALYZE, penimbal, dan metrik pengeluaran.

Kesilapan 3: Menjalankan EXPLAIN ANALYZE penulisan secara langsung dalam pengeluaran

ANALYZE melaksanakan kenyataan tersebut. Sahkan penulisan dalam transaksi putar balik atau replika terpencil.

Kesilapan 4: Hanya menaikkan sasaran statistik

Sasaran yang lebih tinggi menambah kerja analisis dan perancangan serta mungkin tidak membetulkan isu kolam atau jenis parameter. Buktikan faedahnya dengan penanda aras.

Kesilapan 5: Mengabaikan sempadan sesi kolam

plan_cache_mode peringkat sesi dan kenyataan tersedia mungkin mempengaruhi sesetengah sambungan sahaja. Rangkumi setiap sambungan kolam dan dasar kitar semula sebelum pelepasan.