Dajte si pozor na operácie, ktoré prinášajú nárazníky...
Pomocou malého dotazu ako príkladu sa pozrime na niektoré univerzálne prístupy k optimalizácii dotazov v PostgreSQL. Či ich použijete alebo nie, je na vás, ale oplatí sa o nich vedieť.
V niektorých nasledujúcich verziách PG sa situácia môže zmeniť, keď sa plánovač stane inteligentnejším, ale pre 9.4/9.6 to vyzerá približne rovnako, ako v príkladoch tu.
Zoberme si veľmi reálnu požiadavku:
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 názvoch tabuliek a políS „ruskými“ názvami polí a tabuliek možno zaobchádzať odlišne, ale je to vec vkusu. Pretože neexistujú zahraniční vývojári a PostgreSQL nám umožňuje dávať mená aj v hieroglyfoch, ak áno v úvodzovkách, potom predmety radšej pomenúvame jednoznačne a zrozumiteľne, aby nevznikli nezrovnalosti.
Pozrime sa na výsledný plán:
144 ms a takmer 53 kB vyrovnávacích pamätí - teda viac ako 400 MB dát! A budeme mať šťastie, ak budú v čase našej požiadavky všetky v cache, inak to pri čítaní z disku bude trvať mnohonásobne dlhšie.
Najdôležitejší je algoritmus!
Ak chcete nejako optimalizovať akúkoľvek požiadavku, musíte najprv pochopiť, čo by mala robiť.
Vývoj samotnej databázovej štruktúry nechajme nateraz mimo rámec tohto článku a zhodneme sa, že môžeme relatívne „lacne“ prepísať žiadosť a/alebo vyvaliť na základňu niektoré veci, ktoré potrebujeme Indexy.
Takže žiadosť:
— skontroluje existenciu aspoň nejakého dokumentu
- v stave, ktorý potrebujeme a určitého typu
- kde autor alebo výkonný umelec je zamestnanec, ktorého potrebujeme
PRIDAJ SA + LIMIT 1
Pomerne často je pre vývojára jednoduchšie napísať dotaz, kde sa najprv spojí veľké množstvo tabuliek a potom z celej tejto množiny zostane len jeden záznam. Jednoduchšie pre vývojárov však neznamená efektívnejšie pre databázu.
V našom prípade to boli len 3 tabuľky - a aký to má efekt...
Najprv sa zbavme spojenia s tabuľkou „Typ dokumentu“ a zároveň povedzme databáze, že náš typový záznam je jedinečný (vieme to, ale plánovač to ešte netuší):
WITH T AS (
SELECT
"@ТипДокумента"
FROM
"ТипДокумента"
WHERE
"ТипДокумента" = 'ПланРабот'
LIMIT 1
)
...
WHERE
d."ТипДокумента" = (TABLE T)
...Áno, ak tabuľka/CTE pozostáva z jedného poľa jedného záznamu, potom v PG môžete písať aj takto, namiesto
d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)Lenivé hodnotenie v dopytoch PostgreSQL
BitmapOr vs UNION
Bitmap Heap Scan nás v niektorých prípadoch vyjde poriadne draho – napríklad v našej situácii, keď požadovanú podmienku spĺňa pomerne veľa záznamov. Dostali sme to, pretože Podmienka ALEBO sa zmenila na BitmapOr- prevádzka v pláne.
Vráťme sa k pôvodnému problému - musíme nájsť zodpovedajúci záznam akýkoľvek z podmienok - to znamená, že nie je potrebné hľadať všetkých 59K záznamov za oboch podmienok. Existuje spôsob, ako vyriešiť jednu podmienku a choďte do druhého až vtedy, keď sa v prvom nič nenašlo. Pomôže nám nasledujúci dizajn:
(
SELECT
...
LIMIT 1
)
UNION ALL
(
SELECT
...
LIMIT 1
)
LIMIT 1„Externý“ LIMIT 1 zaisťuje, že vyhľadávanie sa skončí, keď sa nájde prvý záznam. A ak sa už nájde v prvom bloku, druhý blok sa nevykoná (nikdy nepopravený mať rešpekt z).
“Skrytie ťažkých podmienok v CASE”
V pôvodnom dotaze je mimoriadne nepohodlný moment – kontrola stavu oproti súvisiacej tabuľke „DocumentExtension“. Bez ohľadu na pravdivosť ostatných podmienok vo výraze (napr. d „Vymazané“ NIE JE PRAVDA), toto spojenie sa vždy vykoná a „stojí zdroje“. Viac alebo menej sa ich minie - závisí od veľkosti tohto stola.
Dotaz však môžete upraviť tak, aby vyhľadávanie súvisiaceho záznamu prebiehalo iba vtedy, keď je to skutočne potrebné:
SELECT
...
FROM
"Документ" d
WHERE
... /*index cond*/ AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
Raz z prepojenej tabuľky k nám žiadne z polí nie je potrebné pre výsledok, potom máme možnosť zmeniť JOIN na podmienku v poddotazi.
Nechajme indexované polia „mimo zátvoriek CASE“, do bloku WHEN pridajte jednoduché podmienky zo záznamu – a teraz sa „ťažký“ dotaz vykoná len pri prechode do THEN.
Moje priezvisko je "Total"
Výsledný dotaz zhromažďujeme so všetkými mechanikami opísanými vyššie:
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;Úprava [na] indexov
Cvičené oko si všimlo, že indexované podmienky v podblokoch UNION sú mierne odlišné – je to preto, že už máme na stole vhodné indexy. A ak by neexistovali, oplatilo by sa vytvoriť: Dokument (osoba 3, typ dokumentu) и Dokument (Typ dokumentu, Zamestnanec).
o poradí polí v podmienkach ROWZ pohľadu plánovača samozrejme môžete písať (A, B) = (constA, constB)A (B, A) = (constB, constA). Ale pri nahrávaní v poradí polí v indexe, takúto požiadavku je jednoducho pohodlnejšie ladiť neskôr.
Čo je v pláne?
Žiaľ, mali sme smolu a v prvom bloku UNION sa nič nenašlo, takže druhý bol ešte vykonaný. Ale aj tak – len 0.037 ms a 11 vyrovnávacích pamätí!
Zrýchlili sme požiadavku a znížili sme pumpovanie dát v pamäti niekoľko tisíckrát, pomocou pomerne jednoduchých techník - dobrý výsledok s trochou copy-paste. 🙂
Zdroj: hab.com