PostgreSQL Antipatterns: вредни JOIN и OR

Бойте се от операции, 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 ни позволява да даваме имена дори и на йероглифите, ако те са заключени в кавички, предпочитаме да именуваме обектите ясно и разбираемо, за да не възникват недоразумения.
Нека разгледаме получения план:

Както и предполагахме, намерихме всичките 30 записа. Но за това изразходихме 60% от общото време — защото направихме и 30 търсения по индекса. А по-малко — може ли?

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 срещу 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.
Моята фамилия е «Итого»

Събираме крайното запитване с всички описани по-горе механики:

Събираме заключителната заявка с всички описани по-горе механики:

С T КАТО (
  ИЗБЕРИ
    "@ТипДокумента"
  ОТ
    "ТипДокумента"
  КЪДЕ
    "ТипДокумента" = 'ПланРабот'
)
  (
    ИЗБЕРИ
      ИСТИНА
    ОТ
      "Документ" d
    КЪДЕ
      ("Лицо3", "ТипДокумента") = (19091, (ТАБЛИЦА T)) И
      СЛУЧАЙ
        КОГАТО "$Черновик" Е NULL И "Удален" НЕ Е ИСТИНА ТОГАВА (
          ИЗБЕРИ
            "Состояние"[1] Е ИСТИНА
          ОТ
            "ДокументРасширение"
          КЪДЕ
            "@Документ" = d."@Документ"
        )
      КРАЙ
    ОГРАНИЧИ 1
  )
СЪЮЗ ВСИЧКИ
  (
    ИЗБЕРИ
      ИСТИНА
    ОТ
      "Документ" d
    КЪДЕ
      ("ТипДокумента", "Сотрудник") = ((ТАБЛИЦА T), 19091) И
      СЛУЧАЙ
        КОГАТО "$Черновик" Е NULL И "Удален" НЕ Е ИСТИНА ТОГАВА (
          ИЗБЕРИ
            "Состояние"[1] Е ИСТИНА
          ОТ
            "ДокументРасширение"
          КЪДЕ
            "@Документ" = d."@Документ"
        )
      КРАЙ
    ОГРАНИЧИ 1
  )
ОГРАНИЧИ 1;

Регулираме [по] индексите

Опитното око забеляза, че индексируемите условия в подблоковете UNION леко се различават — това е така, защото вече имаме подходящи индекси на таблицата. А ако ги нямаше — тогава щеше да си струва да се създадат: Документ(Лицо3, ТипДокумента) и Документ(ТипДокумента, Сотрудник).
за реда на полетата в ROW-условиятаОт гледна точка на планировчика, разбира се, може да се напише и (A, B) = (constA, constB), и (B, A) = (constB, constA). Но при записването в реда на полетата в индекса, такава заявка е просто по-удобна после за отстраняване на грешки.
Какъв е планът?

Както и предполагахме, намерихме всичките 30 записа. Но за това изразходихме 60% от общото време — защото направихме и 30 търсения по индекса. А по-малко — може ли?

За съжаление, не ни провървя, и в първия UNION-блок нищо не се намери, затова вторият все пак продължи с изпълнението. Но дори и при това — само 0.037ms и 11 буфера!
Ускорихме заявката и намалихме "прокачването" на данни в паметта хиляди пъти, използвайки достатъчно прости методи — не лош резултат при малко копи-пейст. 🙂

Източник: habr.com