PostgreSQL Antipatterns: «Det ska bara finnas en!»

I SQL beskriver du "vad" du vill ha, inte "hur" det ska utföras. Därför ligger problemet med att utveckla SQL-frågor i stilen "som hörs, så skrivs" på sin hedersplats, jämte egenskaperna för beräkning av villkor i SQL.

I dag kommer vi att titta på några mycket enkla exempel på vad detta kan leda till i sammanhanget av att använda GROUP/DISTINCT och LIMIT tillsammans med dem.

Om du har skrivit i frågan «först anslut dessa tabeller, och sedan ta bort alla dubbletter, det ska bara finnas en instans per nyckel» — så kommer det att fungera, även om anslutningen inte alls var nödvändig.

Och ibland har man tur och det "bara fungerar", ibland påverkar det prestandan negativt, och ibland ger det helt oväntade effekter ur utvecklarens synvinkel.

Nåväl, kanske inte så spektakulära, men...

«Söta paret»: JOIN + DISTINCT

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

Det är tydligt vad man ville välj sådana poster X för vilka det finns kopplade poster i Y med det aktuella villkoret.Frågan skrevs genom JOIN — och man fick några pk-värden flera gånger (exakt så många som det fanns passande poster i Y). Hur tar man bort dem? Självklart DISTINCT!

Det är särskilt "uppmuntrande" när varje X-post hittar flera hundra kopplade Y-poster, och sedan tar man heroiskt bort dubbletterna...

Hur rättar man till det? Först och främst måste man inse att uppgiften kan modifieras till «välj sådana poster X för vilka det finns ÅTMINSTONE ETT kopplad med det aktuella villkoret i Y» — för från själva Y-posten behöver vi inget.

Nästlad EXISTS

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

Vissa versioner av PostgreSQL förstår att det i EXISTS är tillräckligt att hitta den första posten, äldre versioner gör inte det. Därför föredrar jag alltid att ange LIMIT 1 inuti 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;

Denna variant tillåter också att samtidigt returnera vissa data från den funna kopplade Y-posten. En liknande variant har diskuterats i artikeln «PostgreSQL Antipatterns: sällan når en post mitten av JOIN».

«Varför betala mer»: DISTINCT [ON] + LIMIT 1

En ytterligare fördel med denna typ av frågeställningar är möjligheten att enkelt begränsa antalet poster, om endast en eller några av dem behövs, som i följande fall:

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

Nu läser vi frågan och försöker förstå vad databashanteringssystemet föreslår att göra:

• vi kopplar samman tabellerna
• vi unikgör efter X.pk
• från de återstående posterna väljer vi någon en

Så vad har vi fått? «Någon enskild post» från de unikifierade – och om vi tar denna enskilda från de icke-unikifierade, skulle resultatet ändras på något sätt?.. «Och om det inte spelar någon roll, varför betala mer?»

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- här kan lämpliga villkor läggas till
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Och exakt samma tema med GROUP BY + LIMIT 1.

«Jag vill bara fråga»: implicit GROUP + LIMIT

Sådana saker förekommer vid olika kontroller av tomhet tabellen eller CTE under körningen av frågan:

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

Aggregatfunktioner (count/min/max/sum/...) kärnverkar över hela datasetet, även utan tydlig specifikation GROUP BY. Men de fungerar inte så bra med LIMIT de.

Utvecklaren kan tänka «Om det finns poster där, behöver jag inte mer än LIMIT». Men så bör man inte tänka! Eftersom det för databasen är:

• beräkna vad som efterfrågas ge tillbaka så många rader som begärs
• Beroende på målvillkoren kan det vara lämpligt att göra en av följande byten:

(count + LIMIT 1) = 0

• NOT EXISTS(LIMIT 1) till (count + LIMIT 1) > 0
• EXISTS(LIMIT 1) till count >= N
• (SELECT count(*) FROM (... LIMIT N)) till «Hur mycket ska jag ta i gram»: DISTINCT + LIMIT

SELECT DISTINCT pk FROM X LIMIT $1

Den naiva utvecklaren kan uppriktigt tro att frågutvärderingen kommer att stoppas,

så fort vi hittar $1 första bästa olika värden I framtiden kan det så småningom fungera tack vare en ny nod.

Index Skip Scan , vars implementering just nu utarbetas, men än så länge – nej.För nu kommer först

alla poster att hämtas , unikgörs, och först från dem kommer så många som begärs att returneras. Särskilt sorgligt kan det bli om vi ville ha något som, men det finns hundratusentals poster i tabellen… $1 = 4För att inte gråta i onödan, låt oss använda en rekursiv fråga

«DISTINCT för fattiga» från PostgreSQL Wiki PostgreSQL Antipatterns: beräkning av villkor i SQL:

Källa: habr.com