🥇PostgreSQL Antimodelleri: zararlı JOIN'ler ve OR'ler

Tampon getiren işlemlere dikkat edin...
Örnek olarak küçük bir sorgu kullanarak PostgreSQL'de sorguları optimize etmeye yönelik bazı evrensel yaklaşımlara bakalım. Bunları kullanıp kullanmamak size kalmış ama bunları bilmekte fayda var.

PG'nin sonraki bazı sürümlerinde, zamanlayıcı daha akıllı hale geldikçe durum değişebilir, ancak 9.4/9.6 için durum, buradaki örneklerde olduğu gibi, yaklaşık olarak aynı görünmektedir.

Çok gerçek bir isteği ele alalım:

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;

tablo ve alan adları hakkındaAlanların ve tabloların “Rusça” isimleri farklı şekilde ele alınabilir, ancak bu bir zevk meselesidir. Çünkü burada Tensor'da yabancı geliştirici yok ve PostgreSQL, hiyerogliflerde bile isimler vermemize izin veriyor tırnak içine alınmış, o zaman hiçbir tutarsızlık olmaması için nesneleri açık ve net bir şekilde adlandırmayı tercih ederiz.
Ortaya çıkan plana bakalım:

[açıklama.tensor.ru'ya bakın]

144ms ve neredeyse 53K arabellek - yani 400 MB'tan fazla veri! Ve isteğimiz sırasında hepsi önbellekte olursa şanslı olacağız, aksi takdirde diskten okunma işlemi çok daha uzun sürecektir.

Algoritma çok önemlidir!

Herhangi bir isteği bir şekilde optimize etmek için öncelikle ne yapması gerektiğini anlamalısınız.
Veritabanı yapısının geliştirilmesini şimdilik bu makalenin kapsamı dışında bırakalım ve nispeten "ucuz" yapabileceğimiz konusunda hemfikir olalım. isteği yeniden yaz ve/veya ihtiyacımız olan bazı şeyleri üsse yuvarlayın endeksler.

Yani istek:
— en azından bazı belgelerin varlığını kontrol eder
- ihtiyacımız olan durumda ve belirli bir türde
- yazarın veya icracının ihtiyacımız olan çalışan olduğu yer

KATIL + SINIR 1

Çoğu zaman, bir geliştiricinin çok sayıda tablonun ilk kez birleştirildiği ve ardından tüm bu kümeden yalnızca bir kaydın kaldığı bir sorgu yazması daha kolaydır. Ancak geliştirici için daha kolay olması, veritabanı için daha verimli olduğu anlamına gelmez.
Bizim durumumuzda sadece 3 masa vardı - ve bunun etkisi ne oldu?

Öncelikle "Belge Türü" tablosuyla olan bağlantıdan kurtulalım ve aynı zamanda veritabanına şunu söyleyelim: tür kaydımız benzersizdir (bunu biliyoruz, ancak planlayıcının henüz bir fikri yok):

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

Evet, tablo/CTE tek bir kaydın tek bir alanından oluşuyorsa, PG'de bunun yerine bu şekilde bile yazabilirsiniz.

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

PostgreSQL sorgularında tembel değerlendirme

BitmapOr vs UNION

Bazı durumlarda, Bitmap Yığın Taraması bize çok pahalıya mal olabilir; örneğin, çok sayıda kaydın gerekli koşulu karşıladığı bizim durumumuzda. Anladık çünkü OR koşulu BitmapOr'a dönüştü- planda operasyon.
Asıl soruna dönelim; buna karşılık gelen bir kayıt bulmamız gerekiyor. herhangi birine koşullardan - yani her iki koşulda da 59K kayıtların tamamını aramaya gerek yoktur. Bir koşulu çözmenin bir yolu var ve ancak ilkinde hiçbir şey bulunamadığında ikinciye gidin. Aşağıdaki tasarım bize yardımcı olacaktır:

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

“Harici” LIMIT 1, ilk kayıt bulunduğunda aramanın sona ermesini sağlar. Ve eğer ilk blokta zaten bulunuyorsa, ikinci blok çalıştırılmayacaktır (asla idam edilmedi ilgili olarak).

“CASE kapsamında zor koşullar gizleniyor”

Orijinal sorguda son derece rahatsız edici bir an var - ilgili "DocumentExtension" tablosuna göre durumun kontrol edilmesi. İfadedeki diğer koşulların doğruluğundan bağımsız olarak (örneğin, d.“Silindi” DOĞRU DEĞİLDİR), bu bağlantı her zaman yürütülür ve "kaynaklara mal olur". Az ya da çok harcanacak - bu tablonun boyutuna bağlıdır.
Ancak ilgili kaydın aranmasının yalnızca gerçekten gerekli olduğunda gerçekleşmesi için sorguyu değiştirebilirsiniz:

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

Bağlantılı tablodan bize bir kez sonuç için hiçbir alana gerek yok, o zaman JOIN'i bir alt sorguda koşula dönüştürme fırsatımız olur.
İndekslenmiş alanları "CASE parantezlerinin dışında" bırakalım, kayıttan WHEN bloğuna basit koşullar ekleyelim - ve artık "ağır" sorgu yalnızca THEN'e geçerken yürütülür.

Soyadım "Toplam"

Ortaya çıkan sorguyu yukarıda açıklanan tüm mekanizmalarla topluyoruz:

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;

Dizinleri ayarlama

Eğitimli bir göz, UNION alt bloklarındaki indekslenmiş koşulların biraz farklı olduğunu fark etti; bunun nedeni, masada zaten uygun indekslerin bulunmasıdır. Ve eğer onlar olmasaydı, yaratmaya değer olurdu: Belge(Kişi3, Belge Türü) и Belge(Belge Türü, Çalışan).
ROW koşullarındaki alanların sırası hakkındaPlanlayıcının bakış açısından elbette yazabilirsiniz (A, B) = (sabitA, sabitB)ve (B, A) = (sabitB, sabitA). Ama kayıt yaparken indeksteki alanların sırasına göre, böyle bir isteğin daha sonra hata ayıklaması daha kolaydır.
Planda ne var?

[açıklama.tensor.ru'ya bakın]

Ne yazık ki şanssızdık ve ilk UNION bloğunda hiçbir şey bulunamadı, bu yüzden ikincisi yine de yürütüldü. Ama yine de - yalnızca 0.037 ms ve 11 arabellek!
İsteği hızlandırdık ve belleğe veri pompalamayı azalttık birkaç bin kez, oldukça basit teknikler kullanarak - biraz kopyala-yapıştır ile iyi bir sonuç. 🙂

Kaynak: habr.com

PostgreSQL Antipatterns: Zararlı JOIN'ler ve OR'ler