ПостгреСКЛ антиобрасци: "Мора постојати само један!"

У СКЛ-у описујете „шта“ желите да постигнете, а не „како“ то треба да се изврши. Дакле, проблем развоја СКЛ упита у стилу „како се чује, тако се и пише“ заузима своје почасно место, заједно са карактеристике израчунавања услова у СКЛ-у.

Данас, користећи изузетно једноставне примере, да видимо до чега то може довести у контексту употребе GROUP/DISTINCT и LIMIT са њима.

Е сад, ако сте написали у захтеву „Прво повежите ове знакове, а затим избаците све дупликате, треба да остане само један копија за сваки кључ" - управо тако ће функционисати, чак и ако веза уопште није била потребна.

А понекад имате среће и то „само ради“, понекад има непријатан ефекат на перформансе, а понекад даје ефекте који су потпуно неочекивани са тачке гледишта програмера.

Па, можда и није тако спектакуларно, али...

„Слатки пар“: ПРИДРУЖИ СЕ + ДИСТИНЦТ

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

Било би јасно шта желе изабрати записе Кс за које постоје записи у И који се односе на испуњен услов. Написао захтев преко JOIN — добио неке пк вредности неколико пута (колико се тачно одговарајућих уноса појавило у И). Како уклонити? Сигурно DISTINCT!

Посебно је "задовољство" када за сваки Кс-запис постоји неколико стотина повезаних И-записа, а затим се дупликати херојски уклањају...

Како поправити? За почетак, схватите да се проблем може модификовати на „одаберите записе Кс за које у И постоји НАЈМАЊЕ ЈЕДАН повезан са испуњеним условом“ - на крају крајева, не треба нам ништа од самог И-записа.

Угнежђено ЕКСИСТС

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

Неке верзије ПостгреСКЛ-а разумеју да је у ЕКСИСТС довољно пронаћи први унос који се појави, старије не. Зато више волим да увек указујем 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;

Иста опција омогућава, ако је потребно, да се одмах врате неки подаци из пронађеног повезаног И-записа. Слична опција је разматрана у чланку „ПостгреСКЛ Антипаттернс: редак запис ће стићи до средине ЈОИН-а“.

„Зашто платити више“: ДИСТИНЦТ [ОН] + ЛИМИТ 1

Додатна предност таквих трансформација упита је могућност лаког ограничавања претраге записа ако је потребан само један или неколико њих, као у следећем случају:

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

Сада читамо захтев и покушавамо да разумемо шта се ДБМС предлаже да уради:

• повезивање знакова
• јединствен од Кс.пк
• од преосталих уноса изаберите један

Па шта си добио? "Само један унос" од уникатних – а ако узмемо ову од нејединствених, да ли ће се резултат некако променити?.. „А ако нема разлике, зашто плаћати више?“

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

И потпуно иста тема са GROUP BY + LIMIT 1.

„Морам само да питам“: имплицитна ГРОУП + ЛИМИТ

Сличне ствари се дешавају на различитим местима провере непразнине знакове или ЦТЕ како захтев напредује:

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

Агрегатне функције (count/min/max/sum/...) се успешно извршавају на целом скупу, чак и без експлицитних инструкција GROUP BY. Само са 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, а у табели има стотине хиљада записа...

Да не бисмо узалуд били тужни, употребимо рекурзивни упит „ДИСТИНЦТ је за сиромашне“ са ПостгреСКЛ Вики:

Извор: ввв.хабр.цом