Bagaimanakah Anda Mendiagnosis dan Mengoptimumkan Pertanyaan PostgreSQL yang Perlahan?

Gesaan dan Konteks Berkaitan

Jadual orders mengandungi 200 juta baris dan menampung 3,000 sisipan atau kemas kini status sesaat. Kira-kira 2% daripada semua pesanan adalah pending, walaupun bahagian itu berbeza secara ketara merentas penyewa. Papan pemuka operasi menjalankan pertanyaan berikut 40 kali sesaat. Ia meminta 50 pesanan belum selesai yang terkini untuk satu penyewa dalam tempoh 30 hari yang lalu:

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Jadual ini sudah mempunyai dua indeks B-tree satu lajur, orders(tenant_id) dan orders(created_at). Pemantauan aplikasi menunjukkan bahawa p95 pertanyaan meningkat daripada 120 ms kepada 2.8 saat manakala CPU, memori, dan bilangan sambungan PostgreSQL kekal di bawah kapasiti. Pelan yang ditangkap pada replika skala pengeluaran menganggarkan 8,000 baris tetapi menghasilkan 420,000 baris sebelum pengisihan, menyentuh kira-kira 120,000 penimbal kongsi, dan akhirnya menggunakan isihan top-N untuk mengembalikan 50 baris.

Soalan ini menyasarkan PostgreSQL 18. Saiz jadual, daya pemprosesan, dan angka pelan ialah andaian temu duga yang digunakan untuk menjadikan penaakulan boleh diuji. Matlamatnya adalah untuk menambah baik bacaan frekuensi tinggi ini sambil mengawal risiko pembinaan indeks, storan, dan amplifikasi penulisan. Sharding, caching, dan pengembangan perkakasan berada di luar langkah pertama ini.

Perkara yang Dinilai oleh Penemu Duga

Isyarat pertama ialah pengesahan beban kerja sebelum perubahan SQL. Jawapan yang kukuh memisahkan "pelaksanaan tunggal paling perlahan" daripada "kos kumulatif terbesar." Pertanyaan dengan purata 80 ms pada 1,000 panggilan sesaat mungkin memerlukan perhatian sebelum pertanyaan lima saat yang berlaku sekali-sekala. pg_stat_statements membekalkan panggilan, jumlah masa pelaksanaan, dan purata masa pelaksanaan. Pemantauan aplikasi atau pengesanan masih mesti membekalkan p95 dan persentil khusus penyewa.

Isyarat kedua ialah membaca pelan pelaksanaan sebagai rantaian sebab akibat. Bukti yang relevan termasuk jurang kardinaliti 8,000 berbanding 420,000, baris yang dikeluarkan oleh nod imbasan, loops bagi setiap nod, aktiviti penimbal, kaedah isihan, dan sama ada predikat muncul dalam keadaan indeks atau Penapis pasca-imbasan. Melihat Seq Scan atau melihat bahawa indeks telah digunakan tidak menentukan sama ada pelan itu baik.

Isyarat ketiga ialah memperoleh susunan kunci daripada bentuk pertanyaan. tenant_id ialah predikat kesaksamaan. created_at ialah kedua-dua predikat julat dan susunan yang diminta. status='pending' ialah keadaan perniagaan yang tetap dan jarang berlaku. Indeks yang sesuai harus memasuki julat belum selesai bagi satu penyewa, membaca dalam susunan cap masa, dan berhenti sebaik sahaja 50 baris ditemui.

Akhir sekali, penemu duga mencari disiplin pengesahan dan pelancaran. Indeks menggunakan storan dan I/O pembinaan sambil menambah kerja pada sisipan dan peralihan status. Jawapan yang lengkap membandingkan pelan pada data skala pengeluaran, cache sejuk dan hangat, saiz penyewa yang berbeza, penulisan serentak, dan ambang undur (rollback) yang jelas. Satu pelaksanaan tempatan yang lebih pantas bukanlah bukti yang mencukupi.

Soalan untuk Dijelaskan Sebelum Menjawab

• Ukuran kependaman manakah yang merosot? p95 bagi setiap penyewa, p95 global, purata kependaman, dan jumlah masa pangkalan data membayangkan keutamaan yang berbeza. Tetapkan bila kemerosotan bermula dan sama ada ia sejajar dengan pertumbuhan data, pengedaran parameter, penggunaan (deployment), atau perubahan statistik.
• Apakah kadar pesanan belum selesai dan pengedaran penyewa? Indeks separa adalah menarik jika pending kekal 1% hingga 2% daripada baris. Kelebihan saiznya mengecil jika separuh daripada jadual belum selesai. Satu purata juga menyembunyikan kecondongan antara penyewa yang sangat besar dan kecil.
• Adakah pertanyaan sentiasa mengandungi nilai literal status='pending'? Indeks separa boleh digunakan hanya apabila perancang boleh membuktikan bahawa syarat pertanyaan membayangkan predikat indeks. Parameter status generik mungkin menghalang pembuktian tersebut.
• Lajur dan jaminan ketekalan manakah yang diperlukan? Mengembalikan teks besar, JSON, atau sepuluh jadual yang digabungkan akan membesarkan (bloat) indeks meliputi dengan cepat. Sahkan lajur yang sebenarnya diperlukan oleh titik akhir senarai ini.
• Sejauh manakah jadual ini sarat dengan penulisan, dan apakah pelancaran yang dibenarkan? Pada 3,000 penulisan sesaat, kelebaran indeks dan kos peralihan mesti diukur. Pengeluaran mungkin memerlukan CREATE INDEX CONCURRENTLY, bersama-sama dengan tempoh pembinaan yang lebih lama, imbasan tambahan, dan prosedur untuk membersihkan indeks tidak sah selepas kegagalan.
• Bolehkah pelan sebenar dijalankan pada replika skala pengeluaran? EXPLAIN ANALYZE melaksanakan pernyataan tersebut. Malah SELECT boleh menghasilkan beban yang ketara, manakala pernyataan penukaran data melaksanakan kesan sampingannya. Gunakan replika, parameter terhad, atau EXPLAIN biasa terlebih dahulu.

Rangka Kerja Jawapan 30 Saat

"Mula-mula saya akan mengaitkan p95 aplikasi dengan panggilan pg_stat_statements, jumlah masa pangkalan data, dan penyewa yang perlahan, sambil menolak penantian kunci dan kebergantungan luaran. Kemudian saya akan menjalankan EXPLAIN (ANALYZE, BUFFERS) dengan parameter perwakilan pada replika skala pengeluaran dan memeriksa anggaran berbanding baris sebenar, gelung, penimbal, dan nod isihan. Di sini, dua indeks satu lajur masih menghasilkan 420,000 calon sebelum pengisihan. Oleh kerana pertanyaan sentiasa menyasarkan keadaan belum selesai yang jarang berlaku, saya akan menguji indeks meliputi separa pada (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending', yang boleh membaca 50 baris pertama mengikut susunan. Jika status mesti diparameterkan, saya akan membandingkan indeks penuh (tenant_id, status, created_at DESC). Saya akan mengesahkan p95, kerja penimbal, saiz indeks, dan kependaman penulisan merentas saiz penyewa, cache sejuk dan hangat, serta penulisan serentak sebelum membina secara serentak dengan ambang undur."

Perbincangan Mendalam Langkah Demi Langkah

Langkah 1: Utamakan daripada beban kerja sebenar

Petakan laluan, penyewa, julat parameter, dan p95 aplikasi kepada pertanyaan pangkalan data yang dinormalisasikan. Jika pg_stat_statements didayakan, mulakan dengan penggunaan sumber kumulatif:

SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time mencari masa pangkalan data yang terkumpul melalui panggilan kerap, mean_exec_time menyerlahkan pelaksanaan individu yang mahal, dan calls menunjukkan pengganda. Paparan tersebut tidak mendedahkan p95 atau menjelaskan mengapa penyewa atau parameter tertentu lambat, jadi kekalkan persentil bahagian aplikasi dan kohort parameter. Jika kependaman disebabkan terutamanya oleh penantian kunci, giliran sambungan, rangkaian, atau panggilan hiliran, menukar pelan pertanyaan sahaja tidak akan membaiki kependaman hujung ke hujung.

Langkah 2: Kumpulkan bukti pelaksanaan sebenar dengan selamat

Periksa bentuk pertanyaan dengan EXPLAIN biasa terlebih dahulu. Kemudian, pada replika skala pengeluaran atau persekitaran terkawal, jalankan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Baca ke atas daripada nod terdalam dengan masa sebenar yang besar. Anggap actual rows × loops sebagai sebahagian daripada jumlah kerja sesuatu nod. Buffers: shared read merekodkan blok yang terpaksa dibaca daripada storan; shared hit bermaksud blok tersebut sudah berada dalam penimbal kongsi, tetapi capaian tersebut masih menggunakan CPU dan lebar jalur memori. Isihan bersandarkan cakera melaporkan isihan luaran dan I/O blok sementara, mendorong penyiasatan terhadap saiz input dan bajet memorinya.

Anggaran 8,000 baris berbanding 420,000 baris sebenar ialah ralat 52.5 kali ganda. Statistik mungkin lapuk, atau statistik satu lajur mungkin gagal mewakili korelasi antara tenant_id dan status. Jalankan ANALYZE dengan skop yang sesuai dan ukur sekali lagi. Jika korelasi yang stabil menjejaskan pelan secara ketara, uji statistik lanjutan untuk lajur tersebut. Statistik lanjutan membawa kos pengumpulan dan perancangan, jadi ciptakannya hanya untuk lajur yang berkait rapat yang menambah baik anggaran penting.

Langkah 3: Terbitkan indeks daripada bentuk pertanyaan

Perancang mungkin menggabungkan indeks satu lajur sedia ada dengan BitmapAnd, tetapi hasil peta bit tidak mengekalkan susunan satu B-tree. Ia masih boleh melawat banyak halaman tindanan (heap pages) dan mengisih. Sebagai alternatif, perancang boleh memilih satu indeks dan menapis predikat lain selepas itu. Mempunyai kedua-dua indeks hanya mencipta laluan calon; ia tidak menghasilkan laluan yang dibentuk untuk WHERE + ORDER BY + LIMIT.

Untuk keadaan belum selesai yang tetap dan jarang berlaku, bandingkan indeks separa terlebih dahulu:

CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'pending';

CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);

Indeks pertama hanya menyimpan pesanan belum selesai dan oleh itu sepatutnya lebih kecil di bawah pengedaran yang dinyatakan. Selepas mencari kesaksamaan penyewa, created_at kedua-duanya mengekang julat 30 hari dan membekalkan susunan menurun, membolehkan imbasan berhenti selepas 50 baris. id dan total_cents ialah lajur muatan dalam INCLUDE; ia tidak mengambil bahagian dalam carian atau pengisihan dan hanya membolehkan bacaan indeks sahaja (index-only read).

Indeks berbilang lajur penuh sesuai untuk status berparameter atau beberapa status yang menggunakan corak pertanyaan yang sama. Lajur kesaksamaan B-tree mendahului mengecilkan julat sebelum julat masa dan pengisihan. "Sentiasa letakkan lajur paling selektif dahulu" adalah terlalu mentah; kesaksamaan, julat, susunan, dan penggunaan semula merentas templat pertanyaan sebenar menentukan susunan kunci bersama-sama.

Langkah 4: Nyatakan had indeks separa dan meliputi

Indeks separa boleh digunakan hanya jika perancang boleh membuktikan bahawa syarat pertanyaan merangkumi status='pending'. Dalam pernyataan disediakan generik yang ditulis sebagai status = $2, parameter itu tidak dapat membayangkan predikat untuk setiap nilai yang mungkin, jadi perancang mungkin mengabaikan indeks separa. Pertanyaan operasi khusus boleh mengekalkan literal, atau reka bentuk boleh menggunakan indeks berbilang lajur penuh. Sahkan keputusan dengan templat pertanyaan sebenar dan bukannya menyimpulkannya daripada definisi indeks.

INCLUDE tidak menjamin Imbasan Indeks Sahaja pada setiap pelaksanaan. PostgreSQL masih mesti mengesahkan keterlihatan MVCC. Apabila halaman tindanan ketiadaan bit all-visible, imbasan akan melawat tindanan (heap). Sisipan yang kerap dan perubahan status pada jadual hangat menjadikan lawatan sedemikian lebih berkemungkinan. Jika pelan masih melaporkan banyak Heap Fetches, bandingkan indeks yang lebih sempit tanpa lajur muatan. Indeks yang lebar juga meningkatkan penggunaan cakera dan cache serta menambah kerja penyelenggaraan pada setiap penulisan yang terjejas.

Langkah 5: Sahkan faedah dan kos

Bandingkan pelan sebelum dan selepas menggunakan parameter perwakilan yang sama: penyewa yang sangat besar, median, dan kecil; penyewa dengan banyak baris belum selesai dan hampir tiada; cache sejuk dan cache hangat. Rekodkan taburan kependaman, baris sebenar, penimbal, gelagat isihan, I/O sementara, Heap Fetches, dan saiz indeks. Satu masa berlalu sensitif kepada cache dan keserentakan, manakala kerja pelan menjelaskan sebab hasilnya berubah.

Kemudian lakukan ujian beban bagi sisipan dan peralihan pending → paid pada nisbah pengeluaran. Pesanan yang selesai membuang entri daripada indeks separa; indeks penuh mengemas kini kunci statusnya. Kedua-duanya mengenakan kerja penulisan. Kriteria penerimaan boleh memerlukan p95 bacaan berada dalam sasaran dan tiada kemerosotan p99 yang ketara, pengurangan besar dalam baris calon dan kerja blok kongsi, serta p95 penulisan, volum WAL, storan, dan ketinggalan replikasi (replication lag) dalam bajet.

Sebelum pelancaran, sahkan ruang simpanan cakera dan pemantauan untuk pembinaan serentak. CREATE INDEX CONCURRENTLY membenarkan sisipan, kemas kini, dan pemadaman diteruskan, tetapi ia mengambil masa yang lebih lama dan boleh meninggalkan indeks tidak sah selepas kegagalan. Setelah dibina, sahkan bahawa templat pertanyaan pengeluaran sebenarnya memilih laluan baharu dan perhatikan kitaran puncak penuh. Jika kependaman penulisan atau replikasi melangkaui ambangnya, alih keluar laluan pertanyaan baharu dan gugurkan indeks baharu mengikut prosedur operasi. Kekalkan indeks lama sehingga penggantian telah melepasi tempoh kestabilan dan tiada beban kerja lain bergantung padanya.

Contoh Jawapan Berkualiti Tinggi

"Mula-mula saya akan memastikan bahawa SQL ini layak diberi keutamaan. Pemantauan aplikasi memberi saya p95 dan penyewa yang perlahan, manakala pg_stat_statements memberi panggilan, jumlah masa pelaksanaan, dan purata masa pelaksanaan. Jika ia berjalan 40 kali sesaat dan berada pada kedudukan tinggi dalam masa pangkalan data kumulatif, saya akan menangkap templat pertanyaan sebenar dan penyewa perwakilan, kemudian menjalankan EXPLAIN (ANALYZE, BUFFERS) pada replika skala pengeluaran.

Masalah utama dalam pelan ini ialah imbasan yang luas: perancang menganggarkan 8,000 baris, 420,000 baris sebenarnya mencapai isihan top-N, dan pertanyaan menyentuh kira-kira 120,000 penimbal. Indeks satu lajur mungkin menggabungkan penapis, tetapi ia tidak secara langsung mencipta julat tersusun untuk tenant_id + pending + created_at DESC. Saya akan menyegarkan statistik dan mengukur sekali lagi. Jika korelasi penyewa dan status terus menyebabkan ralat anggaran, saya akan menguji statistik lanjutan.

Oleh kerana pertanyaan operasi sentiasa meminta keadaan belum selesai yang jarang berlaku, saya akan menguji indeks separa pada (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending'. Ia mengehadkan populasi yang diindeks, memasuki julat masa tersusun bagi satu penyewa, dan boleh berhenti selepas 50 baris. Jika aplikasi memparameterkan status dan menanyakan beberapa nilai, saya akan membandingkan indeks penuh (tenant_id, status, created_at DESC). INCLUDE hanya membolehkan kemungkinan Imbasan Indeks Sahaja; halaman hangat mungkin masih memerlukan lawatan tindanan, jadi saya akan memeriksa Heap Fetches.

Pengesahan akan merangkumi penyewa besar, sederhana, dan kecil, cache sejuk dan hangat, serta penulisan serentak. Saya akan membandingkan p95 dan p99, baris calon, penimbal, I/O sementara, saiz indeks, WAL, kependaman penulisan, dan ketinggalan replikasi. Saya akan membina secara serentak dalam pengeluaran, mengesahkan templat sebenar menggunakan pelan baharu, dan memerhatikan kitaran puncak yang lengkap. Jika keuntungan bacaan lemah atau laluan penulisan melebihi bajet, saya akan menarik balik laluan tersebut dan menggugurkan indeks baharu dan bukannya menyembunyikan pelan yang tidak dapat dijelaskan di sebalik lebih banyak perkakasan."

Kesilapan Lazim

• Menambah indeks sebaik sahaja SQL kelihatan perlahan → Frekuensi, parameter, dan jenis penantian masih tidak diketahui, jadi pasukan mungkin mengoptimumkan pertanyaan berkeutamaan rendah → Kaitkan persentil aplikasi, pg_stat_statements, dan parameter sebenar terlebih dahulu.
• Menganggap setiap Seq Scan sebagai kecacatan → Imbasan berjujukan mungkin lebih murah untuk jadual kecil atau pertanyaan yang mengembalikan sebahagian besar daripadanya → Bandingkan baris sebenar, penimbal, dan jumlah kos berbanding alternatif.
• Hanya menyemak sama ada pelan menyatakan Index Scan → Imbasan indeks masih boleh membaca ratusan ribu entri dan berulang kali melawat tindanan → Periksa actual rows × loops, Penapis, Penimbal, dan Capaian Tindanan (Heap Fetches).
• Mengabaikan jurang baris anggaran berbanding baris sebenar → Kardinaliti yang salah boleh mendorong cantuman, imbasan, dan isihan yang lemah → Segarkan statistik dan uji statistik lanjutan untuk lajur berkorelasi yang stabil.
• Menganggap berbilang indeks satu lajur sama dengan satu indeks berbilang lajur → Gabungan peta bit secara amnya kehilangan susunan yang diperlukan dan boleh melawat banyak halaman tindanan → Terbitkan kunci daripada kesaksamaan, julat, susunan, dan LIMIT.
• Meletakkan setiap lajur yang dikembalikan dalam INCLUDE → Kembung indeks (index bloat) mengurangkan kecekapan cache dan menguatkan penulisan → Liputi hanya lajur sempit yang diperlukan oleh pertanyaan bernilai tinggi.
• Membina indeks separa tanpa menguji templat pertanyaan → Predikat berparameter mungkin tidak membayangkan predikat indeks semasa waktu perancangan → Jalankan EXPLAIN pada bentuk disediakan yang sama yang digunakan dalam pengeluaran.
• Menjalankan EXPLAIN ANALYZE pada SQL pangkalan data utama secara sebarangan → Ia melaksanakan pernyataan tersebut; bacaan yang berat menghasilkan beban dan penulisan melaksanakan kesan sampingan → Gunakan EXPLAIN biasa terlebih dahulu dan dapatkan bukti sebenar pada replika atau di dalam transaksi terkawal.
• Melaporkan satu larian yang turun daripada 2.8 saat kepada nombor yang lebih rendah → Cache, parameter, dan keserentakan boleh mencipta kemenangan secara tidak sengaja → Bandingkan taburan, kerja pelan, dan kitaran puncak penuh.

Soalan Susulan dan Maklum Balas

Susulan 1: Mengapakah imbasan berjujukan boleh menjadi lebih pantas daripada imbasan indeks?

Apabila pertanyaan membaca sebahagian besar daripada jadual, capaian berjujukan mengelakkan banyak capaian rawak yang terlibat dalam merentasi indeks dan mengambil halaman tindanan yang bertaburan. Jadual kecil mungkin hanya menduduki beberapa halaman, menjadikan imbasan langsung lebih murah juga. Bandingkan penimbal dan jumlah masa berlalu pada data sebenar dan bukannya menilai pelan mengikut nama nod.

Susulan 2: Mengapakah indeks (tenant_id) dan (created_at) yang berasingan tidak mencukupi?

Perancang boleh memilih satu indeks dan menapis selepas itu atau menggabungkan kedua-duanya dengan BitmapAnd. Peta bit mengumpul lokasi tupel calon dan tidak mengekalkan susunan B-tree bagi created_at, jadi ia biasanya membaca banyak halaman tindanan dan kemudian mengisih. Indeks berbilang lajur meletakkan kesaksamaan penyewa, julat masa, dan susunan pada satu laluan capaian tersusun, membolehkan LIMIT 50 berhenti awal.

Susulan 3: Mengapakah PostgreSQL mungkin tidak pernah menggunakan indeks separa?

Perancang mesti mengenali semasa perancangan bahawa syarat pertanyaan membayangkan predikat indeks. status='pending' sepadan secara langsung; status=$2 tidak dapat menjanjikan padanan untuk setiap parameter. Ungkapan yang ditulis secara berbeza, taburan data yang berubah, atau anggaran kos yang menjadikan capaian tindanan kelihatan mahal juga boleh memilih laluan lain. Jalankan EXPLAIN pada templat yang disediakan sebenar, dan gunakan indeks berbilang lajur penuh apabila status mesti kekal generik.

Susulan 4: Bagaimana jika anggaran kekal salah sebanyak 50 kali ganda selepas ANALYZE?

Semak liputan persampelan, sasaran statistik setiap lajur, dan sama ada data berubah secara mendadak. Jika tenant_id dan status berkorelasi kuat, statistik satu lajur menganggarkan predikat tersebut sebagai bebas. Uji kebergantungan atau statistik lanjutan MCV untuk kumpulan lajur tersebut. Statistik lanjutan menambah baik anggaran; ia tidak mencipta laluan capaian yang hilang, jadi sahkan indeks dan bentuk pertanyaan secara berasingan.

Susulan 5: Bacaan bertambah baik, tetapi p95 penulisan meningkat. Bagaimanakah anda membuat keputusan?

Kembali kepada SLO dan jumlah beban kerja: kuantifikasi masa pangkalan data yang dijimatkan pada bacaan, pengguna yang terjejas oleh kemerosotan penulisan, dan perubahan dalam WAL, storan, dan ketinggalan replikasi. Jika hanya satu status tetap yang jarang berlaku memerlukan pecutan, indeks separa sempit mungkin mengatasi indeks meliputi penuh. Jika lajur muatan menyebabkan kembung, alih keluar INCLUDE dan terima beberapa capaian tindanan. Apabila bajet penulisan dilampaui, tarik balik pelan indeks dan cari laluan capaian yang lebih kecil melalui skop pertanyaan, jaminan penomboran halaman (pagination), atau model data.