🥇PostgreSQL Antipatterns: kahjulikud JOIN ja OR

Kartke tehingutest, mis toovad kaasa buffers...
Vaatame väikese päringu näitel mõned universaalsed lähenemisviisid PostgreSQL päringute optimeerimiseks. Kas neid kasutada või mitte — otsustate teie, aga tundke need ära.

Mõnes hilisemates PG versioonides võib olukord „tarkema” planeerijaga muutuda, kuid 9.4/9.6 puhul näeb see välja enam-vähem sama, nagu siin näidatud.

Võtan täiesti reaalse päringu:

SELECT
  TRUE
FROM
  "Dokument" d
INNER JOIN
  "DokumentRakendus" doc_ex
    USING("@Dokument")
INNER JOIN
  "DokumendiTüüp" t_doc ON
    t_doc."@DokumendiTüüp" = d."DokumendiTüüp"
WHERE
  (d."Isik3" = 19091 or d."Töötaja" = 19091) AND
  d."$Mustand" IS NULL AND
  d."Kustutatud" IS NOT TRUE AND
  doc_ex."Seisund"[1] IS TRUE AND
  t_doc."DokumendiTüüp" = 'Tööplaan'
LIMIT 1;

rääkides tabelite ja väljade nimedestVõib olla erinevaid arvamusi „vene“ nime kohta väljadele ja tabelitele, aga see on maits küsimus. Kuna meie "Tensoris" pole välismaalasi arendajaid, ja PostgreSQL võimaldab meil nimetada isegi hieroglüüfidena, kui need on tsitaatides, siis eelistame nimetada objekte üheselt arusaadavalt, et ei tekiks segadust.
Vaatame saadud plaani:
PostgreSQL Antipatterns: kahjulikud JOIN ja OR
[vaata explain.tensor.ru]

144ms ja peaaegu 53K buffers — see tähendab rohkem kui 400MB andmeid! Ja meil on õnne, kui kõik need on meie päringu hetkeks vahemällu salvestatud, vastasel juhul venib see palju kauem, kui andmed tulevad kettalt.

Algoritm on kõige tähtsam!

Kuidas iganes päringut optimeerida, tuleb esmalt mõista, mida see tegelikult tegema peab.
Jätame selle artikli raames andmebaasi struktuuri arendamise kõrvale ja lepime kokku, et saame suhteliselt 'odavalt' päringu ümber kirjutada ja/või rakendada andmebaasi mõningaid vajalikke indekseid.

Nii et päring:
— kontrollib, kas mõni dokument eksisteerib
— õiges olekus ja konkreetse tüübi järgi
— kus autor või täitja on meie vajalik töötaja

JOIN + LIMIT 1

Sageli on arendajal lihtsam kirjutada päring, kus esmalt tehakse suure hulga tabelite ühendamine, ja siis jääb sellest hulgast ainult üksainus kirje. Aga arendaja jaoks lihtsam ei tähenda, et see on andmebaasi jaoks efektiivsem.
Meie juhul oli tabeleid ainult 3 — aga milline efekt...

Alustame 'DokumendiTüüp' tabeliga ühenduse eemaldamisega ning ütleme andmebaasile, et meie korralduskirje tüüp on ainulaadne (me küll teame seda, kuid planeerija ei tunne veel ära):

WITH T AS (
  SELECT
    "@DokumendiTüüp"
  FROM
    "DokumendiTüüp"
  WHERE
    "DokumendiTüüp" = 'Tööplaan'
  LIMIT 1
)
...
WHERE
  d."DokumendiTüüp" = (TABLE T)
...

Jah, kui tabel/CTE koosneb ainsast väljast ainsast kirjest, siis PG-s võib kirjutada isegi nii, selle asemel

d."DokumendiTüüp" = (SELECT "@DokumendiTüüp" FROM T LIMIT 1)

PostgreSQL päringutes 'laisk' arvutamine

BitmapOr vs UNION

Mõnes olukorras võib Bitmap Heap Scan meile väga kalliks minna — näiteks meie olukorras, kus piisavalt palju kirjeid langeb nõutud tingimuse alla. Saime selle tõttu OR-tingimuse, mis muutus BitmapOr-operatsiooniks plaanis.
Naaseme algse ülesande juurde — peame leidma kirje, mis vastab ühele nendest tingimustest — st pole mõtet otsida kõiki 59K kämpingut mõlema tingimuse järgi. On viis, kuidas töötada välja üks tingimus ja teise juurde minna ainult siis, kui esimesest ei leitud midagi.Meie abiks on selline konstruktsioon:

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

«Väline» LIMIT 1 tagab, et otsing lõpeb, kui esimene kirje leitakse. Ja kui see leitatakse juba esimeses blokis, siis teise täitmine ei toimu (never executed plaanis).

„Peidame CASE alla” keerulised tingimused

Alguses lihtsas päringus on äärmiselt ebamugav aspekt — kontrollimine seotud tabeli „DokumentRoskelik” seisundi järgi. Ükskõik, kas muud tingimused väljendis on tõesed (näiteks, d.«Kustutatud» IS NOT TRUE), toimub see liitumine alati ja „kulutab ressursse”. Rohkem või vähem neid kulutatakse, sõltub selle tabeli mahust.
Kuid päringu saab muuta nii, et seotud kirje otsing toimub ainult siis, kui see on tõesti vajalik:

SELECT
  ...
FROM
  "Dokument" d
WHERE
  ... 
/*index cond*/ AND
  CASE
    WHEN "$Mustand" IS NULL AND "Kustutatud" IS NOT TRUE THEN (
      SELECT
        "Seisund"[1] IS TRUE
      FROM
        "DokumentRoskelik"
      WHERE
        "@Dokument" = d."@Dokument"
    )
  END

Kuna me ei vaja seotud tabelist ühtegi välja , siis on meil võimalus muuta JOIN tingimuseks alampäringus.Jätame indekseeritavad väljad „kõrgematesse” CASE-i, lihtsad tingimused toome sisse WHEN-blokki — ja nüüd „raske” päring täidetakse ainult juhul, kui liigume THEN-i.
Minu perekonnanimi on „Kokkuvõte”

Kogume tulemuseks oleva päringu koos kõigi eespool kirjeldatud mehhanismidega:

SOB5170

KUIDAS T ON KUIDAS (
  VALIGE
    "@DokumendiTüüp"
  FROM
    "DokumendiTüüp"
  WHERE
    "DokumendiTüüp" = 'Tööplaan'
)
  (
    VALIGE
      TÕSI
    FROM
      "Dokument" d
    WHERE
      ("Isik3", "DokumendiTüüp") = (19091, (TABEL T)) JA
      JUHUL
        KUI "$Mustandi" ON NULL JA "Kustutatud" EI OLE TÕSI SIIS (
          VALIGE
            "Seisund"[1] ON TÕSI
          FROM
            "DokumendiLaienemine"
          WHERE
            "@Dokument" = d."@Dokument"
        )
      OLE TINGIMUS
    LIMIT 1
  )
UNION ALL
  (
    VALIGE
      TÕSI
    FROM
      "Dokument" d
    WHERE
      ("DokumendiTüüp", "Töötaja") = ((TABEL T), 19091) JA
      JUHUL
        KUI "$Mustandi" ON NULL JA "Kustutatud" EI OLE TÕSI SIIS (
          VALIGE
            "Seisund"[1] ON TÕSI
          FROM
            "DokumendiLaienemine"
          WHERE
            "@Dokument" = d."@Dokument"
        )
      OLE TINGIMUS
    LIMIT 1
  )
LIMIT 1;

Kohandame [vastu] indekseid

Kogenud pilk märkis, et indekseeritud tingimused UNION allüksustes erinevad veidi — see on tingitud sellest, et meil on juba vastavad indeksid tabelis. Ja kui neid ei oleks — siis oleks tasunud need luua: Dokument(Isik3, DokumendiTüüp) ja Dokument(DokumendiTüüp, Töötaja).
väljade järjekorrast ROW-tingimustesPlaneerija vaatenurgast on muidugi võimalik kirjutada ka (A, B) = (constA, constB), ja (B, A) = (constB, constA). Kuid kirjutamisel indeksi väljade järjekorras, on selline päring lihtsalt mugavam hiljem tõrkeotsinguks.
Mida plaanis on?
PostgreSQL Antipatterns: kahjulikud JOIN ja OR
[vaata explain.tensor.ru]

Kahjuks ei olnud meil vedu ja esimeses UNION-blokis ei leitud midagi, seega teine ikkagi läks täitmisele. Kuid isegi sel juhul — kokku 0.037ms ja 11 puhvrit!
Me kiirendasime päringut ja vähendasime "andmete töötlemist" mälus mitme tuhande korra, kasutades piisavalt lihtsaid meetodeid — mitte halb tulemus väikese kopeerimise kohta. 🙂

Allikas: habr.com