ProHoster > Blog > Pentadbiran > Antipattern PostgreSQL: CTE x CTE

Antipattern PostgreSQL: CTE x CTE

Disebabkan barisan kerja saya, saya perlu menangani situasi apabila pembangun menulis permintaan dan berfikir "Dasarnya pintar, ia boleh mengendalikan segala-galanya sendiri!Β«

Dalam sesetengah kes (sebahagiannya daripada ketidaktahuan tentang keupayaan pangkalan data, sebahagiannya daripada pengoptimuman pramatang), pendekatan ini membawa kepada kemunculan "Frankensteins".

Pertama, saya akan memberikan contoh permintaan sedemikian:

-- для ΠΊΠ°ΠΆΠ΄ΠΎΠΉ ΠΊΠ»ΡŽΡ‡Π΅Π²ΠΎΠΉ ΠΏΠ°Ρ€Ρ‹ Π½Π°Ρ…ΠΎΠ΄ΠΈΠΌ ассоциированныС значСния ΠΏΠΎΠ»Π΅ΠΉ
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 menilai secara substantif kualiti permintaan, mari buat beberapa set data arbitrari:

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 begitu membaca data mengambil masa kurang daripada seperempat masa pelaksanaan pertanyaan:

Antipattern PostgreSQL: CTE x CTE[lihat explain.tensor.ru]

Membongkarnya sekeping demi sekeping

Mari kita lihat dengan lebih dekat permintaan itu dan tertanya-tanya:

  1. Mengapa WITH RECURSIVE di sini jika tiada CTE rekursif?
  2. Mengapakah nilai min/maks dikelompokkan dalam CTE yang berasingan jika nilai tersebut kemudian terikat pada sampel asal?
    +25% masa
  3. Mengapa menggunakan 'PILIH * DARI' tanpa syarat pada penghujung untuk mengulang CTE sebelumnya?
    +14% masa

Dalam kes ini, kami sangat bernasib baik kerana Hash Join dipilih untuk sambungan, dan bukan Nested Loop, kerana kami akan menerima bukan hanya satu pas Imbasan CTE, tetapi 10K!

serba sedikit tentang CTE ScanDi sini kita mesti ingat itu CTE Scan adalah serupa dengan Seq Scan - iaitu, tiada pengindeksan, tetapi hanya carian lengkap, yang memerlukan 10K x 0.3ms = 3000ms untuk kitaran oleh cte_max atau 1K x 1.5ms = 1500ms apabila gelung oleh cte_bind!
Sebenarnya, apa yang anda mahu dapatkan sebagai hasilnya? Ya, biasanya ini adalah soalan yang timbul pada minit ke-5 menganalisis pertanyaan "tiga cerita".

Kami mahu mengeluarkan untuk setiap pasangan kunci unik min/maks daripada kumpulan mengikut key_a.
Jadi mari kita gunakan untuk ini fungsi tetingkap:

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);

Antipattern PostgreSQL: CTE x CTE
[lihat explain.tensor.ru]

Memandangkan membaca data dalam kedua-dua pilihan mengambil masa yang sama kira-kira 4-5ms, maka semua masa kita akan bertambah -32% - ini dalam bentuk yang paling tulen beban dikeluarkan dari CPU asas, jika permintaan sedemikian dilaksanakan dengan cukup kerap.

Secara umum, anda tidak boleh memaksa tapak untuk "membawa yang bulat, gulung yang segi empat sama."

Sumber: www.habr.com

Tambah komen