ProHoster > blog > administratie > PostgreSQL Antipatterns: "Er mag er maar één zijn!"

PostgreSQL Antipatterns: "Er mag er maar één zijn!"

In SQL beschrijft u ‘wat’ u wilt bereiken, niet ‘hoe’ het moet worden uitgevoerd. Daarom neemt het probleem van het ontwikkelen van SQL-query’s in de stijl van “zoals het wordt gehoord is hoe het is geschreven” een ereplaats in, samen met kenmerken van het berekenen van voorwaarden in SQL.

Laten we vandaag, aan de hand van uiterst eenvoudige voorbeelden, kijken waar dit in de gebruikscontext toe kan leiden GROUP/DISTINCT и LIMIT met hen.

Nu, als je in het verzoek hebt geschreven “Verbind eerst deze borden en gooi dan alle duplicaten weg, er zou er nog maar één over moeten zijn kopie voor elke sleutel" - dit is precies hoe het zal werken, zelfs als de verbinding helemaal niet nodig was.

En soms heb je geluk en werkt het “gewoon”, soms heeft het een onaangenaam effect op de prestaties, en soms geeft het effecten die volkomen onverwacht zijn vanuit het oogpunt van de ontwikkelaar.

PostgreSQL Antipatterns: "Er mag er maar één zijn!"
Nou ja, misschien niet zo spectaculair, maar...

“Lief koppel”: DOE MEE + DISTINCT

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

Het zou duidelijk zijn wat ze wilden selecteer records X waarvoor er records in Y zijn die gerelateerd zijn aan de vervulde voorwaarde. Heb een verzoek geschreven via JOIN — meerdere keren een aantal pk-waarden gekregen (precies hoeveel geschikte vermeldingen er in Y verschenen). Hoe te verwijderen? Zeker DISTINCT!

Het is vooral “bevredigend” als er voor elk X-record enkele honderden gerelateerde Y-records zijn, en de duplicaten vervolgens heldhaftig worden verwijderd...

PostgreSQL Antipatterns: "Er mag er maar één zijn!"

Hoe te repareren? Realiseer je om te beginnen dat het probleem kan worden aangepast “selecteer records X waarvoor in Y TEN MINSTE ÉÉN is gekoppeld aan de vervulde voorwaarde” - we hebben immers niets nodig van het Y-record zelf.

Genest BESTAAT

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

Sommige versies van PostgreSQL begrijpen dat het in EXISTS voldoende is om het eerste item dat verschijnt te vinden, terwijl oudere versies dat niet doen. Daarom geef ik er de voorkeur aan om altijd aan te geven LIMIT 1 внутри EXISTS.

LATERALE VERBINDING

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;

Dezelfde optie maakt het mogelijk om, indien nodig, onmiddellijk enkele gegevens uit het gevonden bijbehorende Y-record terug te geven. Een soortgelijke optie wordt in het artikel besproken "PostgreSQL Antipatterns: een zeldzaam record bereikt het midden van een JOIN".

“Waarom meer betalen”: DISTINCT [AAN] + LIMIT 1

Een bijkomend voordeel van dergelijke querytransformaties is de mogelijkheid om het zoeken naar records eenvoudig te beperken als er slechts één of enkele nodig zijn, zoals in het volgende geval:

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

Nu lezen we het verzoek en proberen we te begrijpen wat het DBMS moet doen:

  • het verbinden van de borden
  • uniek door X.pk
  • selecteer er één uit de overige vermeldingen

Dus wat heb je gekregen? "Slechts één inzending" van de unieke - en als we deze van de niet-unieke nemen, zal het resultaat dan op de een of andere manier veranderen? .. "En als er geen verschil is, waarom zou je dan meer betalen?"

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

En precies hetzelfde onderwerp GROUP BY + LIMIT 1.

“Ik moet het gewoon vragen”: impliciete GROEP + LIMIET

Soortgelijke dingen gebeuren bij verschillende niet-leegheidscontroles tekenen of CTE's naarmate het verzoek vordert:

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

Geaggregeerde functies (count/min/max/sum/...) worden met succes uitgevoerd op de hele set, zelfs zonder expliciete instructies GROUP BY. Alleen met LIMIT ze zijn niet erg vriendelijk.

De ontwikkelaar kan nadenken “als daar records zijn, dan heb ik niet meer dan LIMIT nodig”. Maar doe dat niet! Want voor de basis is het:

  • tellen wat ze willen volgens alle gegevens
  • geef zoveel regels als ze vragen

Afhankelijk van de doelomstandigheden is het passend om een ​​van de volgende vervangingen uit te voeren:

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

“Hoeveel te wegen in grammen”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Een naïeve ontwikkelaar kan oprecht geloven dat de uitvoering van het verzoek zal stoppen. zodra we $1 vinden van de eerste verschillende waarden die we tegenkomen.

Ergens in de toekomst kan en zal dit werken dankzij een nieuw knooppunt Index overslaan scannen, waarvan de implementatie momenteel wordt uitgewerkt, maar nog niet.

Voor nu eerst alle records worden opgehaald, zijn uniek en alleen daarvan wordt het gevraagde bedrag geretourneerd. Het is vooral triest als we zoiets wilden $ 1 = 4, en er zijn honderdduizenden records in de tabel...

Om niet tevergeefs verdrietig te zijn, gebruiken we een recursieve zoekopdracht "DISTINCT is voor de armen" van PostgreSQL Wiki:

PostgreSQL Antipatterns: "Er mag er maar één zijn!"

Bron: www.habr.com

Voeg een reactie