PostgreSQL andmynstur: Skaðleg JOIN og OR

Varist aðgerðir sem koma með biðminni...
Með því að nota litla fyrirspurn sem dæmi skulum við skoða nokkrar alhliða aðferðir til að fínstilla fyrirspurnir í PostgreSQL. Hvort þú notar þau eða ekki er undir þér komið, en það er þess virði að vita um þau.

Í sumum síðari útgáfum af PG gæti ástandið breyst eftir því sem tímaáætlunarmaðurinn verður snjallari, en fyrir 9.4/9.6 lítur það svipað út eins og í dæmunum hér.

Við skulum taka mjög raunverulega beiðni:

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;

um töflu- og reitnöfnHægt er að meðhöndla „rússnesku“ nöfn reita og taflna á annan hátt, en þetta er smekksatriði. Vegna þess að hér á Tensor það eru engir erlendir forritarar, og PostgreSQL gerir okkur kleift að gefa nöfn jafnvel í híeróglyfum, ef þeir með gæsalappa, þá kjósum við að nefna hluti ótvírætt og skýrt þannig að það sé ekkert misræmi.
Við skulum líta á áætlunina sem myndast:

[horfðu á explain.tensor.ru]

144ms og næstum 53K biðminni - það er meira en 400MB af gögnum! Og við verðum heppin ef þau eru öll í skyndiminni þegar beiðni okkar er send, annars mun það taka margfalt lengri tíma þegar lesið er af diski.

Reikniritið er mikilvægast!

Til þess að hámarka hvaða beiðni sem er, verður þú fyrst að skilja hvað hún ætti að gera.
Við skulum skilja þróun gagnagrunnsbyggingarinnar sjálfrar utan gildissviðs þessarar greinar í bili og sammála um að við getum tiltölulega „ódýrt“ endurskrifa beiðnina og/eða rúlla á botninn eitthvað af því sem við þurfum vísitölur.

Svo beiðnin:
— athugar hvort til sé að minnsta kosti einhver skjöl
- í því ástandi sem við þurfum og af ákveðinni gerð
- þar sem höfundur eða flytjandi er sá starfsmaður sem við þurfum

JOIN + TAKMARKARI 1

Oft er auðveldara fyrir þróunaraðila að skrifa fyrirspurn þar sem fjöldi borða er fyrst sameinaður, og þá er aðeins ein skrá eftir af öllu settinu. En auðveldara fyrir verktaki þýðir ekki skilvirkara fyrir gagnagrunninn.
Í okkar tilviki voru aðeins 3 borð - og hver er áhrifin...

Losum okkur fyrst við tenginguna við "Document Type" töfluna og segjum um leið gagnagrunninum að Tegundarskrá okkar er einstök (við vitum þetta, en tímaáætlunarmaðurinn hefur ekki hugmynd ennþá):

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

Já, ef taflan/CTE samanstendur af einum reit af einni skrá, þá geturðu jafnvel skrifað svona í PG í stað þess að

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

Latur mat í PostgreSQL fyrirspurnum

BitmapOr vs UNION

Í sumum tilfellum mun Bitmap Heap Scan kosta okkur mikið - til dæmis í okkar aðstæðum, þegar töluvert margar skrár uppfylla tilskilin skilyrði. Við fengum það vegna þess OR ástand breytt í BitmapOr- rekstur í áætlun.
Snúum okkur aftur að upprunalega vandamálinu - við þurfum að finna skrá sem samsvarar hver sem er frá skilyrðunum - það er engin þörf á að leita að öllum 59K færslum við báðar aðstæður. Það er leið til að vinna úr einu skilyrði, og farðu aðeins í annað þegar ekkert fannst í því fyrsta. Eftirfarandi hönnun mun hjálpa okkur:

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

„Ytri“ LIMIT 1 tryggir að leitinni lýkur þegar fyrsta skráin finnst. Og ef það er þegar að finna í fyrstu blokkinni verður seinni blokkin ekki keyrð (aldrei tekinn af lífi að því er varðar).

„Að fela erfiðar aðstæður undir CASE“

Það er ákaflega óþægilegt augnablik í upprunalegu fyrirspurninni - að athuga stöðuna gegn tengdu töflunni „DocumentExtension“. Burtséð frá sannleika annarra skilyrða í tjáningu (td. d. „Eydd“ ER EKKI SATT), þessi tenging er alltaf framkvæmd og „kostar fjármagn“. Meira eða minna af þeim verður varið - fer eftir stærð þessa borðs.
En þú getur breytt fyrirspurninni þannig að leitin að tengdri færslu á sér stað aðeins þegar það er raunverulega nauðsynlegt:

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

Einu sinni frá tengdu töflunni til okkar engan af reitunum þarf fyrir niðurstöðuna, þá höfum við tækifæri til að breyta JOIN í skilyrði fyrir undirfyrirspurn.
Skiljum verðtryggðu reitina „utan CASE sviga“, bætum einföldum skilyrðum úr skránni við WHEN blokkina - og nú er „þunga“ fyrirspurnin aðeins keyrð þegar farið er í THEN.

Eftirnafnið mitt er "Total"

Við söfnum fyrirspurninni sem myndast með öllum vélbúnaði sem lýst er hér að ofan:

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;

Aðlögun [að] vísitölum

Þjálfað auga tók eftir því að verðtryggðu aðstæðurnar í UNION undirblokkunum eru aðeins öðruvísi - þetta er vegna þess að við höfum nú þegar viðeigandi vísitölur á borðinu. Og ef þeir væru ekki til væri það þess virði að búa til: Skjal (Persóna3, DocumentType) и Skjal (DocumentType, Starfsmaður).
um röð reita í ROW-skilyrðumFrá sjónarhóli skipuleggjanda er auðvitað hægt að skrifa (A, B) = (constA, constB)Og (B, A) = (constB, constA). En við upptöku í röð reitanna í vísitölunni, slíka beiðni er einfaldlega þægilegra að kemba síðar.
Hvað er í áætluninni?

[horfðu á explain.tensor.ru]

Því miður vorum við óheppnir og ekkert fannst í fyrstu UNION blokkinni, svo sú seinni var enn tekinn af lífi. En þó svo - aðeins 0.037ms og 11 biðminni!
Við höfum flýtt fyrir beiðninni og dregið úr gagnadælingu í minni nokkur þúsund sinnum, með tiltölulega einföldum aðferðum - góður árangur með smá copy-paste. 🙂

Heimild: www.habr.com