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

Пазете се от операции, които носят буфери...
Използвайки малка заявка като пример, нека да разгледаме някои универсални подходи за оптимизиране на заявки в 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;

относно имената на таблици и полета„Руските“ имена на полета и таблици могат да се третират по различен начин, но това е въпрос на вкус. Тъй като тук в Tensor няма чуждестранни разработчици и PostgreSQL ни позволява да даваме имена дори в йероглифи, ако те са оградено в кавички, тогава предпочитаме да назоваваме обектите недвусмислено и ясно, за да няма разминавания.
Нека да разгледаме получения план:

[вижте expand.tensor.ru]

144ms и почти 53K буфери - тоест повече от 400MB данни! И ние ще имаме късмет, ако всички те са в кеша до момента на нашата заявка, в противен случай ще отнеме много повече време при четене от диск.

Алгоритъмът е най-важен!

За да оптимизирате по някакъв начин всяка заявка, първо трябва да разберете какво трябва да прави.
Нека засега оставим разработването на самата структура на базата данни извън обхвата на тази статия и се съгласим, че можем сравнително „евтино“ пренапишете заявката и/или търкаляме върху основата някои от нещата, от които се нуждаем Индекси.

Така че молбата:
— проверява съществуването на поне някакъв документ
- в необходимото ни състояние и от определен вид
- където автор или изпълнител е служителят, от който се нуждаем

ПРИСЪЕДИНЕНЕ + ОГРАНИЧЕНИЕ 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 ще ни струва много - например в нашата ситуация, когато доста записи отговарят на изискваното условие. Разбрахме го, защото Условието ИЛИ се превърна в BitmapOr- операция в план.
Да се ​​върнем към първоначалния проблем - трябва да намерим съответстващ запис на всеки от условията - тоест няма нужда да търсите всички 59K записа и при двете условия. Има начин да се изработи едно условие и отидете на втория само когато нищо не е намерено в първия. Следният дизайн ще ни помогне:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

„Външен“ LIMIT 1 гарантира, че търсенето приключва, когато бъде намерен първият запис. И ако вече е намерен в първия блок, вторият блок няма да бъде изпълнен (никога не е изпълняван с уважение към).

„Скриване на трудни условия под CASE“

В оригиналната заявка има изключително неудобен момент - проверка на състоянието спрямо свързаната таблица „Разширение на документа“. Независимо от истинността на други условия в израза (напр. г. „Изтрит“ НЕ Е ВЯРНО), тази връзка винаги се изпълнява и „коства ресурси“. Повече или по-малко от тях ще бъдат изразходвани - зависи от размера на тази маса.
Но можете да промените заявката, така че търсенето на свързан запис да се извършва само когато е наистина необходимо:

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 са малко по-различни - това е така, защото вече имаме подходящи индекси в таблицата. И ако не съществуваха, би си струвало да се създадат: Документ (Person3, DocumentType) и Документ (тип документ, служител).
относно реда на полетата в условията на ROWОт гледна точка на проектанта, разбира се, можете да пишете (A, B) = (constA, constB)И (B, A) = (constB, constA). Но при запис по реда на полетата в индекса, такава заявка е просто по-удобна за отстраняване на грешки по-късно.
Какво има в плана?

[вижте expand.tensor.ru]

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

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