🥇PostgreSQL antipatterns: káros JOIN-ok és OR-k

Óvakodjon azoktól a műveletektől, amelyek puffereket hoznak...
Példaként egy kis lekérdezést használva nézzünk meg néhány univerzális megközelítést a PostgreSQL lekérdezések optimalizálására. Az, hogy használod-e vagy sem, rajtad múlik, de érdemes tudni róluk.

A PG egyes későbbi verzióiban a helyzet megváltozhat, ahogy az ütemező okosabb lesz, de a 9.4/9.6 esetében megközelítőleg ugyanúgy néz ki, mint az itt látható példákban.

Vegyünk egy nagyon valós kérést:

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;

a tábla- és mezőnevekrőlA mezők és táblák „orosz” elnevezései másként kezelhetők, de ez ízlés dolga. Mert a itt a Tensornál nincsenek külföldi fejlesztők, és a PostgreSQL lehetővé teszi, hogy még hieroglifákkal is adjunk neveket, ha idézőjelek közé zárva, akkor inkább egyértelműen és egyértelműen nevezzük el az objektumokat, hogy ne legyenek eltérések.
Nézzük az eredményül kapott tervet:

[megtekintés itt: magyarázat.tensor.ru]

144 ms és majdnem 53K puffer - vagyis több mint 400 MB adat! És szerencsénk lesz, ha kérésünk időpontjára mindegyik a gyorsítótárban van, különben sokszor tovább tart a lemezről történő olvasás.

Az algoritmus a legfontosabb!

Annak érdekében, hogy valamilyen kérést optimalizáljon, először meg kell értenie, mit kell tennie.
Magát az adatbázis-struktúra fejlesztését most hagyjuk a cikk keretein kívül, és értsük meg, hogy viszonylag „olcsón” írja át a kérést és/vagy rátekerjük az alapra néhány dolgot, amire szükségünk van Indexek.

Tehát a kérés:
— legalább néhány dokumentum meglétét ellenőrzi
- olyan állapotban, amilyenre szükségünk van, és egy bizonyos típusú
- ahol a szerző vagy előadó a szükséges alkalmazott

CSATLAKOZÁS + LIMIT 1

A fejlesztőnek gyakran könnyebb olyan lekérdezést írni, ahol először sok tábla van összekapcsolva, és csak egy rekord marad ebből az egész halmazból. De a könnyebb a fejlesztő számára nem jelenti azt, hogy hatékonyabb az adatbázis számára.
A mi esetünkben csak 3 asztal volt - és mi ennek a hatása...

Először szüntessük meg a „Dokumentumtípus” táblával való kapcsolatot, és egyúttal mondjuk el az adatbázisnak, hogy típusrekordunk egyedi (ezt tudjuk, de az ütemezőnek még fogalma sincs):

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

Igen, ha a tábla/CTE egyetlen rekord egyetlen mezőjéből áll, akkor PG-ben akár így is írhatsz,

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

Lusta kiértékelés a PostgreSQL lekérdezésekben

BitmapOr vs UNION

Bizonyos esetekben a Bitmap Heap Scan sokba fog kerülni - például a mi helyzetünkben, amikor elég sok rekord teljesíti a szükséges feltételt. Megkaptuk, mert VAGY feltétel BitmapOr-ra változott- működés tervben.
Térjünk vissza az eredeti problémához – meg kell találnunk a megfelelő rekordot bárki feltételektől – vagyis nem kell mindkét feltétel mellett megkeresni az összes 59K rekordot. Van mód egy feltétel kidolgozására, és csak akkor menjen a másodikra, ha az elsőben nem talált semmit. A következő tervezés segít nekünk:

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

A „Külső” LIMIT 1 biztosítja, hogy a keresés az első rekord megtalálásakor véget érjen. És ha már megtalálható az első blokkban, akkor a második blokk nem kerül végrehajtásra (soha nem végezték ki tiszteletére).

„Nehéz körülmények elrejtése a CASE alatt”

Az eredeti lekérdezésben van egy rendkívül kényelmetlen pillanat – az állapot ellenőrzése a kapcsolódó „DocumentExtension” táblával. Függetlenül attól, hogy a kifejezésben szereplő egyéb feltételek (pl. d. „Törölve” NEM IGAZ), ez a kapcsolat mindig végrehajtásra kerül, és „erőforrásokba kerül”. Többé-kevesebbet elköltenek - az asztal méretétől függ.
De módosíthatja a lekérdezést úgy, hogy a kapcsolódó rekord keresése csak akkor történjen meg, ha valóban szükséges:

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

Egyszer a linkelt táblázatból hozzánk az eredményhez egyik mezőt sem kell kitölteni, akkor lehetőségünk van a JOIN-t egy részlekérdezés feltételévé alakítani.
Hagyjuk az indexelt mezőket „a CASE zárójelen kívül”, adjunk hozzá egyszerű feltételeket a rekordból a WHEN blokkhoz - és most a „heavy” lekérdezés csak a THEN-nek való átadáskor kerül végrehajtásra.

A vezetéknevem "Összesen"

Az eredményül kapott lekérdezést a fent leírt összes mechanikával összegyűjtjük:

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;

Indexek beállítása [hozzá]

Egy gyakorlott szem észrevette, hogy az UNION alblokkokban az indexelt feltételek kissé eltérnek – ez azért van, mert a táblázatban már vannak megfelelő indexek. És ha nem lennének, érdemes lenne létrehozni: Dokumentum (3. személy, dokumentumtípus) и Dokumentum (dokumentumtípus, alkalmazott).
a mezők sorrendjéről ROW feltételek mellettA tervező szemszögéből persze lehet írni (A, B) = (constA, constB)És (B, A) = (constB, constA). De felvételkor az index mezőinek sorrendjében, egy ilyen kérés egyszerűen kényelmesebb a későbbi hibakereséshez.
Mi van a tervben?

[megtekintés itt: magyarázat.tensor.ru]

Sajnos nem volt szerencsénk és az első UNION blokkban nem találtak semmit, így a másodikat mégis kivégezték. De még így is – csak 0.037 ms és 11 puffer!
Felgyorsítottuk a kérést, és csökkentettük az adatszivattyúzást a memóriában több ezerszer, meglehetősen egyszerű technikákkal - jó eredmény egy kis copy-paste segítségével. 🙂

Forrás: will.com

PostgreSQL antipatterns: káros JOIN-ok és OR-k