PostgreSQL Antimönster: "Det måste bara finnas ett!"

I SQL beskriver du "vad" du vill uppnå, inte "hur" det ska utföras. Därför tar problemet med att utveckla SQL-frågor i stil med "som det hörs är hur det skrivs" dess hedersplats, tillsammans med funktioner för beräkning av villkor i SQL.

Idag, med extremt enkla exempel, låt oss se vad detta kan leda till i användningssammanhang GROUP/DISTINCT и LIMIT med dem.

Nu, om du skrev i begäran "anslut först dessa skyltar och kasta sedan ut alla dubbletter, det ska bara finnas en kvar kopia för varje nyckel" – det är precis så det kommer att fungera, även om anslutningen inte behövdes alls.

Och ibland har du tur och det "bara fungerar", ibland har det en obehaglig effekt på prestanda, och ibland ger det effekter som är helt oväntade från utvecklarens synvinkel.

Tja, kanske inte så spektakulärt, men...

"Sweet couple": JOIN + DISTINCT

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

Det skulle vara tydligt vad de ville välj poster X för vilka det finns poster i Y som är relaterade till det uppfyllda villkoret. Skrev en förfrågan via JOIN — fick några pk-värden flera gånger (exakt hur många passande poster dök upp i Y). Hur tar man bort? Säkert DISTINCT!

Det är särskilt "glädjande" när det för varje X-skiva finns flera hundra relaterade Y-poster, och sedan dubbletter tas bort heroiskt...

Hur man fixar? Till att börja med, inse att problemet kan ändras till "välj poster X för vilka det i Y finns MINST EN associerad med det uppfyllda villkoret" – Vi behöver trots allt inget från själva Y-skivan.

Kapslad FINNS

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 i EXISTS räcker det med att hitta den första posten som kommer upp, äldre gör det inte. Därför föredrar jag att alltid ange LIMIT 1 inom EXISTS.

SIDOFOGA

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;

Samma alternativ gör det möjligt att omedelbart returnera vissa data från den hittade associerade Y-posten. Ett liknande alternativ diskuteras i artikeln "PostgreSQL Antimönster: en sällsynt post kommer att nå mitten av en JOIN".

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

En ytterligare fördel med sådana frågetransformationer är möjligheten att enkelt begränsa sökningen efter poster om bara en eller ett fåtal 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 begäran och försöker förstå vad DBMS föreslås göra:

• kopplar ihop skyltarna
• unik av X.pk
• från de återstående posterna väljer du en

Så vad fick du? "Bara en post" från de unika - och om vi tar den här av de icke-unika, kommer resultatet att förändras på något sätt? .. "Och om det inte är någon skillnad, varför betala mer?"

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

Och exakt samma ämne med GROUP BY + LIMIT 1.

"Jag måste bara fråga": implicit GROUP + LIMIT

Liknande saker inträffar på olika icke-tomhetskontroller tecken eller CTE när begäran fortskrider:

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

Aggregera funktioner (count/min/max/sum/...) exekveras framgångsrikt på hela uppsättningen, även utan explicita instruktioner GROUP BY. Bara med LIMIT de är inte särskilt vänliga.

Utvecklaren kan tänka "om det finns poster där behöver jag inte mer än LIMIT". Men gör inte det! För för basen är det:

• räkna vad de vill enligt alla uppgifter
• ge så många rader som de ber om

Beroende på målförhållandena är det lämpligt att göra ett av följande byten:

• (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))

"Hur mycket att hänga i gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

En naiv utvecklare kan uppriktigt tro att begäran kommer att sluta utföras. så snart vi hittar $1 av de första olika värdena som stöter på.

Någon gång i framtiden kan och kommer detta att fungera tack vare en ny nod Index Skip Scan, vars genomförande håller på att utarbetas, men inte ännu.

För nu först alla poster kommer att hämtas, är unika, och endast från dem kommer det begärda beloppet att returneras. Det är särskilt tråkigt om vi vill ha något liknande $ 1 = 4, och det finns hundratusentals poster i tabellen...

För att inte vara ledsna förgäves, låt oss använda en rekursiv fråga "DISTINCT är för de fattiga" från PostgreSQL Wiki:

Källa: will.com