PostgreSQL antiobrasci: štetni JOIN-ovi i OR-ovi

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

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

Uzmimo vrlo realan 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 nazivima tablica i polja„Ruski“ nazivi polja i tabela mogu se različito tretirati, ali to je stvar ukusa. Zbog ovdje u Tensoru nema stranih programera, a PostgreSQL nam dozvoljava da dajemo imena čak i hijeroglifima, ako u navodnicima, onda radije imenujemo objekte nedvosmisleno i jasno kako ne bi došlo do neslaganja.
Pogledajmo rezultirajući plan:

[pogledajte objasni.tensor.ru]

144ms i skoro 53K bafera - odnosno više od 400MB podataka! I imaćemo sreće ako svi budu u kešu do trenutka našeg zahteva, inače će biti potrebno višestruko duže kada se čita sa diska.

Algoritam je najvažniji!

Da biste na neki način optimizirali bilo koji zahtjev, prvo morate razumjeti šta on treba da radi.
Ostavimo razvoj same strukture baze podataka za sada van okvira ovog članka i složimo se da možemo relativno „jeftino“ prepišite zahtjev i/ili otkotrljamo na podlogu neke od stvari koje su nam potrebne indeksi.

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

PRIDRUŽITE SE + LIMIT 1

Često je programeru lakše napisati upit u kojem se prvo spoji veliki broj tabela, a onda od cijelog ovog skupa ostane samo jedan zapis. Ali lakše za programera ne znači i efikasnije za bazu podataka.
U našem slučaju su bile samo 3 stola - i kakav je efekat...

Prvo se riješimo veze s tablicom "Vrsta dokumenta" i istovremeno kažimo bazi podataka da naš tipski zapis je jedinstven (ovo znamo, ali planer još nema pojma):

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

Da, ako se tabela/CTE sastoji od jednog polja jednog zapisa, onda u PG možete čak pisati 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 koštati mnogo – na primjer, u našoj situaciji, kada dosta zapisa ispunjava traženi uvjet. Dobili smo jer OR uvjet pretvoren u BitmapOr- rad u planu.
Vratimo se originalnom problemu - trebamo pronaći odgovarajući zapis bilo kome iz uslova - to jest, nema potrebe tražiti svih 59K zapisa pod oba uslova. Postoji način da se razradi jedan uslov, i idite na drugi samo kada ništa nije pronađeno u prvom. Sljedeći dizajn će nam pomoći:

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

“Spoljni” LIMIT 1 osigurava da se pretraga završi kada se pronađe prvi zapis. A ako je već pronađen u prvom bloku, drugi blok se neće izvršiti (nikad pogubljen u odnosu na).

“Skrivanje teških uslova pod CASE-om”

Postoji izuzetno nezgodan trenutak u originalnom upitu - provjera statusa prema povezanoj tablici “DocumentExtension”. Bez obzira na istinitost drugih uslova u izrazu (npr. d. “Izbrisano” NIJE TAČNO), ova veza se uvijek izvršava i „košta resurse“. Više ili manje njih će se potrošiti - ovisi o veličini ove tablice.
Ali možete modificirati upit tako da se pretraga povezanog zapisa dogodi samo kada je to zaista neophodno:

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 iz povezane tabele za nas nijedno polje nije potrebno za rezultat, tada imamo priliku pretvoriti JOIN u uslov na podupitu.
Ostavimo indeksirana polja "izvan zagrada CASE", dodajmo jednostavne uslove iz zapisa u blok WHEN - i sada se "teški" upit izvršava samo kada se prelazi na THEN.

Moje prezime je "Ukupno"

Prikupljamo rezultirajući 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] indeksa

Uvježbano oko je primijetilo da su indeksirani uvjeti u UNION podblokovima malo drugačiji - to je zato što već imamo odgovarajuće indekse na tabeli. A da ne postoje, vrijedilo bi stvoriti: Dokument (osoba 3, vrsta dokumenta) и Dokument (Vrsta dokumenta, zaposlenik).
o redoslijedu polja u ROW uslovimaSa stanovišta planera, naravno, možete pisati (A, B) = (constA, constB)i (B, A) = (konstB, konstA). Ali prilikom snimanja redoslijedom polja u indeksu, takav zahtjev je jednostavno praktičniji za kasnije otklanjanje grešaka.
Šta je u planu?

[pogledajte objasni.tensor.ru]

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

izvor: www.habr.com