PostgreSQL antipatterns: "Turi likti tik vienas!"

SQL jūs apibūdinate „ką“ norite pasiekti, o ne „kaip“ tai turėtų būti įvykdyta. Todėl SQL užklausų kūrimo tokiu stiliumi „kaip girdima, kaip rašoma“ problema užima garbės vietą kartu su SQL sąlygų skaičiavimo ypatybės.

Šiandien, naudodamiesi labai paprastais pavyzdžiais, pažiūrėkime, ką tai gali sukelti naudojimo kontekste GROUP/DISTINCT и LIMIT su jais.

Dabar, jei parašėte prašyme „Pirmiausia sujunkite šiuos ženklus, o tada išmeskite visus dublikatus, turėtų likti tik vienas kopija kiekvienam raktui" - būtent taip jis veiks, net jei ryšio visai nereikėjo.

O kartais pasiseka ir tai „tiesiog veikia“, kartais tai daro nemalonų poveikį našumui, o kartais suteikia efektus, kurie kūrėjo požiūriu yra visiškai netikėti.

Na, gal ne taip įspūdingai, bet...

„Saldi pora“: PRISIJUNK + ATSKIRTI

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

Būtų aišku, ko jie nori pasirinkite įrašus X, kurių Y yra įrašų, susijusių su įvykdyta sąlyga. Parašė prašymą per JOIN - keletą kartų gavo kai kurias pk reikšmes (tiksliai tiek, kiek tinkamų įrašų atsirado Y). Kaip pašalinti? Žinoma DISTINCT!

Ypač „džiugina“, kai kiekvienam X įrašui yra keli šimtai susijusių Y įrašų, o tada dublikatai herojiškai pašalinami...

Kaip pataisyti? Pirmiausia supraskite, kad problemą galima pakeisti „pasirinkti įrašus X, kurių Y yra BENT VIENAS, susietas su įvykdyta sąlyga“ - juk mums nieko nereikia iš paties Y įrašo.

Įdėtas EGISTRAVIMAS

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

Kai kurios PostgreSQL versijos supranta, kad EXISTS užtenka rasti pirmą pasitaikiusį įrašą, senesnėse – ne. Todėl norėčiau visada nurodyti LIMIT 1 per EXISTS.

ŠONINIS PRISIJUNGIMAS

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;

Ta pati parinktis leidžia, jei reikia, nedelsiant grąžinti kai kuriuos duomenis iš rasto susieto Y įrašo. Panaši galimybė aptariama straipsnyje „PostgreSQL antipatterns: retas įrašas pasieks JOIN vidurį“.

„Kodėl mokėti daugiau“: ATSKIRTI [ĮJUNGTA] + 1 LIMITAS

Papildomas tokių užklausų transformacijų pranašumas yra galimybė lengvai apriboti įrašų paiešką, jei reikia tik vieno ar kelių iš jų, kaip šiuo atveju:

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

Dabar perskaitome užklausą ir bandome suprasti, ką DBVS siūloma daryti:

• jungiančius ženklus
• unikalus X.pk
• iš likusių įrašų pasirinkite vieną

Taigi ką gavai? „Tik vienas įėjimas“ iš unikalių - o jei paimsime šitą iš neunikalių, ar rezultatas kaip nors pasikeis?.. „O jei nėra skirtumo, kam mokėti daugiau?

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

Ir lygiai ta pati tema su GROUP BY + LIMIT 1.

„Aš tiesiog turiu paklausti“: numanoma GROUP + LIMIT

Panašūs dalykai vyksta skirtingai netuštumos patikrinimai pasirašo arba CTE, kai užklausa vyksta:

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

Suvestinės funkcijos (count/min/max/sum/...) yra sėkmingai vykdomi visame rinkinyje, net ir be aiškių nurodymų GROUP BY. Tik su LIMIT jie nėra labai draugiški.

Kūrėjas gali galvoti „Jei ten yra įrašų, man reikia ne daugiau kaip LIMIT“. Bet nedaryk to! Nes pagrindui tai yra:

• skaičiuok ką nori pagal visus įrašus
• duoti tiek eilučių, kiek jie prašo

Atsižvelgiant į tikslines sąlygas, tikslinga atlikti vieną iš šių pakeitimų:

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

„Kiek pakabinti gramais“: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivus kūrėjas gali nuoširdžiai tikėti, kad užklausa nustos vykdyti. kai tik rasime 1 USD iš pirmųjų skirtingų reikšmių.

Kada nors ateityje tai gali veikti ir veiks dėl naujo mazgo Indekso praleidimo nuskaitymas, kurio įgyvendinimas šiuo metu rengiamas, bet dar ne.

Kol kas pirmiausia bus gauti visi įrašai, yra unikalūs, ir tik iš jų bus grąžinta prašoma suma. Ypač liūdna, jei norėjome kažko panašaus 1 USD = 4, o lentelėje yra šimtai tūkstančių įrašų...

Kad nebūtų veltui liūdna, naudokite rekursinę užklausą „DISTINCT skirtas vargšams“ iš PostgreSQL Wiki:

Šaltinis: www.habr.com