🥇PostgreSQL Antipatterns: zovulaza JOIN ndi ORs

Chenjerani ndi machitidwe omwe amabweretsa ma buffers...
Pogwiritsa ntchito funso laling'ono monga chitsanzo, tiyeni tiwone njira zina zapadziko lonse lapansi zokwaniritsira mafunso mu PostgreSQL. Kaya muzigwiritsa ntchito kapena ayi zili ndi inu, koma ndikofunikira kudziwa za iwo.

M'matembenuzidwe ena otsatila a PG zinthu zimatha kusintha pamene wokonza ndondomekoyo akukhala wanzeru, koma kwa 9.4 / 9.6 ikuwoneka mofanana, monga mu zitsanzo apa.

Tiyeni titenge pempho lenileni:

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;

za tebulo ndi mayina a mindaMayina a "Russian" a minda ndi matebulo akhoza kuchitidwa mosiyana, koma iyi ndi nkhani ya kukoma. Chifukwa ndi kuno ku Tensor palibe otukula akunja, ndipo PostgreSQL imatilola kuti tipatse mayina ngakhale m'ma hieroglyphs, ngati atero ophatikizidwa m'mawu, ndiye timakonda kutchula zinthu momveka bwino komanso momveka bwino kuti pasakhale kusiyana.
Tiyeni tiwone zotsatira zake:

[onani pa explain.tensor.ru]

144ms ndi pafupifupi 53K mabafa - ndiye kuti, zopitilira 400MB za data! Ndipo tidzakhala ndi mwayi ngati onse ali mu cache pofika nthawi ya pempho lathu, apo ayi zidzatenga nthawi zambiri powerenga kuchokera pa disk.

Algorithm ndiyofunikira kwambiri!

Kuti mukwaniritse pempho lililonse, muyenera kumvetsetsa kaye zomwe muyenera kuchita.
Tiyeni tisiye chitukuko cha kasamalidwe kazomwe zili kunja kwa nkhaniyi pakadali pano, ndikuvomereza kuti titha "zotsika mtengo" lembaninso pempho ndi/kapena kugubuduza m'munsi zina mwa zinthu zomwe timafunikira zizindikiro.

Choncho pempho:
- imayang'ana kukhalapo kwa zolemba zina
- mu chikhalidwe chomwe tikufuna komanso cha mtundu wina wake
- kumene wolemba kapena wochita ndi wogwira ntchito yomwe tikufuna

LOWANI + MALIRE 1

Nthawi zambiri zimakhala zosavuta kuti wopanga mapulogalamu alembe funso pomwe magome ambiri amalumikizidwa koyamba, kenako ndi mbiri imodzi yokha yomwe imatsalira pagulu lonseli. Koma kuphweka kwa wopanga sikutanthauza kuchita bwino pa database.
Kwa ife panali matebulo atatu okha - ndipo zotsatira zake ndi zotani ...

Tiyeni tiyambe tichotse kulumikizana ndi tebulo la "Document Type", ndipo nthawi yomweyo auzeni database kuti. mbiri yathu yamtundu ndi yapadera (tikudziwa izi, koma wokonza mapulani sakudziwabe):

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

Inde, ngati tebulo/CTE ili ndi gawo limodzi la mbiri imodzi, ndiye kuti mu PG mutha kulemba motere, m'malo molemba.

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

Kuwunika kwaulesi pamafunso a PostgreSQL

BitmapOr vs UNION

Nthawi zina, Bitmap Heap Scan ingatiwonongere ndalama zambiri - mwachitsanzo, momwe tilili, zolemba zambiri zikakwaniritsa zofunikira. Tinazipeza chifukwa OR chikhalidwe chasinthidwa kukhala BitmapOr- ntchito mu dongosolo.
Tiyeni tibwerere ku vuto loyambirira - tiyenera kupeza mbiri yofanana aliyense kuchokera pamikhalidwe - ndiko kuti, palibe chifukwa chofufuzira zolemba zonse za 59K pansi pamikhalidwe yonseyi. Pali njira yothetsera vuto limodzi, ndi pita kwa wachiwiri kokha pamene palibe chinapezeka koyamba. Mapangidwe otsatirawa atithandiza:

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

"Zakunja" LIMIT 1 zimatsimikizira kuti kusaka kutha pomwe mbiri yoyamba ikupezeka. Ndipo ngati zapezeka kale mu chipika choyamba, chipika chachiwiri sichidzachitidwa (sanaphedwe mu ulemu).

"Kubisala zovuta pansi pa CASE"

Pali nthawi yovuta kwambiri pafunso loyambirira - kuyang'ana momwe zilili ndi tebulo lofananira "DocumentExtension". Mosasamala kanthu za chowonadi cha zikhalidwe zina m'mawuwo (mwachitsanzo, d.“Kufufutidwa” SIZOONA), kugwirizana uku kumachitidwa nthawi zonse ndipo "mtengo wamtengo wapatali". Zambiri kapena zochepa za iwo zidzagwiritsidwa ntchito - zimatengera kukula kwa tebulo ili.
Koma mutha kusintha funsolo kuti kusaka mbiri yofananira kuchitike pokhapokha pakufunika:

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

Kamodzi kuchokera pa tebulo lolumikizidwa kwa ife palibe gawo lililonse lomwe limafunikira pazotsatira, ndiye tili ndi mwayi wosintha JOIN kukhala chikhalidwe pa subquery.
Tiyeni tisiye minda yolondolera "kunja kwa mabulaketi a CASE", onjezani zinthu zosavuta kuchokera pa cholembera kupita ku block WHEN - ndipo tsopano funso "lolemera" limachitidwa pokhapokha mutadutsa ku THEN.

Dzina langa ndi "Total"

Timasonkhanitsa funso lotsatira ndi makina onse omwe afotokozedwa pamwambapa:

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;

Kusintha [ku] ma index

Diso lophunzitsidwa lidawona kuti zomwe zalembedwa m'ma subblocks a UNION ndizosiyana pang'ono - izi ndichifukwa choti tili ndi zolemba zoyenera patebulo. Ndipo ngati kulibe, zingakhale zoyenera kupanga: Document(Munthu3, DocumentType) и Document (DocumentType, Wogwira Ntchito).
za dongosolo la minda mu ROW mikhalidweKuchokera pamalingaliro a okonzekera, ndithudi, mukhoza kulemba (A, B) = (constA, constB)ndi (B, A) = (constB, constA). Koma pojambula mu dongosolo la minda mu index, pempho loterolo ndi losavuta kulithetsa pambuyo pake.
Mu pulani yanji?

[onani pa explain.tensor.ru]

Tsoka ilo, tinali opanda mwayi ndipo palibe chomwe chidapezeka mu block yoyamba ya UNION, kotero yachiwiri idaphedwabe. Koma ngakhale - basi 0.037ms ndi 11 mabafa!
Tafulumizitsa pempho ndikuchepetsa kupopera kwa data mu kukumbukira kambirimbiri, pogwiritsa ntchito njira zosavuta - zotsatira zabwino ndi kukopa pang'ono-paste. 🙂

Source: www.habr.com

PostgreSQL Antipatterns: zovulaza JOIN ndi ORs