🥇PostgreSQL Antipatternləri: zərərli QOŞULMALAR və OR-lar

Buferlər gətirən əməliyyatlardan çəkinin...
Nümunə kimi kiçik bir sorğudan istifadə edərək, PostgreSQL-də sorğuları optimallaşdırmaq üçün bəzi universal yanaşmalara baxaq. Onlardan istifadə edib etməməyiniz sizə bağlıdır, lakin onlar haqqında bilməyə dəyər.

PG-nin bəzi sonrakı versiyalarında planlayıcı daha ağıllı olduqda vəziyyət dəyişə bilər, lakin 9.4/9.6 üçün buradakı nümunələrdə olduğu kimi təxminən eyni görünür.

Çox real bir xahişi götürək:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

cədvəl və sahə adları haqqındaSahələrin və cədvəllərin "rusca" adlarına fərqli yanaşmaq olar, lakin bu zövq məsələsidir. Çünki burada Tensorda heç bir xarici tərtibatçı yoxdur və PostgreSQL bizə adları hətta heroqliflərlə verməyə imkan verir, əgər onlar dırnaqlar içərisindədir, onda biz heç bir uyğunsuzluq olmaması üçün obyektləri birmənalı və aydın adlandırmağa üstünlük veririk.
Nəticə planına baxaq:

[express.tensor.ru saytına baxın]

144ms və demək olar ki, 53K bufer - yəni 400MB-dan çox məlumat! İstədiyimiz vaxta qədər hamısı önbellekdə olsa, şanslı olacağıq, əks halda diskdən oxumaq dəfələrlə uzun çəkəcəkdir.

Alqoritm ən vacibdir!

İstənilən sorğunu bir şəkildə optimallaşdırmaq üçün əvvəlcə onun nə etməli olduğunu başa düşməlisiniz.
Verilənlər bazası strukturunun inkişafını hələlik bu məqalənin əhatə dairəsindən kənarda qoyaq və nisbətən “ucuz” edə biləcəyimizlə razılaşaq. sorğunu yenidən yazın və/yaxud bizə lazım olan bəzi şeyləri bazaya yuvarlayın İndekslər.

Beləliklə, sorğu:
— ən azı bir neçə sənədin mövcudluğunu yoxlayır
- ehtiyacımız olan vəziyyətdə və müəyyən bir növdə
- müəllif və ya ifaçı bizə lazım olan işçidir

QOŞULUN + LİMİT 1

Çox vaxt bir tərtibatçının əvvəlcə çox sayda cədvəlin birləşdirildiyi və sonra bütün dəstdən yalnız bir qeydin qaldığı bir sorğu yazması daha asandır. Lakin tərtibatçı üçün daha asan, verilənlər bazası üçün daha səmərəli demək deyil.
Bizim vəziyyətimizdə cəmi 3 masa var idi - və təsiri nədir ...

Gəlin əvvəlcə “Sənəd növü” cədvəli ilə əlaqəni aradan qaldıraq və eyni zamanda verilənlər bazasına deyək ki, tip rekordumuz unikaldır (biz bunu bilirik, lakin planlaşdırıcının hələ heç bir fikri yoxdur):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Bəli, əgər cədvəl/CTE bir qeydin tək sahəsindən ibarətdirsə, PG-də hətta bunun əvəzinə belə yaza bilərsiniz.

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

PostgreSQL sorğularında tənbəl qiymətləndirmə

BitmapOr vs UNION

Bəzi hallarda Bitmap Heap Scan bizə çox baha başa gələcək - məsələn, bizim vəziyyətimizdə, kifayət qədər çoxlu qeydlər tələb olunan şərtə cavab verəndə. aldıq, çünki OR vəziyyəti BitmapOr-a çevrildi- plan üzrə əməliyyat.
Orijinal problemə qayıdaq - uyğun bir rekord tapmalıyıq hər hansı birinə şərtlərdən - yəni hər iki şərtdə bütün 59K qeydləri axtarmağa ehtiyac yoxdur. Bir şərti həll etməyin bir yolu var və yalnız birincidə heç nə tapılmadıqda ikinciyə keçin. Aşağıdakı dizayn bizə kömək edəcək:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

“Xarici” LIMIT 1 axtarışın ilk qeyd tapıldığı zaman başa çatmasını təmin edir. Əgər birinci blokda artıq tapılıbsa, ikinci blok icra olunmayacaq (heç vaxt edam edilmir nəzərən).

“CASE altında çətin şərtləri gizlədir”

Orijinal sorğuda son dərəcə əlverişsiz bir məqam var - "DocumentExtension" ilə əlaqəli cədvəllə vəziyyəti yoxlamaq. İfadədəki digər şərtlərin doğruluğundan asılı olmayaraq (məsələn, d.“Silindi” DOĞRU DEYİL), bu əlaqə həmişə yerinə yetirilir və "resurslara xərclənir". Onların az və ya çoxu xərclənəcək - bu cədvəlin ölçüsündən asılıdır.
Lakin siz sorğunu elə dəyişdirə bilərsiniz ki, əlaqəli qeydin axtarışı yalnız həqiqətən zəruri olduqda baş versin:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Bir dəfə əlaqəli cədvəldən bizə nəticə üçün sahələrin heç birinə ehtiyac yoxdur, onda bizim JOIN-ı alt sorğuda şərtə çevirmək imkanımız var.
İndekslənmiş sahələri "CASE mötərizələrindən kənarda" buraxaq, qeyddən WHEN blokuna sadə şərtlər əlavə edək - indi "ağır" sorğu yalnız THEN-ə keçərkən yerinə yetirilir.

Mənim soyadım "Total"

Nəticə sorğusunu yuxarıda təsvir edilən bütün mexanizmlərlə toplayırıq:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

İndekslərin tənzimlənməsi

Təcrübəli bir göz UNION alt bloklarında indeksləşdirilmiş şərtlərin bir qədər fərqli olduğunu fərq etdi - bunun səbəbi cədvəldə artıq uyğun indekslərin olmasıdır. Əgər onlar mövcud olmasaydı, yaratmağa dəyərdi: Sənəd (Şəxs3, Sənədin Növü) и Sənəd (Sənəd Növü, İşçi).
ROW şəraitində sahələrin sırası haqqındaPlanlayıcının nöqteyi-nəzərindən, əlbəttə ki, yaza bilərsiniz (A, B) = (constA, constB)Və (B, A) = (constB, constA). Ancaq qeyd edərkən indeksdəki sahələrin sırası ilə, belə bir sorğu daha sonra sazlamaq üçün daha rahatdır.
Planda nə var?

[express.tensor.ru saytına baxın]

Təəssüf ki, bizim bəxtimiz gətirmədi və birinci UNION blokunda heç nə tapılmadı, buna görə də ikincisi hələ də icra olundu. Ancaq buna baxmayaraq - yalnız 0.037ms və 11 bufer!
Sorğunu sürətləndirdik və məlumatların yaddaşa nəqlini azaltdıq bir neçə min dəfə, kifayət qədər sadə üsullardan istifadə etməklə - bir az kopyala-yapışdırmaqla yaxşı nəticə. 🙂

Mənbə: www.habr.com

PostgreSQL Antipatternləri: zərərli JOIN və OR