Pozor na operace, které přinášejí vyrovnávací paměti...
Pomocí malého dotazu jako příkladu se podívejme na některé univerzální přístupy k optimalizaci dotazů v PostgreSQL. Zda je použijete nebo ne, je na vás, ale stojí za to o nich vědět.
V některých následujících verzích PG se situace může změnit, protože plánovač bude chytřejší, ale pro 9.4/9.6 to vypadá přibližně stejně jako v příkladech zde.
Vezměme velmi reálnou žádost:
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ázvech tabulek a políS „ruskými“ názvy polí a tabulek lze zacházet různě, ale to je věc vkusu. Protože neexistují žádní zahraniční vývojáři a PostgreSQL nám umožňuje dávat jména i v hieroglyfech, pokud ano uzavřený v uvozovkách, pak objekty raději pojmenováváme jednoznačně a srozumitelně, aby nevznikaly nesrovnalosti.
Podívejme se na výsledný plán:
144 ms a téměř 53 kB vyrovnávací paměti - tedy více než 400 MB dat! A budeme mít štěstí, když budou v době našeho požadavku všechny v cache, jinak to bude při čtení z disku trvat mnohonásobně déle.
Algoritmus je nejdůležitější!
Aby bylo možné nějakým způsobem optimalizovat jakýkoli požadavek, musíte nejprve pochopit, co by měl dělat.
Vývoj samotné databázové struktury nechme prozatím mimo rámec tohoto článku a shodneme se, že můžeme relativně „levně“ přepsat žádost a/nebo válet na základnu některé věci, které potřebujeme Indexy.
Takže žádost:
— zkontroluje existenci alespoň nějakého dokumentu
- ve stavu, který potřebujeme a určitého typu
- kde autor nebo výkonný umělec je zaměstnanec, kterého potřebujeme
PŘIPOJTE SE + LIMIT 1
Dost často je pro vývojáře snazší napsat dotaz, kde se nejprve spojí velké množství tabulek, a pak z celé této sady zůstane pouze jeden záznam. Ale jednodušší pro vývojáře neznamená efektivnější pro databázi.
V našem případě to byly jen 3 stoly - a jaký to má efekt...
Zbavme se nejprve spojení s tabulkou „Typ dokumentu“ a zároveň sdělme databázi, že náš typový záznam je jedinečný (víme to, ale plánovač zatím nic netuší):
WITH T AS (
SELECT
"@ТипДокумента"
FROM
"ТипДокумента"
WHERE
"ТипДокумента" = 'ПланРабот'
LIMIT 1
)
...
WHERE
d."ТипДокумента" = (TABLE T)
...Ano, pokud se tabulka/CTE skládá z jednoho pole jednoho záznamu, pak v PG můžete psát i takto, místo
d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)Líné hodnocení v dotazech PostgreSQL
BitmapOr vs UNION
Bitmap Heap Scan nás v některých případech bude stát hodně – například v naší situaci, kdy požadovanou podmínku splňuje poměrně hodně záznamů. Dostali jsme to, protože Podmínka OR se změnila na BitmapOr- provoz v plánu.
Vraťme se k původnímu problému - musíme najít odpovídající záznam všem z podmínek - to znamená, že není potřeba hledat všech 59K záznamů za obou podmínek. Existuje způsob, jak vyřešit jednu podmínku, a do druhého jděte teprve tehdy, když v prvním nebylo nic nalezeno. Pomůže nám následující design:
(
SELECT
...
LIMIT 1
)
UNION ALL
(
SELECT
...
LIMIT 1
)
LIMIT 1„Externí“ LIMIT 1 zajišťuje, že vyhledávání skončí, když je nalezen první záznam. A pokud je již nalezen v prvním bloku, druhý blok nebude proveden (nikdy nepopraven vzhledem k).
“Skrytí obtížných podmínek pod CASE”
V původním dotazu je extrémně nepohodlný moment - kontrola stavu podle související tabulky „DocumentExtension“. Bez ohledu na pravdivost dalších podmínek ve výrazu (např. d „Smazáno“ NENÍ PRAVDA), toto připojení se vždy provede a „stojí prostředky“. Utratí se jich více či méně – záleží na velikosti tohoto stolu.
Dotaz však můžete upravit tak, aby hledání souvisejícího záznamu probíhalo pouze tehdy, když je to skutečně nutné:
SELECT
...
FROM
"Документ" d
WHERE
... /*index cond*/ AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
Jednou z propojené tabulky k nám žádné z polí není pro výsledek potřeba, pak máme možnost změnit JOIN na podmínku v dílčím dotazu.
Ponechme indexovaná pole „mimo závorky CASE“, do bloku WHEN přidejte jednoduché podmínky ze záznamu – a nyní se „těžký“ dotaz provede pouze při přechodu do THEN.
Moje příjmení je "Total"
Shromažďujeme výsledný dotaz se všemi výše popsanými mechanikami:
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 indexů [na]
Cvičené oko si všimlo, že indexované podmínky v podblocích UNION jsou mírně odlišné - je to proto, že již máme na stole vhodné indexy. A pokud by neexistovaly, stálo by za to vytvořit: Dokument (osoba 3, typ dokumentu) и Dokument (Typ dokumentu, Zaměstnanec).
o pořadí polí v ŘÁDKOVÝCH podmínkáchZ pohledu plánovače samozřejmě psát můžete (A, B) = (constA, constB)A (B, A) = (constB, constA). Ale při nahrávání v pořadí polí v indexu, takový požadavek je jednoduše pohodlnější ladit později.
Co je v plánu?
Bohužel jsme měli smůlu a v prvním bloku UNION se nic nenašlo, takže druhý byl ještě proveden. Ale i tak – jedině 0.037 ms a 11 vyrovnávacích pamětí!
Zrychlili jsme požadavek a omezili čerpání dat v paměti několik tisíckrát, pomocí poměrně jednoduchých technik - dobrý výsledek s trochou copy-paste. 🙂
Zdroj: www.habr.com