PostgreSQL Antipatterns: "Det må bare være ett!"

I SQL beskriver du "hva" du ønsker å få, ikke "hvordan" det skal gjøres. Derfor tar problemet med å utvikle SQL-spørringer i stil med "som det høres er hvordan det skrives" sin æresplass, sammen med særegenheter ved tilstandsevaluering i SQL.

I dag, ved hjelp av ekstremt enkle eksempler, la oss se hva dette kan føre til i brukssammenheng GROUP/DISTINCT и LIMIT med dem.

Det er hvis du skrev i forespørselen "Koble først til disse nettbrettene, og kast deretter ut alle duplikatene, det skal bare være en forekomst for hver nøkkel" – Det er akkurat slik det vil fungere, selv om tilkoblingen ikke var nødvendig i det hele tatt.

Og noen ganger er du heldig og det "bare fungerer", noen ganger har det en ubehagelig effekt på ytelsen, og noen ganger gir det effekter som er helt uventede fra utviklerens synspunkt.

Vel, kanskje ikke så spektakulært, men...

"Søtt par": JOIN + DISTINCT

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

Hvordan skulle det være klart hva de ville velg slike poster X som i Y er knyttet til den oppfylte betingelsen. Har sendt inn en forespørsel via JOIN - mottok noen verdier av pk flere ganger (nøyaktig hvor mange passende poster viste seg å være i Y). Hvordan fjerne? Sikkert DISTINCT!

Det er spesielt "hyggelig" når det for hver X-post er flere hundre relaterte Y-poster, og deretter duplikater fjernes heroisk ...

Hvordan fikse? Til å begynne med, innse at oppgaven kan endres til "velg de postene X der det er MINST ÉN i Y knyttet til betingelsen som er oppfylt" – Vi trenger tross alt ikke noe fra selve Y-platen.

Nestet FINNES

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

Noen versjoner av PostgreSQL forstår at i EXISTS er det nok å finne den første posten som kommer over, eldre gjør det ikke. Derfor foretrekker jeg alltid å indikere LIMIT 1 innenfor EXISTS.

SIDEFORSAMLING

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;

Det samme alternativet tillater, om nødvendig, å umiddelbart returnere noen data fra den funnet tilknyttede Y-posten samtidig. Et lignende alternativ er diskutert i artikkelen "PostgreSQL Antipatterns: sjeldne rekorder vil nå midten av JOIN".

"Hvorfor betale mer": DISTINCT [ON] + LIMIT 1

En ekstra fordel med slike spørringstransformasjoner er muligheten til enkelt å begrense opptellingen av poster hvis bare én/få av dem er nødvendig, som i følgende tilfelle:

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

Nå leser vi forespørselen og prøver å forstå hva DBMS skal gjøre:

• vi kobler platene
• unik av X.pk
• velg en av de gjenværende postene

Så hva fikk du? "Noen en rekord" fra de unike - og hvis du tar denne av de ikke-unike, vil resultatet på en eller annen måte endre seg? .. "Og hvis det ikke er noen forskjell, hvorfor betale mer?"

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

Og akkurat samme tema med GROUP BY + LIMIT 1.

"Jeg må bare spørre": implisitt GROUP + LIMIT

Lignende ting forekommer i forskjellige tomhetssjekker etiketter eller CTE-er etter hvert som forespørselen skrider frem:

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

Aggregerte funksjoner (count/min/max/sum/...) er vellykket utført på hele settet, selv uten å spesifisere det eksplisitt GROUP BY. Bare her med LIMIT de er ikke veldig vennlige.

Utbygger kan tenke "Nå, hvis det er poster der, så trenger jeg ikke mer enn LIMIT". Men du trenger ikke! Fordi for basen er det:

• telle hva de vil på alle poster
• gi så mange linjer som de ber om

Avhengig av målforholdene, er det hensiktsmessig å gjøre en av følgende erstatninger:

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

"Hvor mye skal henge i gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

En naiv utvikler kan oppriktig tro at utførelsen av en forespørsel vil stoppe, så snart vi finner de første $1 forskjellige verdiene som kommer over.

En gang i fremtiden kan og vil dette fungere takket være en ny node Indekshopp over skanning, hvis implementering er under utarbeidelse, men ikke ennå.

Så langt først alle poster vil bli hentet, er unike, og bare så mange av dem som forespurt vil bli returnert. Det er spesielt trist hvis vi ønsket noe sånt $ 1 = 4, og det er hundretusenvis av poster i tabellen ...

For ikke å være triste forgjeves, vil vi bruke en rekursiv spørring "DISTINCT for the Poor" fra PostgreSQL Wiki:

Kilde: www.habr.com