Antipattern PostgreSQL: CTE x CTE

Berdasarkan jenis kegiatan, seseorang harus berurusan dengan situasi ketika pengembang menulis permintaan dan berpikir " basisnya cerdas, ia dapat menangani semuanya! "

Dalam beberapa kasus (sebagian karena ketidaktahuan tentang kemampuan basis data, sebagian dari optimasi prematur), pendekatan ini mengarah pada penampilan "Frankenstein".

Pertama, saya akan memberikan contoh pertanyaan seperti itu:

--         WITH RECURSIVE cte_bind AS ( SELECT DISTINCT ON (key_a, key_b) key_a a , key_b b , fld1 bind_fld1 , fld2 bind_fld2 FROM tbl ) --  min/max      , cte_max AS ( SELECT a , max(bind_fld1) bind_fld1 , min(bind_fld2) bind_fld2 FROM cte_bind GROUP BY a ) --        min/max- , cte_a_bind AS ( SELECT cte_bind.a , cte_bind.b , cte_max.bind_fld1 , cte_max.bind_fld2 FROM cte_bind INNER JOIN cte_max ON cte_max.a = cte_bind.a ) SELECT * FROM cte_a_bind; 

Untuk mengevaluasi secara obyektif kualitas permintaan, mari kita buat beberapa kumpulan data arbitrer:

 CREATE TABLE tbl AS SELECT (random() * 1000)::integer key_a , (random() * 1000)::integer key_b , (random() * 10000)::integer fld1 , (random() * 10000)::integer fld2 FROM generate_series(1, 10000); CREATE INDEX ON tbl(key_a, key_b); 

Ternyata membaca data itu sendiri membutuhkan kurang dari seperempat dari total waktu eksekusi permintaan:

[lihat menjelaskan.tensor.ru]

Bongkar dengan tulang

Kami akan mencermati permintaan, dan kami akan bingung:

1. Mengapa DENGAN RECURSIVE di sini, jika tidak ada CTE rekursif?
2. Mengapa mengelompokkan nilai min / maks dalam CTE terpisah jika masih melekat pada sampel asli?
   + 25% dari waktu
3. Mengapa pada akhirnya menggunakan pembacaan ulang dari CTE sebelumnya melalui 'SELECT * FROM' tanpa syarat?
   + 14% dari waktu

Dalam hal ini, kami sangat beruntung bahwa Hash Join dipilih untuk koneksi, dan bukan Nested Loop, karena dengan begitu kami tidak akan mendapatkan satu pun CTE Scan pass, tetapi 10K!


Sebenarnya, apa yang ingin Anda dapatkan sebagai hasilnya? Ya, biasanya pertanyaan semacam ini yang ia kunjungi di suatu tempat pada menit ke-5 analisis permintaan "tiga lantai".

Kami ingin setiap pasangan kunci unik untuk menghapus min / maks dari grup dengan key_a .
Jadi kita akan menggunakan fungsi jendela untuk ini:

 SELECT DISTINCT ON(key_a, key_b) key_a a , key_b b , max(fld1) OVER(w) bind_fld1 , min(fld2) OVER(w) bind_fld2 FROM tbl WINDOW w AS (PARTITION BY key_a); 

[lihat menjelaskan.tensor.ru]

Karena membaca data di kedua versi membutuhkan waktu sekitar 4-5ms sama, seluruh waktu perolehan kami -32% adalah beban murni dihapus dari CPU dasar , jika permintaan seperti itu dilakukan cukup sering.

Secara umum, Anda tidak boleh memaksa alas untuk "memakai bulat, guling persegi."