Bagaimana Postgres menghitung perkiraan jumlah baris selama perencanaan kueri

Aug 20 2020

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_1dan frequent_value_2keduanya ada dalam daftar MCV. Dan frekuensinya adalah 0.00002667dan 0.99783masing - 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_distinctnilai 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

2 LaurenzAlbe Aug 20 2020 at 19:34

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.