PostgreSQL Antipatterns: "Ho tlameha ho be le e le 'ngoe feela!"

Ho SQL, u hlalosa "seo" seo u batlang ho se fihlela, eseng "joang" se lokelang ho etsoa. Ka hona, bothata ba ho hlahisa lipotso tsa SQL ka mokhoa oa "joalokaha ho utluoa ke kamoo ho ngotsoeng kateng" ho nka sebaka sa eona sa tlhompho, hammoho le likarolo tsa ho bala maemo a SQL.

Kajeno, re sebelisa mehlala e bonolo haholo, a re boneng hore na sena se ka lebisa ho eng molemong oa tšebeliso GROUP/DISTINCT и LIMIT le bona.

Jwale, haeba o ngotse kopong “Qala ka ho hokahanya matšoao ana, ebe u lahlela likopi tsohle, ho lokela ho sala a le mong feela kopitsa senotlolo ka seng" - ena ke hantle kamoo e tla sebetsa kateng, le haeba ho ne ho sa hlokahale ho hang.

'Me ka linako tse ling u na le mahlohonolo' me "e sebetsa feela", ka linako tse ling e na le phello e sa thabiseng ts'ebetsong, 'me ka linako tse ling e fana ka liphello tse sa lebelloang ka ho feletseng ho tloha ponong ea moqapi.

Che, mohlomong ha se ntho e makatsang, empa ...

"Banyalani ba monate": JOIN + DIISTINCT

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

Ho ne ho tla hlaka hore na ba batla eng khetha litlaleho X tseo ho tsona ho nang le litlaleho ho Y tse amanang le boemo bo phethahetseng. O ngotse kopo ka JOIN - e na le litekanyetso tsa pk makhetlo a 'maloa (hantle-ntle hore na ho na le litlhaloso tse kae tse loketseng tse hlahang ho Y). Joang ho tlosa? Ka sebele DISTINCT!

Hoa "thabisa" haholo ha rekoto e 'ngoe le e' ngoe ea X ho na le makholo a 'maloa a amanang le lirekoto tsa Y, ebe tse kopitsoang li tlosoa ...

Joang ho lokisa? Ho qala, hlokomela hore bothata bo ka fetoloa hore e be "khetha lirekoto tsa X tseo ho tsona ho Y ho nang le BOHLOKOA e le 'ngoe e amanang le boemo bo phethahetseng" - ka mor'a tsohle, ha re hloke letho ho tsoa ho Y-rekoto ka boeona.

Nested EXISTS

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

Liphetolelo tse ling tsa PostgreSQL lia utloisisa hore ho EXISTS ho lekane ho fumana ho kena ha pele ho hlahang, batho ba baholo ha ba tsebe. Ka hona, ke khetha ho bontša kamehla LIMIT 1 ka hare EXISTS.

KAMOHELO KAMOHELO

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;

Khetho e tšoanang e lumella, haeba ho hlokahala, ho khutlisa hang-hang data e itseng ho tsoa ho Y-rekoto e amanang le eona. Khetho e tšoanang e tšohloa sehloohong sena "PostgreSQL Antipatterns: rekoto e sa tloaelehang e tla fihla bohareng ba JOIN".

"Hobaneng ho lefa ho feta": DISTINCT [ON] + LIMIT 1

Molemo o mong oa liphetoho tse joalo ke bokhoni ba ho fokotsa ho batlisisa litlaleho haeba ho hlokahala e le 'ngoe kapa tse' maloa feela, joalo ka tabeng e latelang:

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

Joale re bala kopo mme re leka ho utloisisa seo DBMS e reriloeng ho se etsa:

• ho kopanya matshwao
• e ikhethang ka X.pk
• ho tsoa ho tse setseng, khetha e le 'ngoe

Joale u fumane eng? "Keno e le 'ngoe feela" ho tsoa ho tse ikhethang - 'me haeba re nka ena ea tse sa ikhethang, na sephetho se tla fetoha ka tsela e itseng?.. "'Me haeba ho se na phapang, ke hobane'ng ha u lefa ho feta?"

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

Le sehlooho se tšoanang hantle le GROUP BY + LIMIT 1.

"Ke tlameha ho botsa feela": GROUP + LIMIT

Lintho tse tšoanang li etsahala ka litsela tse fapaneng licheke tse se nang letho matšoao kapa CTE ha kopo e ntse e tsoela pele:

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

Kopanya mesebetsi (count/min/max/sum/...) li etsoa ka katleho sehlopheng sohle, ntle le litaelo tse hlakileng GROUP BY. Feela le LIMIT ha ba na botsoalle haholo.

Moqapi a ka nahana "Haeba ho na le lirekoto moo, ha ke hloke ho feta LIMIT". Empa u se ke ua etsa joalo! Hobane bakeng sa motheo ke:

• bala seo ba se batlang ho latela litlaleho tsohle
• fana ka mela e mengata kamoo ba e kopang kateng

Ho latela maemo a reriloeng, ho loketse ho etsa e 'ngoe ea tse latelang:

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

"Ke bokae ho fanyeha ka ligrama": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Moqapi ea sa tsebeng letho a ka lumela ka tieo hore kopo e tla emisa ho sebetsa. hang ha re fumana $ 1 ea litekanyetso tsa pele tse fapaneng tse hlahang.

Ka nako e 'ngoe nakong e tlang sena se ka sebetsa' me se tla sebetsa ka lebaka la node e ncha Index Skip Scan, ts'ebetsong eo hajoale e ntseng e rarolloa, empa ha e so fihle.

Bakeng sa hona joale pele lirekoto tsohle li tla khutlisoa, li ikhetha, ’me chelete e kōptjoang e tla khutlisoa ho bona feela. Ho bohloko haholo haeba re ne re batla ntho e joalo $ 1 = 4, 'me ho na le lirekoto tse likete tse makholo tafoleng...

E le hore re se ke ra hlonama lefeela, ha re sebeliseng potso e iphetang "DISTINCT ke ea mafutsana" ho tsoa ho PostgreSQL Wiki:

Source: www.habr.com