ProHoster > Blog > uprava > PostgreSQL antiuzorci: Štetni JOIN-ovi i OR-ovi

PostgreSQL antiuzorci: Štetni JOIN-ovi i OR-ovi

Čuvajte se operacija koje donose međuspremnike...
Koristeći mali upit kao primjer, pogledajmo neke univerzalne pristupe optimizaciji upita u PostgreSQL-u. Hoćete li ih koristiti ili ne, ovisi o vama, ali vrijedi znati o njima.

U nekim sljedećim verzijama PG-a situacija se može promijeniti kako planer postaje pametniji, ali za 9.4/9.6 izgleda približno isto, kao u primjerima ovdje.

Uzmimo vrlo stvaran zahtjev:

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 imenima tablica i polja"Ruska" imena polja i tablica mogu se tretirati drugačije, ali to je stvar ukusa. Jer ovdje u Tensoru nema stranih programera, a PostgreSQL nam dopušta davanje imena čak i hijeroglifima, ako ih ima u navodnicima, tada radije imenujemo objekte nedvosmisleno i jasno kako ne bi bilo odstupanja.
Pogledajmo nastali plan:
PostgreSQL antiuzorci: Štetni JOIN-ovi i OR-ovi
[pogledajte na expand.tensor.ru]

144ms i gotovo 53K međuspremnika - odnosno više od 400 MB podataka! I bit ćemo sretni ako svi budu u cacheu u trenutku našeg zahtjeva, inače će čitanje s diska trajati mnogo više.

Algoritam je najvažniji!

Da biste nekako optimizirali bilo koji zahtjev, prvo morate razumjeti što bi trebao učiniti.
Ostavimo za sada razvoj same strukture baze podataka izvan okvira ovog članka i složimo se da možemo relativno "jeftino" prepisati zahtjev i/ili otkotrljati na podlogu neke stvari koje su nam potrebne Indeksi.

Dakle, zahtjev:
— provjerava postojanje barem nekog dokumenta
- u stanju kakvom nam treba i određenog tipa
- gdje je autor ili izvođač djelatnik koji nam je potreban

PRIDRUŽITE SE + OGRANIČITE 1

Programeru je često lakše napisati upit u kojem se prvo spoji veliki broj tablica, a zatim od cijelog skupa ostane samo jedan zapis. No lakše za programera ne znači i učinkovitije za bazu podataka.
U našem slučaju bila su samo 3 stola - a kakav je učinak...

Prvo se riješimo veze s tablicom "Vrsta dokumenta", a u isto vrijeme recimo bazi podataka da naš tipski zapis je jedinstven (mi to znamo, ali planer još nema pojma):

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

Da, ako se tablica/CTE sastoji od jednog polja jednog zapisa, tada u PG možete pisati čak i ovako, umjesto

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

Lijena evaluacija u PostgreSQL upitima

BitmapOr vs UNION

U nekim slučajevima Bitmap Heap Scan će nas skupo koštati - na primjer, u našoj situaciji, kada dosta zapisa ispunjava traženi uvjet. Dobili smo jer Uvjet ILI pretvoren je u BitmapOr- operacija u planu.
Vratimo se izvornom problemu - moramo pronaći odgovarajući zapis bilo tko iz uvjeta - to jest, nema potrebe tražiti svih 59K zapisa pod oba uvjeta. Postoji način da se riješi jedan uvjet, i idite na drugi tek kada u prvom ništa nije pronađeno. Sljedeći dizajn će nam pomoći:

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

“Vanjski” LIMIT 1 osigurava da pretraživanje završava kada se pronađe prvi zapis. A ako je već pronađen u prvom bloku, drugi blok se neće izvršiti (nikad izvršena u pogledu).

“Skrivanje teških uvjeta pod CASE”

Postoji izuzetno nezgodan trenutak u izvornom upitu - provjera statusa prema povezanoj tablici "DocumentExtension". Bez obzira na istinitost drugih uvjeta u izrazu (npr. d. "Izbrisano" NIJE TOČNO), ova se veza uvijek izvodi i “košta resurse”. Više ili manje će ih se potrošiti - ovisi o veličini ovog stola.
Ali možete modificirati upit tako da se traženje povezanog zapisa odvija samo kada je to stvarno 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

Jednom od povezane tablice do nas niti jedno polje nije potrebno za rezultat, onda imamo priliku pretvoriti JOIN u uvjet na podupitu.
Ostavimo indeksirana polja "izvan zagrada CASE", dodajmo jednostavne uvjete iz zapisa u blok WHEN - i sada se "teški" upit izvršava samo pri prijelazu na THEN.

Moje prezime je "Total"

Prikupljamo dobiveni upit sa svim gore opisanim mehanikama:

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;

Podešavanje [na] indekse

Istrenirano oko primijetilo je da su indeksirani uvjeti u podblokovima UNION malo drugačiji - to je zato što već imamo odgovarajuće indekse na stolu. I da ne postoje, vrijedilo bi ih stvoriti: Dokument(Osoba3, Vrsta dokumenta) и Dokument (vrsta dokumenta, zaposlenik).
o redoslijedu polja u ROW uvjetimaSa stanovišta planera, naravno, možete pisati (A, B) = (constA, constB)I (B, A) = (constB, constA). Ali pri snimanju prema redoslijedu polja u indeksu, takav je zahtjev jednostavno lakše ispraviti kasnije.
Što je u planu?
PostgreSQL antiuzorci: Štetni JOIN-ovi i OR-ovi
[pogledajte na expand.tensor.ru]

Nažalost, nismo imali sreće i u prvom UNION bloku ništa nije pronađeno, pa je drugi ipak izvršen. Ali čak i tako – samo 0.037 ms i 11 međuspremnika!
Ubrzali smo zahtjev i smanjili pumpanje podataka u memoriju nekoliko tisuća puta, koristeći prilično jednostavne tehnike - dobar rezultat s malo copy-paste. 🙂

Izvor: www.habr.com

Dodajte komentar