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-запису нам нічого не потрібно.

Вкладений EXISTS

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

Деякі версії PostgreSQL розуміють, що в EXISTS достатньо знайти перший запис, більш старі — ні. Тому я волію завжди вказувати LIMIT 1 всередині EXISTS.

LATERAL JOIN

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.

"Мені тільки запитати": неявний GROUP + LIMIT

Подібні речі зустрічаються при різних перевірках непорожнечі таблички або 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))

"Скільки вішати в грамах": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Наївний розробник може щиро вважати, що виконання запиту зупиниться, як тільки ми знайдемо $1 перших, що попалися різних значень.

Колись у майбутньому це може так і працюватиме завдяки новому вузлу. Index Skip Scan, Реалізація якого зараз опрацьовується, але поки що - ні.

Поки що спочатку будуть витягнуті всі записи, Унікалізовані, і тільки вже з них повернеться скільки запитано. Особливо сумно буває, якщо ми хотіли щось на зразок $ 1 = 4, А записів у таблиці - сотні тисяч ...

Щоб не сумувати марно, скористаємося рекурсивним запитом «DISTINCT для бідних» з PostgreSQL Wiki:

Джерело: habr.com