PostgreSQL antipatterns: "Ir jābūt tikai vienam!"

SQL jūs aprakstāt "ko" vēlaties iegūt, nevis "kā" tas būtu jādara. Tāpēc goda vietu ieņem SQL vaicājumu izstrādes problēma stilā "kā tas ir dzirdēts, kā tas tiek rakstīts" nosacījumu novērtēšanas īpatnības SQL.

Šodien, izmantojot ārkārtīgi vienkāršus piemērus, redzēsim, pie kā tas var novest lietošanas kontekstā GROUP/DISTINCT и LIMIT ar viņiem.

Tas ir, ja jūs rakstījāt pieprasījumā "Vispirms savienojiet šīs planšetdatorus un pēc tam izmetiet visus dublikātus, jābūt tikai vienam piemērs katrai atslēgai" - tieši tā tas darbosies, pat ja savienojums nemaz nebija vajadzīgs.

Un dažreiz jums paveicas, un tas "vienkārši darbojas", dažreiz tas nepatīkami ietekmē veiktspēju, un dažreiz tas rada efektus, kas ir absolūti negaidīti no izstrādātāja viedokļa.

Nu, varbūt ne tik iespaidīgi, bet…

"Saldais pāris": PIEVIENOTIES + ATŠĶIRĪTIES

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

Kā būtu skaidrs, ko viņi vēlas atlasiet tādus ierakstus X, kuriem Y ir saistīti ar izpildīto nosacījumu. Iesniedza pieprasījumu, izmantojot JOIN - vairākas reizes saņēma dažas pk vērtības (precīzi, cik piemērotu ierakstu izrādījās Y). Kā noņemt? Noteikti DISTINCT!

Tas ir īpaši “patīkami”, ja katram X ierakstam ir vairāki simti saistītu Y ierakstu, un pēc tam varonīgi tiek noņemti dublikāti ...

Kā salabot? Vispirms saprotiet, ka uzdevumu var mainīt uz "atlasiet tos ierakstus X, kuriem Y ir VISMAZ VIENS, kas saistīts ar nosacījumu, kas tiek izpildīts" - galu galā mums nekas nav vajadzīgs no paša Y ieraksta.

Ligzdotas PASTĀV

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

Dažas PostgreSQL versijas saprot, ka programmā EXISTS pietiek atrast pirmo ierakstu, kas uzrodas, vecākās to nedara. Tāpēc es dodu priekšroku vienmēr norādīt LIMIT 1 laikā EXISTS.

SĀNU PIEVIENOJUMI

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;

Tā pati opcija ļauj, ja nepieciešams, nekavējoties atgriezt dažus datus no atrastā saistītā Y ieraksta vienlaikus. Līdzīga iespēja ir apskatīta rakstā "PostgreSQL antipatterns: reti ieraksti sasniegs JOIN vidu".

“Kāpēc maksāt vairāk”: ATŠĶIRĪBA [IESLĒGTA] + 1. LIMITĒJUMS

Šādu vaicājumu transformāciju papildu priekšrocība ir iespēja viegli ierobežot ierakstu uzskaiti, ja nepieciešams tikai viens/daži no tiem, kā tas ir šādā gadījumā:

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

Tagad mēs lasām pieprasījumu un mēģinām saprast, kas DBVS ir jādara:

• mēs savienojam plāksnes
• unikāls X.pk
• izvēlieties vienu no atlikušajiem ierakstiem

Tātad, ko jūs saņēmāt? "Kāds ieraksts" no unikālajiem - un, ja paņem šo no neunikālajiem, vai rezultāts kaut kā mainīsies? .. "Un, ja nav atšķirības, kāpēc maksāt vairāk?"

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

Un tieši tā pati tēma ar GROUP BY + LIMIT 1.

"Man tikai jājautā": netieša GROUP + LIMIT

Līdzīgas lietas notiek dažādos tukšuma pārbaudes etiķetes vai CTE, pieprasījumam turpinoties:

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

Apkopotās funkcijas (count/min/max/sum/...) tiek veiksmīgi izpildīti visā komplektā, pat ja tas nav skaidri norādīts GROUP BY. Tikai šeit ar LIMIT viņi nav īpaši draudzīgi.

Izstrādātājs var domāt “Tagad, ja tur ir ieraksti, man nevajag vairāk par LIMIT”. Bet nevajag! Jo bāzei tas ir:

• skaita, ko viņi vēlas visos ierakstos
• dot tik daudz rindu, cik viņi lūdz

Atkarībā no mērķa apstākļiem ir lietderīgi veikt kādu no šīm aizstāšanām:

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

"Cik daudz pakārt gramos": ATŠĶIRĪBA + IEROBEŽOJUMS

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivs izstrādātājs var patiesi uzskatīt, ka pieprasījuma izpilde tiks pārtraukta, tiklīdz mēs atrodam pirmās 1 ASV dolāra dažādās vērtības, kas rodas.

Kaut kad nākotnē tas var darboties un darbosies, pateicoties jaunam mezglam Indekss Skip Scan, kuras ieviešana šobrīd tiek izstrādāta, bet vēl ne.

Pagaidām vispirms visi ieraksti tiks izgūti, ir unikālas, un tiks atgrieztas tikai tik daudz, cik pieprasīts. Tas ir īpaši skumji, ja mēs gribējām kaut ko līdzīgu $ 1 = 4, un tabulā ir simtiem tūkstošu ierakstu ...

Lai nebūtu veltīgi skumji, izmantosim rekursīvu vaicājumu "DISTINCT for the Poor" no PostgreSQL Wiki:

Avots: www.habr.com