🥇PostgreSQL Antipatterns: "Yuav tsum tsuas muaj ib sab laug xwb!"

Hauv SQL, koj piav qhia "dab tsi" koj xav ua tiav, tsis yog "yuav ua li cas" nws yuav tsum raug tua. Yog li ntawd, qhov teeb meem ntawm kev tsim SQL queries nyob rau hauv cov style ntawm "raws li nws tau hnov yog li cas nws yog sau" yuav siv nws qhov chaw ntawm hwm, nrog rau cov yam ntxwv ntawm kev suav cov xwm txheej hauv SQL.

Niaj hnub no, siv cov piv txwv yooj yim heev, cia peb pom dab tsi qhov no tuaj yeem ua rau hauv cov ntsiab lus ntawm kev siv GROUP/DISTINCT и LIMIT nrog lawv.

Tam sim no, yog tias koj sau rau hauv qhov kev thov "Ua ntej txuas cov paib no, thiab tom qab ntawd muab pov tseg tag nrho cov duplicates, yuav tsum muaj ib leeg xwb luam rau txhua tus yuam sij " - qhov no yog raws nraim li cas nws yuav ua haujlwm, txawm tias qhov kev sib txuas tsis xav tau txhua.

Thiab qee zaum koj muaj hmoo thiab nws "tsuas yog ua haujlwm", qee zaum nws muaj kev cuam tshuam tsis zoo rau kev ua haujlwm, thiab qee zaum nws muab cov teebmeem uas tsis tau xav txog los ntawm tus tsim tawm qhov kev xav.

Zoo, tej zaum tsis zoo nkauj heev, tab sis ...

"Cov khub niam txiv qab zib": JOIN + DISTINCT

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

Nws yuav pom tseeb tias lawv xav tau dab tsi xaiv cov ntaub ntawv X uas muaj cov ntaub ntawv hauv Y uas cuam tshuam nrog cov xwm txheej ua tiav. Sau ntawv thov ntawm JOIN - tau txais qee qhov txiaj ntsig pk ob peb zaug (yuav muaj pes tsawg qhov tsim nyog nkag tau tshwm sim hauv Y). Yuav tshem tawm li cas? Muaj tseeb tiag DISTINCT!

Nws yog qhov tshwj xeeb tshaj yog "kev txaus siab" thaum rau txhua X-cov ntaub ntawv muaj ntau pua qhov cuam tshuam nrog Y-cov ntaub ntawv, thiab tom qab ntawd cov duplicates tau raug tshem tawm ...

Yuav kho li cas? Pib nrog, paub tias qhov teeb meem tuaj yeem hloov kho rau "Xaiv cov ntaub ntawv X rau qhov twg hauv Y muaj tsawg kawg yog ib qho cuam tshuam nrog qhov ua tiav" - Tom qab tag nrho, peb tsis xav tau dab tsi los ntawm Y-cov ntaub ntawv nws tus kheej.

Nested EXISTS

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

Ib txhia versions ntawm PostgreSQL nkag siab tias nyob rau hauv EXISTS nws yog txaus mus nrhiav tau tus thawj nkag uas tuaj, cov laus tsis ua. Yog li kuv nyiam ib txwm qhia LIMIT 1 sab hauv EXISTS.

LATERAL JOIN

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;

Tib qhov kev xaiv tso cai, yog tias tsim nyog, kom rov qab qee cov ntaub ntawv tam sim ntawd los ntawm qhov pom muaj feem cuam tshuam Y-cov ntaub ntawv. Ib qho kev xaiv zoo sib xws tau tham hauv kab lus "PostgreSQL Antipatterns: cov ntaub ntawv tsis tshua muaj yuav mus txog nruab nrab ntawm Kev Koom Tes".

“Vim li cas thiaj them ntau dua”: DISTINCT [ON] + LIMIT 1

Ib qho txiaj ntsig ntxiv ntawm cov lus nug hloov pauv yog lub peev xwm los txwv kev tshawb nrhiav cov ntaub ntawv yooj yim yog tias tsuas yog ib lossis ob peb ntawm lawv xav tau, xws li hauv cov ntaub ntawv hauv qab no:

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

Tam sim no peb nyeem qhov kev thov thiab sim nkag siab tias DBMS tau thov ua dab tsi:

txuas cov paib
ib leeg by X.pk
los ntawm qhov seem nkag, xaiv ib qho

Yog li koj tau txais dab tsi? "Tsuas yog ib qho nkag" los ntawm cov neeg tshwj xeeb - thiab yog tias peb muab qhov no ib qho ntawm cov tsis sib xws, cov txiaj ntsig puas yuav hloov pauv li cas? ... "Thiab yog tias tsis muaj qhov sib txawv, vim li cas them nyiaj ntau dua?"

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

Thiab raws nraim tib lub ntsiab lus nrog GROUP BY + LIMIT 1.

"Kuv tsuas yog yuav tsum nug": implicit GROUP + LIMIT

Tej yam zoo sib xws tshwm sim ntawm qhov sib txawv cov tshev uas tsis yog emptiness kos npe lossis CTEs raws li qhov kev thov nce mus ntxiv:

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

Aggregate functions (count/min/max/sum/...) tau ua tiav tiav ntawm tag nrho cov txheej txheem, txawm tias tsis muaj cov lus qhia meej GROUP BY. Tsuas yog nrog LIMIT lawv tsis yog phooj ywg heev.

Tus tsim tawm tuaj yeem xav "yog tias muaj cov ntaub ntawv nyob ntawd, ces kuv xav tau tsis pub ntau tshaj LIMIT". Tab sis tsis txhob ua li ntawd! Vim rau lub hauv paus nws yog:

suav qhov lawv xav tau raws li tag nrho cov ntaub ntawv
muab ntau kab raws li lawv thov

Nyob ntawm lub hom phiaj xwm txheej, nws tsim nyog los ua ib qho kev hloov pauv hauv qab no:

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

"Yuav ua li cas dai hauv grams": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Tus neeg tsim tawm tsis paub yuav ua siab ncaj ntseeg tias qhov kev thov yuav tsum tsis txhob ua. sai li sai tau thaum peb pom $1 ntawm thawj qhov sib txawv ntawm qhov tseem ceeb uas tuaj hla.

Qee lub sij hawm yav tom ntej qhov no yuav thiab yuav ua haujlwm ua tsaug rau cov node tshiab Index Hla Scan, qhov kev siv uas tam sim no tab tom ua haujlwm, tab sis tseem tsis tau.

Tam sim no ua ntej tag nrho cov ntaub ntawv yuav raug muab rov qab, yog tshwj xeeb, thiab tsuas yog los ntawm lawv yuav cov nyiaj thov rov qab. Tshwj xeeb tshaj yog tu siab yog tias peb xav tau ib yam dab tsi zoo li $ 1 = 4, thiab muaj ntau pua txhiab cov ntaub ntawv nyob rau hauv lub rooj ...

Yuav kom tsis txhob tu siab rau qhov tsis muaj nuj nqis, cia peb siv cov lus nug recursive "DISTINCT yog rau cov neeg pluag" los ntawm PostgreSQL Wiki:

Tau qhov twg los: www.hab.com

PostgreSQL Antipatterns: "Yuav tsum muaj ib qho xwb!"