🥇PostgreSQL-antipatterns: haitalliset JOIN- ja OR:t

Varo toimintoja, jotka tuovat puskureita...
Tarkastellaanpa esimerkkinä pientä kyselyä yleisiä lähestymistapoja kyselyiden optimointiin PostgreSQL:ssä. Voit itse päättää, käytätkö niitä vai et, mutta niistä kannattaa tietää.

Joissakin myöhemmissä PG:n versioissa tilanne saattaa muuttua, kun ajastin tulee älykkäämmäksi, mutta versioille 9.4/9.6 se näyttää suunnilleen samalta kuin näissä esimerkeissä.

Otetaanpa hyvin todellinen pyyntö:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

taulukoiden ja kenttien nimistäKenttien ja taulukoiden "venäläisiä" nimiä voidaan käsitellä eri tavalla, mutta tämä on makuasia. Koska täällä Tensorissa ei ole ulkomaisia kehittäjiä, ja PostgreSQL antaa meille mahdollisuuden antaa nimiä jopa hieroglyfeinä, jos ne lainausmerkkien sisällä, niin haluamme nimetä objektit yksiselitteisesti ja selkeästi, jotta eroja ei ole.
Katsotaanpa tuloksena olevaa suunnitelmaa:

[katso selittää.tensor.ru]

144ms ja lähes 53K puskurit - eli yli 400 Mt dataa! Ja olemme onnekkaita, jos ne kaikki ovat välimuistissa pyyntöömme mennessä, muuten levyltä luettaessa kestää monta kertaa kauemmin.

Algoritmi on tärkein!

Jotta voisit jotenkin optimoida minkä tahansa pyynnön, sinun on ensin ymmärrettävä, mitä sen pitäisi tehdä.
Jätetään itse tietokantarakenteen kehittäminen toistaiseksi tämän artikkelin ulkopuolelle ja sovitaan, että voimme suhteellisen "halvalla" kirjoita pyyntö uudelleen ja/tai rullata alustalle joitain asioita, joita tarvitsemme Indexes.

Joten pyyntö:
— tarkistaa ainakin jonkin asiakirjan olemassaolon
- sellaisessa kunnossa kuin tarvitsemme ja tietyn tyyppisiä
- jossa tekijä tai esittäjä on tarvitsemamme työntekijä

LIITTY + RAJA 1

Melko usein kehittäjän on helpompi kirjoittaa kysely, jossa ensin liitetään suuri määrä taulukoita, ja sitten tästä koko joukosta on jäljellä vain yksi tietue. Mutta helpompi kehittäjälle ei tarkoita tehokkaampaa tietokantaa.
Meidän tapauksessamme oli vain 3 pöytää - ja mikä on vaikutus...

Puretaan ensin yhteys "Dokumenttityyppi"-taulukkoon ja kerrotaan samalla tietokannalle, että tyyppitietueemme on ainutlaatuinen (Tiedämme tämän, mutta aikatauluttaja ei tiedä vielä):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Kyllä, jos taulukko/CTE koostuu yhden tietueen yhdestä kentästä, PG:ssä voit kirjoittaa jopa näin, sen sijaan, että

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Laiska arviointi PostgreSQL-kyselyissä

BitmapOr vs UNION

Joissain tapauksissa Bitmap Heap Scan tulee meille kalliiksi - esimerkiksi meidän tilanteessamme, kun aika monet tietueet täyttävät vaaditun ehdon. Saimme sen, koska OR-ehto muuttui BitmapOr:ksi- toiminta suunnitelmassa.
Palataan alkuperäiseen ongelmaan - meidän on löydettävä vastaava tietue mille tahansa ehdoista - eli kaikkia 59K tietueita ei tarvitse etsiä molemmissa olosuhteissa. On olemassa tapa ratkaista yksi ehto, ja Siirry toiseen vasta, kun ensimmäisestä ei löytynyt mitään. Seuraava suunnittelu auttaa meitä:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

"Ulkoinen" RAJA 1 varmistaa, että haku päättyy, kun ensimmäinen tietue löytyy. Ja jos se löytyy jo ensimmäisestä lohkosta, toista lohkoa ei suoriteta (ei koskaan teloitettu suhteen).

"Vaikeiden olosuhteiden piilottaminen CASE:n alla"

Alkuperäisessä kyselyssä on äärimmäisen epämukava hetki - tilan tarkistaminen vastaavasta taulukosta "DocumentExtension". Riippumatta lausekkeen muiden ehtojen totuudesta (esim. d."Poistettu" EI OLE TOSI), tämä yhteys suoritetaan aina ja "maksaa resursseja". Enemmän tai vähemmän niistä käytetään - riippuu tämän pöydän koosta.
Voit kuitenkin muokata kyselyä niin, että liittyvää tietuetta etsitään vain silloin, kun se on todella tarpeen:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Kerran linkitetystä taulukosta meille mitään kenttiä ei tarvita tulokseen, niin meillä on mahdollisuus muuttaa JOIN alikyselyn ehdoksi.
Jätetään indeksoidut kentät "CASE-sulkujen ulkopuolelle", lisätään yksinkertaiset ehdot tietueesta WHEN-lohkoon - ja nyt "raskas" kysely suoritetaan vain siirrettäessä THEN:iin.

Sukunimeni on "Total"

Keräämme tuloksena olevan kyselyn kaikilla yllä kuvatuilla mekaniikkailla:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Säätö [to] indekseihin

Koulutettu silmä huomasi, että UNION-alilohkojen indeksoidut ehdot ovat hieman erilaisia - tämä johtuu siitä, että meillä on jo sopivat indeksit taulukossa. Ja jos niitä ei olisi olemassa, kannattaa luoda: Asiakirja (henkilö3, asiakirjatyyppi) и Asiakirja (asiakirjan tyyppi, työntekijä).
kenttien järjestyksestä ROW-ehdoissaSuunnittelijan näkökulmasta tietysti voit kirjoittaa (A, B) = (constA, constB)Ja (B, A) = (constB, constA). Mutta äänitettäessä hakemiston kenttien järjestyksessä, tällainen pyyntö on yksinkertaisesti helpompi korjata myöhemmin.
Mitä suunnitelmassa on?

[katso selittää.tensor.ru]

Valitettavasti meillä oli epäonnea ja ensimmäisestä UNION-lohkosta ei löytynyt mitään, joten toinen teloitettiin silti. Mutta silti - vain 0.037 ms ja 11 puskuria!
Olemme nopeuttaneet pyyntöä ja vähentäneet tietojen pumppaamista muistiin useita tuhansia kertoja, käyttäen melko yksinkertaisia tekniikoita - hyvä tulos pienellä copy-pastella. 🙂

Lähde: will.com

PostgreSQL:n antipatterns: Haitalliset JOIN- ja OR:t