PostgreSQL Antipatterns: "Bakarrik egon behar da!"

SQL-n, "zer" lortu nahi duzun deskribatzen duzu, ez "nola" exekutatu behar den. Hori dela eta, SQL kontsultak garatzeko "bezala entzuten da nola idatzita dagoen" estiloan hartzen du ohore lekua, SQLn baldintzak kalkulatzeko ezaugarriak.

Gaur egun, adibide oso sinpleak erabiliz, ikus dezagun zer ekar dezakeen horrek erabileraren testuinguruan GROUP/DISTINCT и LIMIT Haiekin.

Orain, eskaeran idatzi baduzu "Lehenengo konektatu seinale hauek, eta gero bota bikoiztu guztiak, bakarra geratu behar da kopia gako bakoitzeko" - horrela funtzionatuko du, nahiz eta konexioa batere behar ez izan.

Eta batzuetan zortea izaten duzu eta "funtzionatzen du", batzuetan eragin desatsegina du errendimenduan, eta batzuetan garatzailearen ikuspuntutik guztiz ustekabekoak diren efektuak ematen ditu.

Tira, agian ez da hain ikusgarria, baina...

“Bikote gozoa”: BATU + DESBERDINTZEN

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

Argi geratuko zen zer nahi zuten hautatu X erregistroak, betetako baldintzarekin erlazionatuta dauden Y-n erregistroak dituztenak. Eskaera bat idatzi du bidez JOIN — pk balio batzuk lortu ditu hainbat aldiz (zehazki zenbat sarrera egoki agertu ziren Y-n). Nola kendu? Zalantzarik gabe DISTINCT!

Batez ere "pozgarria" da X-erregistro bakoitzeko ehunka erlazionatutako Y-erregistro daudenean, eta gero bikoiztuak heroikoki kentzen direnean...

Nola konpondu? Hasteko, konturatu arazoa alda daitekeela "hautatu X erregistroak, zeinen Y-n GUTXIENEZ BAT dagoen betetako baldintzarekin lotuta" - azken finean, ez dugu ezer behar Y-erregistrotik bertatik.

Habiaratua EGITEN DA

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

PostgreSQLren bertsio batzuek ulertzen dute EXISTS-en nahikoa dela agertzen den lehenengo sarrera aurkitzea, zaharragoek ez. Horregatik nahiago dut beti adierazi LIMIT 1 barruan EXISTS.

ALBOKO LOTURA

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;

Aukera berak ahalbidetzen du, beharrezkoa bada, aurkitutako Y-erregistroko datu batzuk berehala itzultzeko. Artikuluan antzeko aukera bat eztabaidatzen da "PostgreSQL Antipatterns: erregistro arraroa JOIN baten erdira iritsiko da".

"Zergatik ordaindu gehiago": DISTINCT [AKTIBATUA] + MUGA 1

Kontsulten eraldaketa horien abantaila gehigarri bat erregistroen bilaketa erraz mugatzeko gaitasuna da, horietako bat edo batzuk bakarrik behar badira, kasu honetan bezala:

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

Orain eskaera irakurri eta DBMSa zer egiteko proposatzen den ulertzen saiatzen gara:

• seinaleak lotuz
• bakarra X.pk
• gainerako sarreretatik, hautatu bat

Beraz, zer lortu duzu? "Sarrera bakarra" bakarretik -eta hau hartzen badugu ez-bakarra, emaitza aldatuko al da nolabait?... "Eta alderik ez badago, zergatik ordaindu gehiago?"

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

Eta zehazki gai bera GROUP BY + LIMIT 1.

“Galdetu besterik ez dut egin behar”: inplizitua TALDEA + MUGA

Antzeko gauzak desberdinetan gertatzen dira ez hutsunearen egiaztapenak seinaleak edo CTEak eskaerak aurrera egin ahala:

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

Funtzio agregatuak (count/min/max/sum/...) arrakastaz exekutatzen dira multzo osoan, argibide espliziturik gabe ere GROUP BY. Bakarrik LIMIT ez dira oso jatorrak.

Garatzaileak pentsa dezake "Han erregistroak badaude, orduan LIMIT baino ez dut behar". Baina ez egin hori! Oinarriarentzat hauxe baita:

• zenbatu nahi dutena erregistro guztien arabera
• eskatu adina lerro eman

Xede-baldintzen arabera, egokia da ordezkapen hauetako bat egitea:

• (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))

“Zenbat gramotan zintzilikatu”: DESBERDINTASUNA + MUGA

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Garatzaile inozo batek benetan sinetsi dezake eskaera exekutatzen geldituko dela. Topatzen diren lehen balio desberdinetatik $1 aurkitu bezain laster.

Etorkizunean, nodo berri bati esker funtzionatuko du Indizea saltatu eskaneatzea, zeinaren ezarpena lantzen ari dira gaur egun, baina oraindik ez.

Oraingoz lehenengo erregistro guztiak berreskuratuko dira, bakarrak dira, eta horietatik bakarrik itzuliko da eskatutako zenbatekoa. Batez ere tristea da antzeko zerbait nahi bagenu $ 1 = 4, eta ehunka mila erregistro daude taulan...

Alferrik triste ez egoteko, erabil dezagun kontsulta errekurtsibo bat "DISTINCT pobreentzako da" PostgreSQL Wiki-tik:

Iturria: www.habr.com