ProHoster > Blog > İdarə > PostgreSQL Antipatternləri: CTE x CTE

PostgreSQL Antipatternləri: CTE x CTE

İşimlə əlaqədar olaraq, bir tərtibatçının sorğu yazıb düşündüyü vəziyyətlərlə məşğul olmaq məcburiyyətindəyəm.Baza ağıllıdır, hər şeyi özü idarə edə bilər!«

Bəzi hallarda (qismən verilənlər bazasının imkanlarını bilməməkdən, qismən vaxtından əvvəl optimallaşdırmadan) bu yanaşma "Frankenstein" lərin yaranmasına səbəb olur.

Əvvəlcə belə bir sorğuya bir misal verəcəm:

-- для каждой ключевой пары находим ассоциированные значения полей
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;

Sorğunun keyfiyyətini əsaslı şəkildə qiymətləndirmək üçün bəzi ixtiyari məlumat dəstini yaradaq:

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

Belə çıxır ki məlumatların oxunması vaxtın dörddə birindən az vaxt apardı sorğunun icrası:

PostgreSQL Antipatternləri: CTE x CTE[express.tensor.ru saytına baxın]

Parça-parça ayıraraq

Gəlin sorğuya daha yaxından nəzər salaq və çaşqın olaq:

  1. Əgər rekursiv CTE-lər yoxdursa, niyə WITH RECURSIVE buradadır?
  2. Nə üçün min/maksimum dəyərləri ayrı bir CTE-də qruplaşdırın, əgər onlar hər halda orijinal nümunə ilə bağlıdırlar?
    +25% vaxt
  3. Niyə əvvəlki CTE-ni təkrarlamaq üçün sonunda qeyd-şərtsiz 'SEÇ * FROM' istifadə etməlisiniz?
    +14% vaxt

Bu halda, çox şanslı idik ki, əlaqə üçün Nested Loop deyil, Hash Join seçildi, çünki o zaman biz yalnız bir CTE Scan keçidini deyil, 10K-nı alacaqdıq!

CTE Scan haqqında bir azBurada biz bunu xatırlamalıyıq CTE Scan Seq Scan-a bənzəyir - yəni indeksləşdirmə yoxdur, ancaq tam axtarış tələb olunur 10K x 0.3ms = 3000ms cte_max tərəfindən dövrlər üçün və ya 1K x 1.5ms = 1500ms cte_bind tərəfindən dönərkən!
Əslində, nəticədə nə əldə etmək istəyirdiniz? Bəli, adətən bu, “üç mərtəbəli” sorğuların təhlilinin 5-ci dəqiqəsində ortaya çıxan sualdır.

Hər bir unikal açar cütü üçün çıxış etmək istədik key_a ilə qrupdan min/maks.
Beləliklə, bunun üçün istifadə edək pəncərə funksiyaları:

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

PostgreSQL Antipatternləri: CTE x CTE
[express.tensor.ru saytına baxın]

Hər iki variantda məlumatların oxunması eyni təxminən 4-5ms çəkdiyi üçün bütün vaxtımız qazanır -32% - bu ən təmiz formadadır yük əsas CPU-dan silindi, əgər belə bir sorğu kifayət qədər tez-tez yerinə yetirilirsə.

Ümumiyyətlə, bazanı "dairəvi aparın, kvadratı yuvarlayın" üçün məcbur etməməlisiniz.

Mənbə: www.habr.com

Добавить комментарий