Бійтеся операцій, 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