ProHoster > Blog > Adminisztráció > PostgreSQL antipatterns: "Csak egynek kell lennie!"

PostgreSQL antipatterns: "Csak egynek kell lennie!"

Az SQL-ben azt írja le, hogy „mit” szeretne elérni, nem pedig azt, hogy „hogyan” kell végrehajtani. Ezért az SQL-lekérdezések „ahogyan hallják, így írják” stílusban történő fejlesztésének problémája kerül megtisztelő helyére. a feltételek számításának jellemzői SQL-ben.

Ma, rendkívül egyszerű példákon keresztül, nézzük meg, mire vezethet ez a használat kontextusában GROUP/DISTINCT и LIMIT velük.

Most, ha írtál a kérésben „Először kösd össze ezeket a jeleket, majd dobd ki az összes másolatot, csak egy maradjon másolat minden kulcshoz" - pontosan így fog működni, még akkor is, ha a csatlakozásra egyáltalán nem volt szükség.

És néha szerencséd van, és „csak működik”, néha kellemetlen hatással van a teljesítményre, néha pedig a fejlesztői szemszögből teljesen váratlan hatásokat ad.

PostgreSQL antipatterns: "Csak egynek kell lennie!"
Nos, talán nem annyira látványos, de...

„Édes pár”: CSATLAKOZÁS + ELTÁVOLÍTÁS

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

Világos lenne, mit akarnak válassza ki azokat az X rekordokat, amelyekhez Y-ban vannak olyan rekordok, amelyek a teljesített feltételhez kapcsolódnak. Kérést írt a következőn keresztül JOIN - többször kapott néhány pk értéket (pontosan hány megfelelő bejegyzés jelent meg Y-ban). Hogyan kell eltávolítani? Biztosan DISTINCT!

Különösen „örömteli”, amikor minden X-rekordhoz több száz kapcsolódó Y-rekord tartozik, majd a duplikátumokat hősiesen eltávolítják...

PostgreSQL antipatterns: "Csak egynek kell lennie!"

Hogyan lehet javítani? Kezdésként vegye észre, hogy a probléma módosítható „válassza ki azokat az X rekordokat, amelyekhez Y-ban LEGALÁBB EGY van társítva a teljesített feltételhez” - elvégre magából az Y-rekordból nem kell semmi.

Beágyazott LÉTEZIK

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

A PostgreSQL egyes verziói megértik, hogy az EXISTS-ben elegendő megtalálni az első bejegyzést, a régebbiek nem. Ezért inkább mindig jelzem LIMIT 1 belső EXISTS.

OLDALOS CSATLAKOZÁS

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;

Ugyanez az opció lehetővé teszi, hogy szükség esetén azonnal visszaadjon néhány adatot a talált társított Y-rekordból. Hasonló lehetőséget tárgyal a cikk "PostgreSQL antipatterns: egy ritka rekord eléri a JOIN közepét".

„Miért fizetne többet”: KÜLÖNBÖZŐ [BE] + 1. KORLÁT

Az ilyen lekérdezéstranszformációk további előnye, hogy könnyen korlátozható a rekordok keresése, ha csak egy vagy néhány szükséges belőlük, mint a következő esetben:

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

Most elolvassuk a kérést, és megpróbáljuk megérteni, mit javasolnak a DBMS-nek:

  • a jelek összekötése
  • egyedi az X.pk
  • a fennmaradó bejegyzések közül válasszon ki egyet

Szóval mit kaptál? "Csak egy belépés" az egyediek közül - és ha ezt vesszük a nem egyediek közül, akkor változni fog valahogy az eredmény?.. "És ha nincs különbség, akkor minek fizessenek többet?"

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

És pontosan ugyanaz a téma GROUP BY + LIMIT 1.

„Csak meg kell kérdeznem”: implicit GROUP + LIMIT

Hasonló dolgok máshol fordulnak elő nem-üresség ellenőrzések aláírja vagy CTE-k a kérés előrehaladtával:

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

Összesített függvények (count/min/max/sum/...) sikeresen végrehajtódnak a teljes készleten, még kifejezett utasítások nélkül is GROUP BY. Csak vele LIMIT nem túl barátságosak.

A fejlesztő gondolkodhat „Ha vannak ott rekordok, akkor nem kell több, mint LIMIT”. De ezt ne tedd! Mert az alapnak ez:

  • azt számolják, amit akarnak minden feljegyzés szerint
  • annyi sort adjanak, amennyit kérnek

A célfeltételektől függően célszerű a következő helyettesítések egyikét végrehajtani:

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

„Mennyi akasztható grammban”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Egy naiv fejlesztő őszintén azt hiheti, hogy a kérés végrehajtása leáll. amint találunk 1 dollárt az első talált különböző értékekből.

Valamikor a jövőben ez működhet és működni fog egy új csomópontnak köszönhetően Index Skip Scan, melynek megvalósítása jelenleg kidolgozás alatt van, de még nem.

Egyelőre először minden rekord le lesz kérve, egyediek, és csak tőlük jár vissza a kért összeg. Különösen szomorú, ha valami ilyesmit akartunk $ 1 = 4, és több százezer rekord van a táblázatban...

Hogy ne legyünk hiába szomorúak, használjunk rekurzív lekérdezést A „DISTINCT a szegényeknek szól” a PostgreSQL Wikiből:

PostgreSQL antipatterns: "Csak egynek kell lennie!"

Forrás: will.com

Hozzászólás