PostgreSQL Antipatterns: štetni JOIN i OR

Bojte se operacija koje donose buffers...
Na primjeru jednostavnog upita razmotrit ćemo neke univerzalne pristupe optimizaciji upita u PostgreSQL-u. Da li ih koristiti ili ne – vi birate, ali ih je dobro poznavati.

U nekim budućim verzijama PG situacija se može promijeniti s "pametnijim" planerom, ali za 9.4/9.6 izgleda otprilike isto kao ovdje navedeni primjeri.

Uzet ću sasvim realan upit:

SELECT
  TRUE
FROM
  "Dokument" d
INNER JOIN
  "DokumentEkstenzija" doc_ex
    USING("@Dokument")
INNER JOIN
  "TipDokumenta" t_doc ON
    t_doc."@TipDokumenta" = d."TipDokumenta"
WHERE
  (d."Lice3" = 19091 or d."Zaposlenik" = 19091) AND
  d."$Članak" IS NULL AND
  d."Obrisan" IS NOT TRUE AND
  doc_ex."Stanje"[1] IS TRUE AND
  t_doc."TipDokumenta" = 'PlanRadova'
LIMIT 1;

o imenima tabela i poljaNa "ruska" imena polja i tabela može se gledati na različite načine, ali to je stvar ukusa. Pošto u "Tenzoru" nema stranih programera, a PostgreSQL nam omogućava da imenujemo stvari čak i hijeroglifima, ukoliko su u navodnicima, onda preferiramo da imenjujemo objekte jasno i razumljivo, kako ne bi došlo do nesporazuma.
Pogledajmo na dobijeni plan:
PostgreSQL Antipatterns: štetni JOIN i OR
[pogledati na explain.tensor.ru]

144ms i gotovo 53K buffers — to jest više od 400MB podataka! I imaćemo sreće ako se svi oni nađu u kešu u trenutku našeg upita, inače će se znatno produžiti vreme prilikom čitanja sa diska.

Algoritam je najvažniji!

Da bismo na neki način optimizirali bilo koji upit, prvo treba da shvatimo šta zapravo treba da radi.
Ostavit ćemo za sada razvoj same strukture Baze podataka van okvira ovog članka, i dogovoriti se da možemo relativno "jeftino" prepisati upit i/ili dodati na bazu neke potrebne indekse.

Dakle, upit:
— proverava postojanje barem jednog dokumenta
— u potrebnom stanju i određenog tipa
— gde je autor ili izvršilac potreban zaposleni

JOIN + LIMIT 1

Dosta često programeru je lakše napisati upit koji prvo radi spajanje velikog broja tabela, a zatim iz te gomile ostavlja samo jedan zapis. No, lakše za programera — ne znači efikasnije za Bazu podataka.
U našem slučaju bilo je samo 3 tabele — a kakav učinak...

Hajde da se najpre oslobodimo spajanja sa tabelom "TipDokumenta", a uz to i kažemo bazi da je zapis tipa jedinstven (mi to znamo, ali planer to još ne shvata):

WITH T AS (
  SELECT
    "@TipDokumenta"
  FROM
    "TipDokumenta"
  WHERE
    "TipDokumenta" = 'PlanRadova'
  LIMIT 1
)
...
WHERE
  d."TipDokumenta" = (TABLE T)
...

Da, ako tabela/CTE sadrži samo jedno polje jedine njegove srži, u PG se može pisati čak i ovako, umjesto

d."TipDokumenta" = (SELECT "@TipDokumenta" FROM T LIMIT 1)

„Lenjiv” izračun u upitima PostgreSQL-a

BitmapOr vs UNION

U nekim slučajevima Bitmap Heap Scan će nas koštati jako puno — na primjer, u našoj situaciji, kada dovoljno mnogo zapisa ispunjava tražene uvjete. Dobijamo ga zbog OR uslova koji je postao BitmapOr-operacija u planu.
Vratimo se početnom zadatku — potrebno je pronaći zapis koji odgovara bilo kojem od uslova — pa nije potrebno tražiti svih 59K zapisa po oba uslova. Postoji način da se obradi jedan uslov, a na drugi preći samo kada po prvom ništa ne bude pronađeno.Pomoći će nam ova konstrukcija:

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

«Spoljašnji» LIMIT 1 garantuje da će pretraga završiti nakon pronalaženja prvog zapisa. I ako se on pronađe već u prvom bloku, izvršavanje drugog neće se dogoditi (nikada se ne izvršava u planu).

«Skrivamo pod CASE» složene uslove

U početnom upitu postoji izuzetno nezgodna stavka — provera stanja povezanog po tabeli «DokumentEkstenzija». Ne zavisno od istinitosti ostalih uslova u izrazu (na primjer, d.«Obrisan» NIJE TAČNO), to spajanje se uvek izvršava i „troši resurse“. Koliko ili manje njih će biti potrošeno — zavisi od obima ove tabele.
Ali možemo modifikovati upit tako da pretraga povezanog zapisa bude moguća samo kada je to zaista potrebno:

SELECT
  ...
FROM
  "Dokument" d
WHERE
  ... 
/*index cond*/ AND
  CASE
    WHEN "$Nacrt" IS NULL AND "Obrisan" NIJE TAČNO THEN (
      SELECT
        "Stanje"[1] JE TAČNO
      FROM
        "DokumentEkstenzija"
      WHERE
        "@Dokument" = d."@Dokument"
    )
  END

Pošto nam iz povezane tabele nije potrebno nijedno polje, imamo mogućnost da pretvorimo JOIN u uslov po podupitu.
Ostavljamo indeksirana polja „van zagrade“ CASE, jednostavne uslove unosimo u WHEN blok — i sada se „težak“ upit izvršava samo kada pređemo u THEN.

Moje prezime „Istočnik“

Sakupljamo rezultantni upit sa svim gore opisanim mehanizmima:

SA T KAO (
  SELECT
    "@TipDokumenta"
  FROM
    "TipDokumenta"
  WHERE
    "TipDokumenta" = 'PlanRadova'
)
  (
    SELECT
      TRUE
    FROM
      "Dokument" d
    WHERE
      ("Lice3", "TipDokumenta") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Crnovik" IS NULL AND "Obrisan" IS NOT TRUE THEN (
          SELECT
            "Stanje"[1] IS TRUE
          FROM
            "DokumentProširenje"
          WHERE
            "@Dokument" = d."@Dokument"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Dokument" d
    WHERE
      ("TipDokumenta", "Zaposleni") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Crnovik" IS NULL AND "Obrisan" IS NOT TRUE THEN (
          SELECT
            "Stanje"[1] IS TRUE
          FROM
            "DokumentProširenje"
          WHERE
            "@Dokument" = d."@Dokument"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Prilagođavamo [za] indekse

Oko je primetilo da se uslovi koji se indeksiraju u spojenim delovima UNION malo razlikuju — to je zato što već imamo odgovarajuće indekse u tabeli. A da ih nije bilo — trebalo bi ih napraviti: Dokument(Lice3, TipDokumenta) i Dokument(TipDokumenta, Zaposleni).
o redosledu polja u ROW-uslovimaSa stanovišta planera, naravno, može se napisati i (A, B) = (constA, constB), i (B, A) = (constB, constA). Ali prilikom pisanja u redosledu polja u indeksu, takav zahtev je jednostavniji za kasnije debagovanje.
Šta je planirano?
PostgreSQL Antipatterns: štetni JOIN i OR
[pogledati na explain.tensor.ru]

Nažalost, nismo imali sreće, i u prvom UNION-bloku ništa se nije pronašlo, pa je drugi ipak nastavio s izvršenjem. Ali čak i tada — ukupno 0.037ms i 11 bafera!
Poboljšali smo zahtev i smanjili 'pritiskanje' podataka u memoriji za nekoliko hiljada puta, koristeći sasvim jednostavne tehnike — dobar rezultat uz malo kopiranja i lepljenja. 🙂

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster