Cara Perancang Menganggar Bilangan Baris

Perancang tidak melihat data anda pada masa merancang pelan. Perancang membaca ringkasan yang telah dikira daripada katalog sistem pg_statistic, yang diisi oleh ANALYZE (dijalankan secara automatik oleh autovacuum). Paparan yang mudah dibaca manusia untuknya ialah pg_stats.

Bagi setiap lajur, pg_stats mendedahkan komponen asas untuk penganggaran:

• null_frac — pecahan nilai NULL.
• n_distinct — bilangan nilai berbeza.
• most_common_vals / most_common_freqs — senarai MCV.
• histogram_bounds — kelompok untuk baki nilai bukan MCV.

Selektiviti ialah pecahan baris yang dianggarkan dikekalkan oleh sesuatu predikat, antara 0 dan 1. Bilangan baris yang dianggarkan ialah:

• estimated_rows = selectivity × total_rows

di mana total_rows datang daripada pg_class.reltuples (turut dikemas kini oleh ANALYZE). Jadi, penganggaran beralih kepada dua soalan: berapakah bilangan baris dalam jadual, dan apakah pecahan yang kekal selepas setiap predikat? Selebihnya ialah perincian tentang cara pecahan itu dikira.

Untuk column = 'value', perancang terlebih dahulu menyemak senarai most_common_vals (MCV). Jika nilai itu ada dalam senarai, perancang menggunakan kekerapan tepat daripada most_common_freqs — tiada pengiraan matematik, hanya carian.

Contoh: jika most_common_vals = {shipped, pending, cancelled} dan most_common_freqs = {0.62, 0.25, 0.08}, maka status = 'shipped' mempunyai selektiviti 0.62. Dalam jadual dengan 1,000,000 baris, anggarannya ialah 620,000 baris.

MCV menjadikan penganggaran taburan yang condong lebih tepat — perancang mengetahui dengan tepat betapa lazimnya nilai yang sangat kerap muncul.

Jika nilai itu tidak terdapat dalam senarai MCV, perancang mengandaikan semua nilai bukan MCV mempunyai kebarangkalian yang sama. Perancang mengira baki kebarangkalian lalu membahagikannya secara sama rata:

• residual = 1 − sum(most_common_freqs) − null_frac
• n_distinct_residual = n_distinct − count(MCVs)
• selectivity = residual / n_distinct_residual

Inilah sebabnya anggaran bagi nilai jarang boleh menjadi kurang tepat apabila ekor panjang itu sendiri condong: andaian seragam dalam baki tidak lagi sesuai. MCV meliputi bahagian utama; baki ialah anggaran rata bagi ekor taburan.

Untuk ketaksamaan seperti amount > 500 atau created_at BETWEEN …, perancang menggunakan histogram_bounds. Sempadan ini membahagikan nilai bukan MCV kepada kelompok yang setiap satunya mengandungi kira-kira bilangan baris yang sama (kedalaman sama, bukan lebar sama).

Untuk menganggarkan amount < X, perancang mencari kedudukan X antara sempadan lalu membuat interpolasi linear dalam kelompok yang mengandungi nilai itu. Dengan N kelompok, setiap kelompok mewakili kira-kira 1/N daripada baris bukan MCV. Oleh itu, perancang mengira kelompok penuh di bawah X serta sebahagian daripada kelompok sempadan.

Dengan berbilang syarat AND pada lajur yang berbeza, PostgreSQL mendarabkan selektiviti dengan mengandaikan lajur tersebut bebas secara statistik:

• sel(A AND B) = sel(A) × sel(B)

Jika status = 'shipped' ialah 0.62 dan country = 'DE' ialah 0.10, perancang menganggarkan 0.062 daripada jadual. Namun, jika pesanan yang dihantar kebanyakannya dari Jerman, pecahan sebenar mungkin 0.30 — anggaran terkurang sebanyak 5 kali ganda. Lajur yang berkorelasi ialah keadaan apabila statistik satu lajur gagal dan pelan runtuh.

Apabila lajur berkorelasi, cipta statistik lanjutan dengan CREATE STATISTICS. Jenis dependencies mengajar perancang tentang kebergantungan fungsi; mcv menyimpan gabungan nilai paling lazim bagi berbilang lajur supaya predikat AND dianggarkan secara bersama, bukannya didarabkan.

Selepas mencipta objek tersebut, anda mesti menjalankan ANALYZE pada jadual untuk mengisinya. Kemudian perancang membaca taburan bersama dan berhenti mengandaikan kebebasan bagi lajur tersebut.

Bilangan baris hasil cantuman dibina berdasarkan anggaran bagi setiap jadual. Untuk cantuman kesamaan, PostgreSQL menganggarkan baris hasil secara kasar seperti berikut:

• rows ≈ (outer_rows × inner_rows) / max(n_distinct_outer, n_distinct_inner)

dengan menggunakan n_distinct kunci cantuman daripada kedua-dua sisi. Inilah sebabnya anggaran satu jadual yang buruk merebak: jika perancang menyangka sisi yang ditapis mempunyai 5 baris sedangkan sebenarnya mempunyai 50,000, setiap cantuman di atasnya mewarisi ralat itu dan mungkin memilih gelung bersarang yang berjalan 50,000 kali dan bukannya cantuman cincangan.

Ketepatan anggaran bergantung sepenuhnya pada statistik yang mendasarinya. Langkah praktikal:

• Jalankan ANALYZE selepas pemuatan pukal; biarkan autovacuum memastikan statistik sentiasa terkini.
• Tingkatkan resolusi pada lajur yang condong dengan ALTER TABLE … ALTER COLUMN … SET STATISTICS n (senarai MCV dan histogram yang lebih besar).
• Tambah CREATE STATISTICS untuk kumpulan lajur yang berkorelasi.
• Bandingkan baris yang dianggarkan dengan baris sebenar menggunakan EXPLAIN ANALYZE untuk mencari nod tempat tekaan mula tersasar.

Nyahpepijat dari bahagian bawah pelan ke atas: nod pertama dengan jurang besar antara anggaran dan keadaan sebenar biasanya ialah punca sebenar.

Kini anda boleh menjejaki anggaran baris dari awal hingga akhir:

• ANALYZE mengisi pg_statistic / pg_stats dan menetapkan reltuples.
• Kesamaan menggunakan senarai MCV apabila nilai itu lazim; jika tidak, ia menggunakan baki seragam berdasarkan n_distinct.
• Julat membuat interpolasi dalam histogram_bounds yang mempunyai kedalaman sama.
• Berbilang AND mendarabkan selektiviti dengan mengandaikan kebebasan — sumber utama ralat penganggaran.
• Statistik lanjutan (dependencies, mcv) membetulkan lajur yang berkorelasi.
• Cantuman menggabungkan anggaran setiap sisi melalui n_distinct kunci cantuman, maka ralat satu jadual merebak ke atas.

Selektiviti × bilangan baris menghasilkan kardinaliti yang mempengaruhi pilihan imbasan, cantuman dan susunan. Kuasai perkara ini dan output EXPLAIN tidak lagi menjadi misteri — ia menjadi cerita yang boleh anda baca.