🥇Protivzorci PostgreSQL: škodljivi JOIN in OR

Pazite se operacij, ki prinašajo medpomnilnike ...
Na primeru majhne poizvedbe si poglejmo nekaj univerzalnih pristopov k optimizaciji poizvedb v PostgreSQL. Ali jih boste uporabljali ali ne, je odvisno od vas, vendar je vredno vedeti o njih.

V nekaterih naslednjih različicah PG se lahko situacija spremeni, ko postane razporejevalnik pametnejši, vendar je za 9.4/9.6 videti približno enako kot v primerih tukaj.

Vzemimo zelo resnično zahtevo:

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;

o imenih tabel in polj"Ruska" imena polj in tabel je mogoče obravnavati drugače, vendar je to stvar okusa. Zaradi tukaj pri Tensorju tujih razvijalcev ni, PostgreSQL pa nam omogoča, da dajemo imena tudi v hieroglifih, če jih v narekovajih, potem predmete raje poimenujmo nedvoumno in jasno, da ne prihaja do neskladij.
Poglejmo nastali načrt:

[ogled na expand.tensor.ru]

144ms in skoraj 53K medpomnilnikov - torej več kot 400 MB podatkov! In imeli bomo srečo, če bodo vsi v predpomnilniku do naše zahteve, sicer bo branje z diska trajalo veliko dlje.

Algoritem je najpomembnejši!

Da bi nekako optimizirali katero koli zahtevo, morate najprej razumeti, kaj naj počne.
Pustimo zaenkrat razvoj same strukture baze podatkov izven obsega tega članka in se strinjamo, da lahko razmeroma "poceni" prepišite zahtevo in/ali na podlago zakotalimo nekaj stvari, ki jih potrebujemo Kazala.

Torej zahteva:
— preveri obstoj vsaj kakšnega dokumenta
- v stanju, kot ga potrebujemo, in določene vrste
- kjer je avtor ali izvajalec zaposleni, ki ga potrebujemo

PRIDRUŽITE SE + OMEJITEV 1

Pogosto je razvijalcu lažje napisati poizvedbo, kjer se najprej združi veliko število tabel, nato pa iz celotnega nabora ostane samo en zapis. Toda lažje za razvijalca ne pomeni tudi učinkovitejše za bazo podatkov.
V našem primeru so bile samo 3 mize - in kakšen je učinek ...

Najprej se znebimo povezave s tabelo "Vrsta dokumenta" in hkrati povejmo bazi podatkov, da naš tipski zapis je edinstven (to vemo, vendar razporejevalnik še nima pojma):

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

Da, če je tabela/CTE sestavljena iz enega samega polja enega zapisa, potem lahko v PG pišete celo takole, namesto

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

Leno vrednotenje v poizvedbah PostgreSQL

BitmapOr proti UNION

V nekaterih primerih nas bo Bitmap Heap Scan zelo drago stalo - na primer v naši situaciji, ko kar nekaj zapisov izpolnjuje zahtevani pogoj. Dobili smo ga, ker Pogoj ALI se je spremenil v BitmapOr- delovanje v načrtu.
Vrnimo se k prvotni težavi - najti moramo ustrezni zapis do katerega koli od pogojev - to pomeni, da ni treba iskati vseh 59K zapisov pod obema pogojema. Obstaja način za izpolnitev enega pogoja in pojdi na drugo šele, ko v prvi ni bilo najdeno nič. V pomoč nam bo naslednja zasnova:

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

“Zunanja” LIMIT 1 zagotavlja, da se iskanje konča, ko je najden prvi zapis. In če je že najden v prvem bloku, drugi blok ne bo izveden (nikoli izvršena iz spoštovanja do).

"Skrivanje težkih pogojev pod CASE"

V prvotni poizvedbi je izjemno neprijeten trenutek - preverjanje stanja glede na povezano tabelo »DocumentExtension«. Ne glede na resničnost drugih pogojev v izrazu (npr. d.»izbrisano«NI RES), se ta povezava vedno izvaja in »stane sredstev«. Več ali manj jih bo porabljenih - odvisno od velikosti te mize.
Poizvedbo pa lahko spremenite tako, da se iskanje povezanega zapisa izvede le, ko je res potrebno:

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

Enkrat iz povezane tabele k nam nobeno od polj ni potrebno za rezultat, potem imamo možnost JOIN spremeniti v pogoj za podpoizvedbo.
Pustimo indeksirana polja "zunaj oklepajev CASE", dodamo preproste pogoje iz zapisa v blok WHEN - in zdaj se "težka" poizvedba izvede le pri prehodu na THEN.

Moj priimek je "Total"

Nastalo poizvedbo zberemo z vsemi zgoraj opisanimi mehaniki:

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;

Prilagoditev [na] indekse

Izurjeno oko je opazilo, da so indeksirani pogoji v podblokih UNION nekoliko drugačni - to je zato, ker že imamo ustrezne indekse na mizi. In če ne bi obstajali, bi bilo vredno ustvariti: Dokument (Oseba3, Vrsta dokumenta) и Dokument (Vrsta dokumenta, Zaposleni).
o vrstnem redu polj v pogojih ROWZ načrtovalskega vidika seveda lahko pišete (A, B) = (constA, constB)In (B, A) = (constB, constA). Toda pri snemanju po vrstnem redu polj v indeksu, je takšna zahteva preprosto bolj priročna za odpravljanje napak pozneje.
Kaj je v načrtu?

[ogled na expand.tensor.ru]

Žal smo imeli smolo in v prvem bloku UNION nismo našli ničesar, zato je bil drugi vseeno izveden. A kljub temu – samo 0.037 ms in 11 medpomnilnikov!
Pospešili smo zahtevo in zmanjšali črpanje podatkov v pomnilnik nekaj tisočkrat, z uporabo dokaj preprostih tehnik – dober rezultat z malo copy-paste. 🙂

Vir: www.habr.com

Protivzorci PostgreSQL: škodljivi JOIN in OR