PostgreSQL Antipatterns: "Трябва да има само един!"

В SQL вие описвате „какво“ искате да постигнете, а не „как“ трябва да се изпълни. Следователно проблемът с разработването на SQL заявки в стил „както се чува, така се пише“ заема своето почетно място, заедно с характеристики на изчисляване на условия в SQL.

Днес, използвайки изключително прости примери, нека видим до какво може да доведе това в контекста на употреба GROUP/DISTINCT и LIMIT с тях.

Сега, ако сте написали в заявката „първо свържете тези знаци и след това изхвърлете всички дубликати, трябва да остане само един копие за всеки ключ" - точно така ще работи, дори ако връзката изобщо не е необходима.

И понякога имате късмет и той „просто работи“, понякога има неприятен ефект върху производителността, а понякога дава ефекти, които са напълно неочаквани от гледна точка на разработчика.

Е, може би не толкова зрелищно, но...

„Сладка двойка“: JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Щеше да е ясно какво искат изберете записи X, за които има записи в Y, които са свързани с изпълненото условие. Написахте заявка чрез JOIN — получи някои pk стойности няколко пъти (точно колко подходящи записи се появиха в Y). Как да премахнете? Със сигурност DISTINCT!

Особено „приятно“ е, когато за всеки X-запис има няколкостотин свързани Y-записа и след това дубликатите се премахват героично...

Как да поправя? Като начало осъзнайте, че проблемът може да бъде променен до „изберете записи X, за които в Y има ПОНЕ ЕДИН, свързан с изпълненото условие“ - в крайна сметка не се нуждаем от нищо от самия Y-запис.

Вложени СЪЩЕСТВУВАТ

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Някои версии на PostgreSQL разбират, че в EXISTS е достатъчно да се намери първият запис, който се появява, по-старите не го правят. Затова предпочитам винаги да посочвам LIMIT 1 в EXISTS.

СТРАНИЧНО СЪЕДИНЕНИЕ

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Същата опция позволява, ако е необходимо, незабавно връщане на някои данни от намерения свързан Y-запис. Подобен вариант е разгледан в статията „PostgreSQL Antipatterns: рядък запис ще достигне средата на JOIN“.

„Защо да плащате повече“: DISTINCT [ON] + LIMIT 1

Допълнителна полза от такива трансформации на заявки е възможността лесно да се ограничи търсенето на записи, ако са необходими само един или няколко от тях, както в следния случай:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Сега четем заявката и се опитваме да разберем какво се предлага да прави СУБД:

• свързване на знаците
• уникален от X.pk
• от останалите записи изберете един

И така, какво получихте? „Само едно влизане“ от уникалните - и ако вземем този от неуникалните, ще се промени ли резултатът по някакъв начин?.. "А ако няма разлика, защо да плащаме повече?"

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

И абсолютно същата тема с GROUP BY + LIMIT 1.

„Просто трябва да попитам“: имплицитна ГРУПА + ОГРАНИЧЕНИЕ

Подобни неща се случват при различни проверки за непразнота знаци или CTE, докато заявката напредва:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Агрегирани функции (count/min/max/sum/...) се изпълняват успешно на целия набор, дори без изрични инструкции GROUP BY. Само със LIMIT те не са много приятелски настроени.

Разработчикът може да мисли „ако има записи там, тогава не ми трябват повече от LIMIT“. Но недей така! Защото за основата е:

• броят каквото искат според всички записи
• дайте толкова редове, колкото поискат

В зависимост от целевите условия е подходящо да се направи едно от следните замествания:

• (count + LIMIT 1) = 0 на NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 на EXISTS(LIMIT 1)
• count >= N на (SELECT count(*) FROM (... LIMIT N))

„Колко да претеглите в грамове“: РАЗЛИЧНО + ОГРАНИЧЕНИЕ

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Наивен разработчик може искрено да вярва, че заявката ще спре да се изпълнява. веднага щом намерим $1 от първите различни стойности, които се срещат.

Някога в бъдеще това може и ще работи благодарение на нов възел Сканиране при пропускане на индекс, чието внедряване в момента се разработва, но все още не.

За сега първо всички записи ще бъдат извлечени, са уникални и само от тях ще бъде върната исканата сума. Особено тъжно е, ако искахме нещо подобно $ 1 = 4, а в таблицата има стотици хиляди записи...

За да не бъдем тъжни напразно, нека използваме рекурсивна заявка „DISTINCT е за бедните“ от PostgreSQL Wiki:

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