Бойцеся аперацый, buffers якія прыносяць…
На прыкладзе невялікага запыту разгледзім некаторыя ўніверсальныя падыходы да аптымізацыі запытаў на PostgreSQL. Карыстацца імі ці не - выбіраць вам, але ведаць пра іх варта.
У нейкіх наступных версіях PG сітуацыя можа змяніцца з "паразуменнем" планавальніка, але для 9.4/9.6 яна выглядае прыкладна аднолькава, як прыклады тут.
Вазьму цалкам рэальны запыт:
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;
пра імёны табліц і палёўДа «рускіх» назваў палёў і табліц можна ставіцца па-рознаму, але гэтая справа густу. Паколькі няма распрацоўшчыкаў-замежнікаў, а PostgreSQL дазваляе нам даваць назвы хоць іерогліфамі, калі яны заключаны ў двукоссі, то мы аддаем перавагу назваць аб'екты адназначна-зразумела, каб не ўзнікала розначытанняў.
Паглядзім на атрыманы план:
144ms і амаль 53K buffers - гэта значыць больш за 400MB дадзеных! І нам павязе, калі ўсе яны апынуцца ў кэшы да моманту нашага запыту, інакш ён стане ў разы даўжэй пры вычытванні з кружэлкі.
Алгарытм важней за ўсё!
Каб неяк аптымізаваць любы запыт, трэба спачатку зразумець, што ж ён увогуле павінен рабіць.
Пакінем пакуль за рамкамі гэтага артыкула распрацоўку самой структуры БД, і дамовімся, што мы можам адносна "танна" перапісаць запыт і/ці накаціць на базу нейкія патрэбныя нам індэксы.
Такім чынам, запыт:
- правярае існаванне хоць нейкага дакумента
- у патрэбным нам стане і вызначанага тыпу
- дзе аўтарам або выканаўцам з'яўляецца патрэбны нам супрацоўнік
JOIN + LIMIT 1
Досыць часта распрацоўніку прасцей напісаць запыт, дзе спачатку робіцца злучэнне вялікай колькасці табліц, а потым з усяго гэтага мноства застаецца адзін-адзіны запіс. Але прасцей для распрацоўніка - не значыць больш эфектыўна для БД.
У нашым выпадку табліц было ўсяго 3 - а які эфект…
Давайце для пачатку пазбавімся ад злучэння з табліцай «Тып Дакумента», а заадно падкажам базе, што ў запіс тыпу ў нас унікальны (мы-то гэта ведаем, а вось планавальнік пакуль не здагадваецца):
WITH T AS (
SELECT
"@ТипДокумента"
FROM
"ТипДокумента"
WHERE
"ТипДокумента" = 'ПланРабот'
LIMIT 1
)
...
WHERE
d."ТипДокумента" = (TABLE T)
...Так, калі табліца/CTE складаецца з адзінага поля адзінага ж запісу, то ў PG можна пісаць нават так, замест
d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)«Лянівыя» вылічэнні ў запытах PostgreSQL
BitmapOr vs UNION
У некаторых выпадках Bitmap Heap Scan будзе каштаваць нам вельмі дорага - напрыклад, у нашай сітуацыі, калі дастаткова шмат запісаў падпадае пад патрабаваную ўмову. Атрымалі мы яго з-за OR-ўмовы, які ператварыўся ў BitmapOr-аперацыю ў плане.
Вернемся да зыходнай задачы - трэба знайсці запіс, які адпавядае любому з умоў - гэта значыць няма чаго шукаць усе 59K запісаў па абодвух умовах. Ёсць спосаб адпрацаваць адну ўмову, а да другога перайсці толькі калі па першым нічога не знайшлося. Нам дапаможа такая канструкцыя:
(
SELECT
...
LIMIT 1
)
UNION ALL
(
SELECT
...
LIMIT 1
)
LIMIT 1"Вонкавы" LIMIT 1 гарантуе, што пошук завершыцца пры знаходжанні першага ж запісу. І калі яна знойдзецца ўжо ў першым блоку, выкананне другога ажыццяўляцца не будзе (never executed у плане).
Хаваем пад CASE складаныя ўмовы
У зыходным запыце ёсць вельмі нязручны момант - праверка стану па звязанай табліцы "ДакументПашырэнне". Незалежна ад сапраўднасці астатніх умоў у выразе (напрыклад, d.«Выдалены» IS NOT TRUE), гэта злучэнне выконваецца заўсёды і «каштуе рэсурсаў». Больш ці менш іх будзе выдаткавана - залежыць ад аб'ёму гэтай табліцы.
Але можна мадыфікаваць запыт так, каб пошук звязанага запісу адбываўся б толькі калі гэта сапраўды неабходна:
SELECT
...
FROM
"Документ" d
WHERE
... /*index cond*/ AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
Раз са злучанай табліцы нам не трэба для выніку ні адно з палёў, то мы маем магчымасць ператварыць JOIN ва ўмову па подзапрос.
Пакінем индексируемые палі "за дужкамі" CASE, простыя ўмовы ад запісу ўносім у WHEN-блок - і зараз "цяжкі" запыт выконваецца толькі пры пераходзе ў THEN.
Маё прозвішча «Разам»
Збіраны выніковы запыт з усімі апісанымі вышэй механікамі:
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;Падганяем [пад] індэксы
Накіданае вока заўважыў, што индексируемые ўмовы ў падблоках UNION ледзь адрозніваюцца – гэта таму, што ў нас ужо ёсць прыдатныя індэксы на табліцы. А калі б іх не было - то варта было б стварыць: Дакумент(Твар3, ТыпДакумента) и Дакумент(ТыпДакумента, Супрацоўнік).
аб парадку палёў у ROW-умовахЗ пункту гледжання планавальніка, вядома, можна напісаць і (A, B) = (constA, constB), І (B, A) = (constB, constA). Але пры запісе у парадку прытрымлівання палёў у індэксе, такі запыт проста зручней потым адладжваць.
Што ў плане?
Нажаль, нам не павезла, і ў першым UNION-блоку нічога не знайшлося, таму другі ўсёткі пайшоў на выкананне. Але нават пры гэтым - усяго 0.037ms і 11 buffers!
Мы паскорылі запыт і скарацілі "прапампоўку" дадзеных у памяці у некалькі тысяч разоў, скарыстаўшыся досыць простымі методыкамі - нядрэнны вынік пры невялікай капіпасце. 🙂
Крыніца: habr.com