Bagaimana Postgres menghitung perkiraan jumlah baris selama perencanaan kueri
Saya memiliki pertanyaan sederhana seperti:
SELECT
*
FROM
example
WHERE
filter_1 = ?
AND filter_2 = ?
LIMIT
10
Tabel ini cukup besar (sekitar 100 juta baris) dan memiliki indeks yang mirip dengan berikut (indeks sebenarnya memiliki satu kolom lagi di sisi kanan tetapi seharusnya tidak ada perbedaan apa pun):
CREATE INDEX example_idx
ON public.example
USING btree (filter_1, filter_2, (...));
Jadi sekarang mari kita jelaskan masalahnya: ketika saya menjalankan kueri saya dalam pernyataan yang disiapkan, 5 eksekusi pertama menggunakan rencana khusus. Kemudian rencana umum dipandang lebih murah dan digunakan sebagai pengingat seumur hidup pernyataan yang disiapkan.
Berikut ini JELASKAN ANALISIS ketika Postgres menggunakan rencana khusus:
Limit (cost=0.57..39.35 rows=10 width=78) (actual time=0.024..0.034 rows=8 loops=1)
-> Index Scan using example_idx on example c0 (cost=0.57..12345.29 rows=3183 width=78) (actual time=0.024..0.032 rows=8 loops=1)
Index Cond: (((filter_1)::text = 'rare_value_1'::text) AND (filter_2 = 'frequent_value_2'::custom_enum))
Planning Time: 0.098 ms
Execution Time: 0.045 ms
Berikut ini JELASKAN ketika Postgres menggunakan rencana umum:
Limit (cost=0.00..11.31 rows=10 width=78)
-> Seq Scan on example_idx c0 (cost=0.00..3469262.28 rows=3067235 width=78)
Filter: (((filter_1)::text = $1) AND (filter_2 = $2))
Di sini, kita dapat melihat dengan jelas bahwa biaya paket generik lebih rendah.
Masalah saya adalah bagaimana perkiraan jumlah baris dalam Indeks Scan dan Seq Scan dihitung.
Dokumentasi menjelaskan bagaimana dan jika saya mengikuti perhitungan mereka, saya sampai pada 3183
, yang merupakan perkiraan jumlah baris untuk rencana khusus:
rare_value_1
dan frequent_value_2
keduanya ada dalam daftar MCV. Dan frekuensinya adalah 0.00002667
dan 0.99783
masing - masing. Juga, jumlah baris tabel yang diperkirakan adalah 119622152
.
0.00002667 * 0.99783 * 119622152 = 3183
Pertanyaan yang tersisa adalah, bagaimana hal itu dilakukan untuk rencana umum?
Saya menemukan bahwa, untuk beberapa alasan yang tidak diketahui, frekuensi MCV diabaikan. Dan Postgresql hanya melihat n_distinct
nilai untuk kolom filter_1 dan filter_2 (masing-masing 13 dan 3):
estimated row count = estimated total number of rows in table / ( n_distinct("filter_1") * n_distinct("filter_2") )
= 119622152 / (13 * 3)
= 3067235
Pertanyaan saya adalah mengapa? Mengapa Postgresql menggunakan cara primitif untuk memperkirakan jumlah baris karena memiliki akses ke statistik yang lebih baik dalam bentuk frekuensi MCV?
Versi Postgresql: 11 (jadi menggunakan opsi "force_custom_plan" tidak memungkinkan untuk kami saat ini).
Jawaban
Tidak ada cara yang lebih baik untuk memperkirakan jumlah baris untuk rencana umum.
Jika Anda memilih menggunakan frekuensi nilai paling umum (MCV), akan ada beberapa masalah:
MCV mana yang Anda pilih?
Estimasi tersebut akan lebih baik dengan asumsi bahwa kedua parameter tersebut adalah MCV, tetapi itu adalah asumsi yang tidak berdasar. Jika keduanya merupakan nilai langka, estimasi akan melenceng lebih jauh dari estimasi saat ini.
Memilih rata-rata adalah yang terbaik yang bisa kita lakukan: ini adalah jalan tengah. Kasus Anda sangat jauh dari sasaran karena salah satu nilainya sangat jarang, yang menurunkan perkiraan rencana kustom, tetapi karena ada beberapa nilai yang berbeda dan salah satunya sangat sering, selektivitas rata-rata buruk, dan rencana umum perkiraan terlalu tinggi.
Anda memiliki dua pilihan:
Gunakan SQL dinamis agar Anda selalu mendapatkan paket kustom.
Ubah kueri untuk dibaca
WHERE filter_1 || '' = ?
sehingga tidak dapat menggunakan indeks.