PostgreSQL Antimønstre: "Der må kun være én!"

I SQL beskriver du "hvad" du vil opnå, ikke "hvordan" det skal udføres. Derfor indtager problemet med at udvikle SQL-forespørgsler i stil med "som det høres er, hvordan det skrives" sin æresplads, sammen med funktioner ved beregning af betingelser i SQL.

I dag, ved hjælp af ekstremt enkle eksempler, lad os se, hvad dette kan føre til i forbindelse med brug GROUP/DISTINCT и LIMIT med dem.

Nu, hvis du skrev i anmodningen "Forbind først disse skilte, og smid derefter alle dubletterne ud, der skulle kun være én tilbage kopi for hver nøgle" - præcis sådan vil det fungere, selvom forbindelsen slet ikke var nødvendig.

Og nogle gange er du heldig, og det "virker bare", nogle gange har det en ubehagelig effekt på ydeevnen, og nogle gange giver det effekter, der er helt uventede fra udviklerens synspunkt.

Nå, måske ikke så spektakulært, men...

"Søde par": JOIN + DISTINCT

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

Det ville være klart, hvad de ville vælg poster X, for hvilke der er poster i Y, der er relateret til den opfyldte betingelse. Skrev en anmodning via JOIN — fik nogle pk-værdier adskillige gange (nøjagtig hvor mange passende poster dukkede op i Y). Hvordan fjerner man? Sikkert DISTINCT!

Det er især "glædeligt", når der for hver X-record er flere hundrede relaterede Y-records, og så er dubletterne heroisk fjernet...

Hvordan rettes? Til at begynde med, indse, at problemet kan ændres til "vælg poster X, for hvilke der i Y er MINDST EN forbundet med den opfyldte betingelse" - vi behøver jo ikke noget fra selve Y-pladen.

Indlejret FINDER

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

Nogle versioner af PostgreSQL forstår, at i EXISTS er det nok at finde den første post, der kommer op, ældre gør det ikke. Derfor foretrækker jeg altid at angive LIMIT 1 inden 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;

Den samme mulighed giver mulighed for om nødvendigt straks at returnere nogle data fra den fundne tilknyttede Y-record. En lignende mulighed diskuteres i artiklen "PostgreSQL-antimønstre: en sjælden post vil nå midten af ​​en JOIN".

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

En yderligere fordel ved sådanne forespørgselstransformationer er muligheden for nemt at begrænse søgningen efter poster, hvis der kun er behov for en eller nogle få af dem, som i følgende tilfælde:

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

Nu læser vi anmodningen og prøver at forstå, hvad DBMS foreslås at gøre:

• forbinder skiltene
• unik af X.pk
• fra de resterende poster skal du vælge en

Så hvad fik du? "Bare én indgang" fra de unikke - og hvis vi tager denne af de ikke-unikke, vil resultatet så ændre sig på en eller anden måde?.. "Og hvis der ikke er nogen forskel, hvorfor så betale mere?"

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

Og præcis samme emne med GROUP BY + LIMIT 1.

"Jeg bliver bare nødt til at spørge": implicit GROUP + LIMIT

Lignende ting opstår på forskellige ikke-tomhedskontrol tegn eller CTE'er, efterhånden som anmodningen skrider frem:

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

Samlede funktioner (count/min/max/sum/...) udføres med succes på hele sættet, selv uden eksplicitte instruktioner GROUP BY. Kun med LIMIT de er ikke særlig venlige.

Udvikleren kan tænke "hvis der er poster der, så behøver jeg ikke mere end LIMIT". Men gør det ikke! Fordi for basen er det:

• tælle hvad de vil ifølge alle optegnelser
• giv så mange linjer, som de beder om

Afhængigt af målbetingelserne er det hensigtsmæssigt at foretage en af ​​følgende udskiftninger:

• (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 meget der skal hænge i gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

En naiv udvikler kan oprigtigt tro, at anmodningen vil stoppe med at udføre. så snart vi finder $1 af de første forskellige værdier, der støder på.

Engang i fremtiden kan og vil dette fungere takket være en ny node Indeks Spring Scan, hvis implementering i øjeblikket er under udarbejdelse, men endnu ikke.

For nu først alle poster vil blive hentet, er unikke, og kun fra dem vil det anmodede beløb blive returneret. Det er især trist, hvis vi ville have noget som $ 1 = 4, og der er hundredtusindvis af poster i tabellen...

For ikke at være trist forgæves, lad os bruge en rekursiv forespørgsel "DISTINCT er for de fattige" fra PostgreSQL Wiki:

Kilde: www.habr.com