🥇Ama-Antipattern e-PostgreSQL: ama-JOIN ayingozi nama-ORs

Qaphela imisebenzi eletha amabhafa...
Sisebenzisa umbuzo omncane njengesibonelo, ake sibheke ezinye izindlela ezitholakala emhlabeni wonke zokuthuthukisa imibuzo ku-PostgreSQL. Ukuthi uyazisebenzisa yini noma cha kukuwe, kodwa kubalulekile ukwazi ngazo.

Kwezinye izinguqulo ezilandelayo ze-PG isimo singase sishintshe njengoba isihleli sihlakanipha, kodwa ku-9.4/9.6 sibukeka sicishe sifane, njengakuzibonelo ezilapha.

Ake sithathe isicelo sangempela:

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;

mayelana namathebula namagama enkundlaAmagama "isiRashiya" amasimu namatafula angaphathwa ngendlela ehlukile, kodwa lokhu kuyindaba yokunambitha. Ngoba i lapha eTensor abekho abathuthukisi bangaphandle, futhi i-PostgreSQL isivumela ukuthi sinikeze amagama ngisho nakuma-hieroglyphs, uma kunjalo kufakwe izingcaphuno, bese sikhetha ukuqamba izinto ngokusobala nangokucacile ukuze kungabikho ukungezwani.
Ake sibheke uhlelo oluwumphumela:

[buka kokuthi explain.tensor.ru]

144ms kanye namabhafa acishe abe ngu-53K - okungukuthi, ngaphezu kuka-400MB wedatha! Futhi sizoba nenhlanhla uma zonke zisenqolobaneni ngesikhathi sesicelo sethu, ngaphandle kwalokho kuzothatha izikhathi eziningi uma kufundwa kudiski.

I-algorithm ibaluleke kakhulu!

Ukuze ngandlela thize ulungiselele noma yisiphi isicelo, kufanele uqale uqonde ukuthi yini okufanele siyenze.
Masishiye ukuthuthukiswa kwesakhiwo sedathabhesi ngokwayo ngaphandle kwendawo yalesi sihloko okwamanje, futhi sivume ukuthi singakwazi "ukungabizi kakhulu" bhala kabusha isicelo kanye/noma sigingqe esisekelweni ezinye zezinto esizidingayo izinkomba.

Ngakho isicelo:
— ihlola ubukhona okungenani bombhalo othile
- esimweni esisidingayo kanye nohlobo oluthile
- lapho umbhali noma umenzi eyisisebenzi esimdingayo

JOYINA + UMKHAWULO 1

Ngokuvamile kuba lula kunjiniyela ukuthi abhale umbuzo lapho inani elikhulu lamathebula lihlanganiswa kuqala, bese kuba irekhodi elilodwa kuphela elisele kulo lonke isethi. Kodwa kulula kunjiniyela akusho ukusebenza kahle kusizindalwazi.
Esimeni sethu bekunamatafula ama-3 kuphela - futhi uyini umphumela...

Masiqale sisuse ukuxhumana nethebula elithi "Uhlobo Lombhalo", futhi ngesikhathi esifanayo sitshele isizindalwazi ukuthi irekhodi lethu lohlobo lihlukile (Siyazi lokhu, kodwa umhleli akakakwazi okwamanje):

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

Yebo, uma ithebula/CTE iqukethe inkambu eyodwa yerekhodi elilodwa, khona-ke ku-PG ungakwazi nokubhala kanje, esikhundleni sokuthi

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

Ukuhlola okuvilaphayo emibuzweni ye-PostgreSQL

I-BitmapOr vs UNION

Kwezinye izimo, i-Bitmap Heap Scan izosibiza kakhulu - isibonelo, esimweni sethu, lapho amarekhodi amaningi ehlangabezana nesimo esidingekayo. Sayithola ngoba NOMA isimo siphenduke i-BitmapOr- ukusebenza ohlelweni.
Ake sibuyele enkingeni yokuqala - sidinga ukuthola irekhodi elihambisanayo noma ubani kusukela ezimweni - okungukuthi, asikho isidingo sokucinga wonke amarekhodi we-59K ngaphansi kwezimo zombili. Kukhona indlela yokwenza isimo esisodwa, futhi uye kweyesibili kuphela kungatholwanga lutho kwesokuqala. Idizayini elandelayo izosisiza:

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

“Okwangaphandle” UMKHAWULO 1 uqinisekisa ukuthi ukusesha kuyaphela lapho kutholwa irekhodi lokuqala. Futhi uma isivele itholakele kubhlokhi yokuqala, ibhulokhi yesibili ngeke isetshenziswe (akakaze abulawe mayelana).

"Ukufihla izimo ezinzima ngaphansi kwe-CASE"

Kunesikhathi esingalungile kakhulu embuzweni wokuqala - ukuhlola isimo ngokuqhathanisa nethebula elihlobene elithi “DocumentExtension”. Ngaphandle kweqiniso lezinye izimo kule nkulumo (isibonelo, d.“Kususiwe” AKULONA), lokhu kuxhumana kuhlale kwenziwa futhi "izinsiza zezindleko". Okuningi noma okuncane kuzosetshenziswa - kuya ngosayizi waleli thebula.
Kodwa ungakwazi ukushintsha umbuzo ukuze ukusesha irekhodi elihlobene kwenzeke kuphela uma kudingekile ngempela:

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

Kanye kusuka etafuleni elixhunywe kithi azikho izinkambu ezidingekayo ukuze uthole umphumela, bese siba nethuba lokuguqulela okuthi JOYINA kube umbandela wombuzo omncane.
Asishiye izinkambu ezinenkomba "ngaphandle kubakaki be-CASE", engeza izimo ezilula ukusuka kwirekhodi ukuya kubhulokhi ethi WHEN - futhi manje umbuzo "osindayo" usetshenziswa kuphela uma udlula ku-THEN.

Isibongo sami "Ingqikithi"

Siqoqa umbuzo owumphumela nazo zonke izinsimbi ezichazwe ngenhla:

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;

Ilungisela [ku] izinkomba

Iso eliqeqeshiwe liqaphele ukuthi izimo ezinenkomba kumabhulokhi amancane e-UNION zihluke kancane - lokhu kungenxa yokuthi sesivele sinezinkomba ezifanele etafuleni. Futhi uma bezingekho, kungakuhle ukuthi zidalwe: Idokhumenti(Umuntu3, Uhlobo Lombhalo) и Idokhumenti(Uhlobo Lombhalo, Isisebenzi).
mayelana nokuhleleka kwezinkambu ezimweni ezingu-ROWNgokombono womhleli, kunjalo, ungabhala (A, B) = (constA, constB)futhi (B, A) = (constB, constA). Kodwa uma uqopha ngokulandelana kwezinkambu kunkomba, isicelo esinjalo kulula kakhulu ukusisusa kamuva.
Kunani ohlelweni?

[buka kokuthi explain.tensor.ru]

Ngeshwa, sibe neshwa futhi akutholakalanga lutho ebhulokhini lokuqala le-UNION, ngakho-ke elesibili belisabulawa. Kodwa noma kunjalo - kuphela 0.037ms kanye namabhafa angu-11!
Sisheshise isicelo futhi sanciphisa ukupompa idatha kumemori izikhathi eziyizinkulungwane eziningana, usebenzisa amasu alula - umphumela omuhle nge-copy-paste encane. 🙂

Source: www.habr.com

I-PostgreSQL Antipatterns: ama-JOIN ayingozi nama-ORs