Antimodelet e PostgreSQL: BASHKIMET dhe OR të dëmshme

Kujdes nga operacionet që sjellin buferë...
Duke përdorur një pyetje të vogël si shembull, le të shohim disa qasje universale për optimizimin e pyetjeve në PostgreSQL. Nëse i përdorni apo jo, varet nga ju, por ia vlen të dini për to.

Në disa versione të mëvonshme të PG situata mund të ndryshojë ndërsa planifikuesi bëhet më i zgjuar, por për 9.4/9.6 duket afërsisht i njëjtë, si në shembujt këtu.

Le të marrim një kërkesë shumë reale:

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;

rreth emrave të tabelave dhe fushaveEmrat "rusë" të fushave dhe tabelave mund të trajtohen ndryshe, por kjo është çështje shije. Sepse këtu në Tensor nuk ka zhvillues të huaj dhe PostgreSQL na lejon të japim emra edhe në hieroglife, nëse ata të mbyllura në thonjëza, atëherë preferojmë t'i emërtojmë objektet në mënyrë të qartë dhe të qartë në mënyrë që të mos ketë mospërputhje.
Le të shohim planin që rezulton:

[shikoni në shpjegojnë.tensor.ru]

144 ms dhe pothuajse 53 mijë buferë - domethënë më shumë se 400 MB të dhëna! Dhe ne do të jemi me fat nëse të gjithë ata janë në cache deri në momentin e kërkesës sonë, përndryshe do të zgjasë shumë herë më shumë kur lexohet nga disku.

Algoritmi është më i rëndësishmi!

Për të optimizuar disi çdo kërkesë, së pari duhet të kuptoni se çfarë duhet të bëjë.
Le ta lëmë zhvillimin e vetë strukturës së bazës së të dhënave jashtë qëllimit të këtij artikulli tani për tani, dhe të pajtohemi që mundemi relativisht "lirë" rishkruaj kërkesën dhe/ose rrotullojmë mbi bazën disa nga gjërat që na duhen Indekset.

Pra kërkesa:
— kontrollon ekzistencën e të paktën disa dokumenteve
- ne gjendjen qe na duhet dhe te nje lloji te caktuar
- ku autori ose interpretuesi është punonjësi që na nevojitet

BASHKOHUNI + LIMITI 1

Shumë shpesh është më e lehtë për një zhvillues të shkruajë një pyetje ku fillimisht bashkohen një numër i madh tabelash dhe më pas mbetet vetëm një rekord nga i gjithë ky grup. Por më e lehtë për zhvilluesin nuk do të thotë më efikase për bazën e të dhënave.
Në rastin tonë kishte vetëm 3 tabela - dhe cili është efekti...

Le të heqim qafe fillimisht lidhjen me tabelën "Lloji i dokumentit" dhe në të njëjtën kohë t'i themi bazës së të dhënave se rekordi ynë i llojit është unik (ne e dimë këtë, por planifikuesi ende nuk e ka idenë):

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

Po, nëse tabela/CTE përbëhet nga një fushë e vetme e një regjistrimi të vetëm, atëherë në PG mund të shkruani edhe kështu, në vend të

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

Vlerësimi dembel në pyetjet e PostgreSQL

BitmapOr vs UNION

Në disa raste, Bitmap Heap Scan do të na kushtojë shumë - për shembull, në situatën tonë, kur mjaft rekorde plotësojnë kushtin e kërkuar. E morëm sepse Kushti OSE u kthye në BitmapOr- funksionimi në plan.
Le të kthehemi te problemi origjinal - duhet të gjejmë një rekord përkatës kushdo nga kushtet - domethënë, nuk ka nevojë të kërkoni të gjitha regjistrimet 59K në të dyja kushtet. Ekziston një mënyrë për të zgjidhur një kusht, dhe shkoni te e dyta vetëm kur nuk u gjet asgjë në të parën. Dizajni i mëposhtëm do të na ndihmojë:

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

KUFI "I jashtëm" 1 siguron që kërkimi të përfundojë kur të gjendet rekordi i parë. Dhe nëse gjendet tashmë në bllokun e parë, blloku i dytë nuk do të ekzekutohet (nuk ekzekutohet kurrë në lidhje me).

“Fshehja e kushteve të vështira në rast”

Ekziston një moment jashtëzakonisht i papërshtatshëm në pyetjen origjinale - kontrollimi i statusit ndaj tabelës përkatëse "DocumentExtension". Pavarësisht nga vërtetësia e kushteve të tjera në shprehje (për shembull, d. "Fshirë" NUK ËSHTË E VËRTETË), kjo lidhje ekzekutohet gjithmonë dhe “kushton burime”. Pak a shumë prej tyre do të shpenzohen - varet nga madhësia e kësaj tabele.
Por ju mund ta modifikoni pyetjen në mënyrë që kërkimi për një regjistrim të lidhur të ndodhë vetëm kur është vërtet e nevojshme:

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

Një herë nga tabela e lidhur tek ne asnjë nga fushat nuk është e nevojshme për rezultatin, atëherë kemi mundësinë ta kthejmë JOIN në një kusht në një nënpyetje.
Le t'i lëmë fushat e indeksuara "jashtë kllapave CASE", shtojmë kushte të thjeshta nga regjistrimi në bllokun WHEN - dhe tani pyetja "e rëndë" ekzekutohet vetëm kur kalon te THEN.

Mbiemri im është "Total"

Ne mbledhim pyetjen që rezulton me të gjithë mekanikën e përshkruar më sipër:

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;

Përshtatja [në] indekset

Një sy i stërvitur vuri re se kushtet e indeksuara në nënblloqet UNION janë paksa të ndryshme - kjo për shkak se ne tashmë kemi indekse të përshtatshme në tryezë. Dhe nëse nuk do të ekzistonin, do të ia vlente të krijoheshin: Dokumenti (Personi 3, Lloji i Dokumentit) и Dokument (lloji i dokumentit, punonjës).
rreth renditjes së fushave në kushtet ROWNga këndvështrimi i planifikuesit, natyrisht, ju mund të shkruani (A, B) = (constA, constB)Dhe (B, A) = (constB, constA). Por gjatë regjistrimit sipas renditjes së fushave në indeks, një kërkesë e tillë është thjesht më e përshtatshme për të korrigjuar gabimet më vonë.
Çfarë është në plan?

[shikoni në shpjegojnë.tensor.ru]

Fatkeqësisht, ne patëm fat dhe në bllokun e parë UNION nuk u gjet asgjë, kështu që i dyti u ekzekutua ende. Por edhe kështu - vetëm 0.037ms dhe 11 bufera!
Ne kemi përshpejtuar kërkesën dhe kemi reduktuar pompimin e të dhënave në memorie disa mijëra herë, duke përdorur teknika mjaft të thjeshta - një rezultat i mirë me pak copy-paste. 🙂

Burimi: www.habr.com