PostgreSQL Antipatterns: "On oltava vain yksi!"

SQL:ssä kuvailet "mitä" haluat saavuttaa, et "miten" se pitäisi suorittaa. Siksi ongelma kehittää SQL-kyselyitä tyyliin "kuten se on kuultu, niin se kirjoitetaan" ottaa kunniapaikan, samoin kuin SQL:n ehtojen laskentaominaisuudet.

Katsotaan tänään erittäin yksinkertaisten esimerkkien avulla, mihin tämä voi johtaa käytön yhteydessä GROUP/DISTINCT и LIMIT heidän kanssaan.

Jos nyt kirjoitit pyyntöön "Yhdistä ensin nämä merkit ja sitten heitä pois kaikki kaksoiskappaleet, pitäisi olla vain yksi jäljellä kopioi jokaiselle avaimelle" - Juuri näin se toimii, vaikka yhteyttä ei tarvittaisi ollenkaan.

Ja joskus olet onnekas ja se "vain toimii", joskus sillä on epämiellyttävä vaikutus suorituskykyyn, ja joskus se antaa tehosteita, jotka ovat täysin odottamattomia kehittäjän näkökulmasta.

No, ei ehkä niin näyttävää, mutta...

"Suloinen pari": LIITTY + EROTTA

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

Olisi selvää, mitä he halusivat valitse tietueet X, joille Y:ssä on tietueita, jotka liittyvät täyttyneeseen ehtoon. Kirjoitti pyynnön kautta JOIN - sai joitakin pk-arvoja useita kertoja (tarkasti kuinka monta sopivaa merkintää ilmestyi Y:ssä). Kuinka poistaa? Varmasti DISTINCT!

Erityisen "ilahduttavaa" on, kun jokaisessa X-tietueessa on useita satoja toisiinsa liittyviä Y-tietueita, ja sitten kaksoiskappaleet poistetaan sankarillisesti...

Kuinka korjata? Aluksi ymmärrä, että ongelmaa voidaan muuttaa "valitse tietueet X, joille Y:ssä on VÄHINTÄÄN YKSI, joka liittyy täyttyneeseen ehtoon" - emmehän itse Y-tietueesta tarvitse mitään.

Sisäkkäinen OLEMASSA

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

Jotkut PostgreSQL-versiot ymmärtävät, että EXISTSissä riittää löytää ensimmäinen esiin tuleva merkintä, vanhemmat eivät. Siksi haluan aina ilmoittaa LIMIT 1 sisällä EXISTS.

SIVULIITTYMINEN

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;

Sama vaihtoehto mahdollistaa tarvittaessa välittömästi osan tiedon palauttamisesta löydetystä liittyvästä Y-tietueesta. Samanlaista vaihtoehtoa käsitellään artikkelissa "PostgreSQL-antipatterns: harvinainen tietue saavuttaa JOINin puolivälin".

"Miksi maksaa enemmän": EROTUS [ON] + RAJA 1

Tällaisten kyselymuunnosten lisäetu on kyky rajoittaa helposti tietueiden hakua, jos niistä tarvitaan vain yksi tai muutama, kuten seuraavassa tapauksessa:

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

Nyt luemme pyynnön ja yritämme ymmärtää, mitä DBMS:ää ehdotetaan tekemään:

• merkkien yhdistäminen
• ainutlaatuinen X.pk
• valitse yksi jäljellä olevista tiedoista

Joten mitä sait? "Vain yksi sisääntulo" ainutlaatuisista - ja jos otamme tämän ei-ainutlaatuisista, muuttuuko tulos jotenkin?.. "Ja jos ei ole eroa, miksi maksaa enemmän?"

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

Ja täsmälleen sama aihe GROUP BY + LIMIT 1.

"Minun täytyy vain kysyä": implisiittinen RYHMÄ + RAJA

Samanlaisia ​​asioita tapahtuu eri tavalla ei-tyhjyystarkastukset allekirjoittaa tai CTE:t pyynnön edetessä:

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

Aggregaattifunktiot (count/min/max/sum/...) suoritetaan onnistuneesti koko joukossa, jopa ilman nimenomaisia ​​ohjeita GROUP BY. Vain kanssa LIMIT he eivät ole kovin ystävällisiä.

Kehittäjä osaa ajatella "Jos siellä on tietueita, en tarvitse enempää kuin LIMIT". Mutta älä tee sitä! Koska pohjalle se on:

• laskea mitä haluavat kaikkien tietueiden mukaan
• anna niin monta riviä kuin he pyytävät

Kohdeolosuhteista riippuen on tarkoituksenmukaista tehdä jokin seuraavista vaihdoista:

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

"Kuinka paljon ripustaa grammoina": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naiivi kehittäjä saattaa vilpittömästi uskoa, että pyyntö lakkaa toimimasta. heti kun löydämme 1 dollarin ensimmäisistä eri arvoista.

Joskus tulevaisuudessa tämä saattaa toimia ja toimii uuden solmun ansiosta Hakemisto Skip Scan, jonka toteutusta valmistellaan parhaillaan, mutta ei vielä.

Toistaiseksi ensin kaikki tietueet haetaan, ovat ainutlaatuisia, ja vain heiltä palautetaan pyydetty summa. Se on erityisen surullista, jos halusimme jotain sellaista $ 1 = 4, ja taulukossa on satoja tuhansia tietueita...

Jotta ei olisi turhaa surullista, käytetään rekursiivista kyselyä "DISTINCT on köyhille" PostgreSQL Wikistä:

Lähde: will.com