PostgreSQL Antipatterns: "Devas esti nur unu!"

En SQL, vi priskribas "kion" vi volas atingi, ne "kiel" ĝi devus esti efektivigita. Tial, la problemo disvolvi SQL-demandojn en la stilo de "kiel ĝi estas aŭdata kiel ĝi estas skribita" prenas sian lokon de honoro, kune kun trajtoj de kalkulado de kondiĉoj en SQL.

Hodiaŭ, uzante ege simplajn ekzemplojn, ni vidu, al kio tio povas konduki en la kunteksto de uzo GROUP/DISTINCT и LIMIT kun ili.

Nun, se vi skribis en la peto "unue konektu ĉi tiujn signojn, kaj poste forĵetu ĉiujn duplikatojn, restu nur unu kopiu por ĉiu ŝlosilo" - ĝuste tiel ĝi funkcios, eĉ se la konekto tute ne estis bezonata.

Kaj foje vi estas bonŝanca kaj ĝi "nur funkcias", foje ĝi havas malagrablan efikon al agado, kaj foje ĝi donas efikojn tute neatenditaj el la vidpunkto de la programisto.

Nu, eble ne tiel sensacia, sed...

“Dolĉa paro”: ALIGI + DISTINGU

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

Estus klare, kion ili volis elektu registrojn X por kiuj estas registroj en Y kiuj rilatas al la plenumita kondiĉo. Skribis peton per JOIN — ricevis kelkajn pk-valorojn plurajn fojojn (ĝuste kiom da taŭgaj enskriboj aperis en Y). Kiel forigi? Certe DISTINCT!

Estas precipe "ĝojige", kiam por ĉiu X-rekordo estas kelkcent rilataj Y-rekordoj, kaj tiam la duplikatoj estas heroe forigitaj...

Kiel ripari? Komence, rimarku, ke la problemo povas esti modifita al "elektu rekordojn X por kiuj en Y estas ALMENAU UNU asociita kun la plenumita kondiĉo" - ja ni bezonas nenion el la Y-rekordo mem.

Nestita EKZISTAS

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

Iuj versioj de PostgreSQL komprenas, ke en EXISTS sufiĉas trovi la unuan eniron kiu aperas, pli malnovaj ne. Tial mi preferas ĉiam indiki LIMIT 1 interne EXISTS.

LATERALA ALIGO

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;

La sama opcio permesas, se necese, tuj resendi iujn datumojn de la trovita asociita Y-rekordo. Simila opcio estas diskutita en la artikolo "PostgreSQL Antipatterns: malofta rekordo atingos la mezon de JOIN".

"Kial pagi pli": DISTINCT [ON] + LIMIGO 1

Plia avantaĝo de tiaj demandtransformoj estas la kapablo facile limigi la serĉon de rekordoj se nur unu aŭ kelkaj el ili estas necesaj, kiel en la sekva kazo:

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

Nun ni legas la peton kaj provas kompreni, kion la DBMS estas proponita fari:

• kunligante la signojn
• unika de X.pk
• el la ceteraj enskriboj, elektu unu

Do kion vi ricevis? "Nur unu eniro" el la unikaj — kaj se ni prenos ĉi tiun el la neunikaj, ĉu la rezulto iel ŝanĝiĝos?.. “Kaj se ne estas diferenco, kial pagi pli?”

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

Kaj ĝuste la sama temo kun GROUP BY + LIMIT 1.

“Mi nur devas demandi”: implicita GRUPO + LIMIGO

Similaj aferoj okazas ĉe malsamaj ne-malplenaj kontroloj signoj aŭ CTE-oj dum la peto progresas:

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

Agregaj funkcioj (count/min/max/sum/...) estas sukcese efektivigitaj sur la tuta aro, eĉ sen eksplicitaj instrukcioj GROUP BY. Nur kun LIMIT ili ne estas tre amikaj.

La programisto povas pensi "se estas rekordoj tie, tiam mi bezonas ne pli ol LIMIT". Sed ne faru tion! Ĉar por la bazo ĝi estas:

• kalkulu, kion ili volas laŭ ĉiuj registroj
• donu tiom da linioj kiom ili petas

Depende de la celkondiĉoj, taŭgas fari unu el la sekvaj anstataŭaĵoj:

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

"Kiom pezi en gramoj": DISTINTA + LIMIGO

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naiva programisto povas sincere kredi, ke la peto ĉesos ekzekuti. tuj kiam ni trovos $1 el la unuaj malsamaj valoroj, kiuj venas.

Iam estonte tio povas kaj funkcios danke al nova nodo Indeksa Skip Scan, kies efektivigo estas nuntempe prilaborata, sed ankoraŭ ne.

Nuntempe unue ĉiuj rekordoj estos prenitaj, estas unikaj, kaj nur de ili la petita kvanto estos redonita. Estas precipe malĝoje, se ni volis ion similan $ 1 = 4, kaj estas centoj da miloj da rekordoj en la tabelo...

Por ne malĝoji vane, ni uzu rekursieman demandon "DISTINTO estas por malriĉuloj" de PostgreSQL-Vikio:

fonto: www.habr.com