PostgreSQL Antipatterns: "Daar moet net een wees!"

In SQL beskryf jy "wat" jy wil kry, nie "hoe" dit gedoen moet word nie. Daarom neem die probleem van die ontwikkeling van SQL-navrae in die styl van "soos dit gehoor word is hoe dit geskryf word" sy ereplek, saam met eienaardighede van toestand-evaluering in SQL.

Vandag, deur uiters eenvoudige voorbeelde te gebruik, kom ons kyk waartoe dit kan lei in die konteks van gebruik GROUP/DISTINCT и LIMIT saam met hulle.

Dit is as jy in die versoek geskryf het “Koppel eers hierdie tablette aan, en gooi dan al die duplikate uit, daar moet net een wees instansie vir elke sleutel" - dit is presies hoe dit sal werk, selfs al was die verbinding glad nie nodig nie.

En soms is jy gelukkig en dit "werk net", soms het dit 'n onaangename uitwerking op prestasie, en soms gee dit effekte wat absoluut onverwags uit die ontwikkelaar se oogpunt is.

Wel, miskien nie so skouspelagtig nie, maar ...

"Sweet couple": JOIN + DISTINCT

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

Hoe sou dit duidelik wees wat hulle wou hê kies sulke rekords X, waarvoor daar in Y geassosieer word met die vervulde voorwaarde. Het 'n versoek ingedien via JOIN - 'n paar waardes van pk verskeie kere ontvang (presies hoeveel geskikte rekords in Y geblyk het te wees). Hoe om te verwyder? Sekerlik DISTINCT!

Dit is veral "aangenaam" as daar vir elke X-rekord 'n paar honderd verwante Y-rekords is, en dan word duplikate heldhaftig verwyder ...

Hoe om reg te maak? Om mee te begin, besef dat die taak aangepas kan word na "kies daardie rekords X waarvoor daar TEN MINSTE EEN in Y geassosieer word met die voorwaarde wat nagekom word" - ons het immers niks van die Y-rekord self nodig nie.

Geneste BESTAAN

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

Sommige weergawes van PostgreSQL verstaan ​​dat in EXISTS dit genoeg is om die eerste rekord te vind wat teëkom, ouer nie. Daarom verkies ek om altyd aan te dui LIMIT 1 binne 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;

Dieselfde opsie laat toe om, indien nodig, onmiddellik sommige data van die gevind geassosieerde Y-rekord op dieselfde tyd terug te stuur. 'N Soortgelyke opsie word in die artikel bespreek "PostgreSQL-teenpatrone: seldsame rekord sal die middel van JOIN bereik".

"Waarom meer betaal": DISTINCT [AAN] + LIMIET 1

'n Bykomende voordeel van sulke navraagtransformasies is die vermoë om die opsomming van rekords maklik te beperk indien slegs een / min van hulle benodig word, soos in die volgende geval:

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

Nou lees ons die versoek en probeer verstaan ​​wat die DBMS veronderstel is om te doen:

• ons verbind die plate
• uniek deur X.pk
• kies een van die oorblywende rekords

So wat het jy gekry? "Iemand een rekord" van die unieke - en as jy hierdie een van die nie-unieke neem, sal die resultaat op een of ander manier verander? .. "En as daar geen verskil is nie, hoekom meer betaal?"

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

En presies dieselfde tema met GROUP BY + LIMIT 1.

"Ek moet net vra": implisiete GROEP + LIMIT

Soortgelyke dinge kom in verskillende voor nie-leegheidskontroles etikette of CTE's soos die versoek vorder:

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

Aggregaat funksies (count/min/max/sum/...) word suksesvol op die hele stel uitgevoer, selfs sonder om uitdruklik te spesifiseer GROUP BY. Net hier met LIMIT hulle is nie baie vriendelik nie.

Die ontwikkelaar kan dink "Nou, as daar rekords daar is, dan het ek nie meer as LIMIT nodig nie". Maar jy hoef nie! Want vir die basis is dit:

• tel wat hulle wil hê op alle rekords
• gee soveel reëls as wat hulle vra

Afhangende van die teikentoestande, is dit gepas om een ​​van die volgende vervangings te maak:

• (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 om te hang in gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

'n Naïewe ontwikkelaar mag opreg glo dat die uitvoering van 'n versoek sal stop, sodra ons die eerste $1 verskillende waardes vind wat teëkom.

Iewers in die toekoms kan en sal dit werk danksy 'n nuwe nodus Indeks Slaan Skandering oor, waarvan die implementering tans uitgewerk word, maar nog nie.

Tot dusver eerste alle rekords sal herwin word, is uniek, en net soveel van hulle as wat versoek word, sal teruggestuur word. Dit is veral hartseer as ons so iets wou hê $ 1 = 4, en daar is honderde duisende rekords in die tabel ...

Om nie tevergeefs hartseer te wees nie, sal ons 'n rekursiewe navraag gebruik "DISTINCT for the Poor" van PostgreSQL Wiki:

Bron: will.com