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