ProHoster > blog > administrasi > Antipola PostgreSQL: CTE x CTE

Antipola PostgreSQL: CTE x CTE

Karena pekerjaan saya, saya harus menghadapi situasi ketika seorang pengembang menulis permintaan dan berpikir “Basisnya cerdas, dapat menangani semuanya sendiri!«

Dalam beberapa kasus (sebagian karena ketidaktahuan akan kemampuan database, sebagian karena optimasi prematur), pendekatan ini mengarah pada munculnya “Frankensteins”.

Pertama, saya akan memberikan contoh permintaan 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 kualitas permintaan secara substansial, mari 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 itu membaca data memakan waktu kurang dari seperempat waktu eksekusi kueri:

Antipola PostgreSQL: CTE x CTE[lihat penjelasan.tensor.ru]

Membongkarnya sepotong demi sepotong

Mari kita lihat lebih dekat permintaan tersebut dan menjadi bingung:

  1. Mengapa WITH RECURSIVE ada di sini jika tidak ada CTE rekursif?
  2. Mengapa mengelompokkan nilai min/maks dalam CTE terpisah jika nilai tersebut kemudian dikaitkan dengan sampel asli?
    +25% waktu
  3. Mengapa menggunakan 'SELECT * FROM' tanpa syarat di akhir untuk mengulangi CTE sebelumnya?
    +14% waktu

Dalam hal ini, kami sangat beruntung karena Hash Join dipilih untuk koneksi tersebut, bukan Nested Loop, karena dengan begitu kami tidak hanya menerima satu tiket CTE Scan, namun 10K!

sedikit tentang CTE ScanDi sini kita harus mengingatnya Pemindaian CTE mirip dengan Pemindaian Seq - yaitu, tidak ada pengindeksan, tetapi hanya pencarian lengkap, yang diperlukan 10K x 0.3 md = 3000ms untuk siklus dengan cte_max или 1K x 1.5 md = 1500ms saat mengulang dengan cte_bind!
Sebenarnya apa yang ingin Anda dapatkan sebagai hasilnya? Ya, biasanya ini adalah pertanyaan yang muncul pada menit ke-5 saat menganalisis kueri "tiga lantai".

Kami ingin menghasilkan keluaran untuk setiap pasangan kunci unik min/maks dari grup berdasarkan key_a.
Jadi mari kita gunakan untuk ini fungsi jendela:

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

Antipola PostgreSQL: CTE x CTE
[lihat penjelasan.tensor.ru]

Karena membaca data di kedua opsi memakan waktu yang sama sekitar 4-5 ms, maka seluruh waktu kita bertambah -32% - ini dalam bentuknya yang paling murni beban dihapus dari CPU dasar, jika permintaan seperti itu dijalankan cukup sering.

Secara umum, Anda tidak boleh memaksakan alasnya untuk “membawa yang bulat, menggulung yang persegi”.

Sumber: www.habr.com

Tambah komentar