ProHoster > Blog > Tsamaiso > PostgreSQL Antipatterns: JOIN e kotsi le ORs

PostgreSQL Antipatterns: JOIN e kotsi le ORs

Hlokomela lits'ebetso tse tlisang li-buffers...
Re sebelisa potso e nyane joalo ka mohlala, ha re shebeng mekhoa e meng ea bokahohleng ea ho ntlafatsa lipotso ho PostgreSQL. Hore na ua li sebelisa kapa che ho ho uena, empa ho bohlokoa ho tseba ka tsona.

Liphetolelong tse ling tse latelang tsa PG boemo bo ka fetoha ha mohlophisi a ntse a hlaka, empa bakeng sa 9.4/9.6 e shebahala e batla e tšoana, joalo ka mehlala e mona.

Ha re nke kopo ea 'nete:

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;

mabapi le tafole le mabitso a tšimoMabitso a "Russia" a masimo le litafole a ka phekoloa ka tsela e fapaneng, empa ena ke taba ea tatso. Hobane the mona Tensor ha ho na bahlahisi ba kantle ho naha, mme PostgreSQL e re lumella ho fana ka mabitso esita le ka li-hieroglyphs, haeba li joalo e kentsoeng ka mantsoe a qotsitsoeng, joale re khetha ho reha lintho ka mokhoa o hlakileng le ka mokhoa o hlakileng e le hore ho se ke ha e-ba le liphapang.
Ha re shebeng moralo oa sephetho:
PostgreSQL Antipatterns: JOIN e kotsi le ORs
[sheba ho explain.tensor.ru]

144ms le li-buffer tse ka bang 53K - ke hore, data e fetang 400MB! 'Me re tla ba lehlohonolo haeba kaofela ha bona ba le ka har'a cache ka nako ea kopo ea rona, ho seng joalo ho tla nka nako e telele ha ba bala ho tswa ho disk.

Algorithm ke ea bohlokoa haholo!

E le hore ka tsela e itseng u ntlafatse kopo leha e le efe, u tlameha ho qala ho utloisisa seo e lokelang ho se etsa.
Ha re tloheleng nts'etsopele ea sebopeho sa database ka boeona kantle ho sebaka sa sengoloa sena hajoale, 'me re lumellane hore re ka "theko e tlase" ngola kopo hape le/kapa ho theolela motheong tse ling tsa lintho tseo re li hlokang lits'oants'o.

Kahoo kopo:
- e hlahloba boteng ba bonyane tokomane e itseng
- boemong boo re bo hlokang le ba mofuta o itseng
- moo mongoli kapa moetsi e leng mosebeletsi eo re mo hlokang

KENYA LE + LIMIT 1

Hangata ho ba bonolo hore moqapi a ngole potso moo palo e kholo ea litafole e kopanngoeng pele, ebe ho sala rekoto e le 'ngoe feela seteng sena kaofela. Empa ho le bonolo ho moqapi ha ho bolele hore o sebetsa hantle bakeng sa database.
Tabeng ea rona ho ne ho e-na le litafole tse 3 feela - mme phello ke efe ...

Ha re qale ka ho tlosa khokahano le tafole ea "Mofuta oa Tokomane", 'me ka nako e ts'oanang re bolelle database hore. mofuta oa rona rekoto e ikhetha (re tseba sena, empa mohlophisi ha a e-so tsebe):

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

E, haeba tafole / CTE e na le tšimo e le 'ngoe ea rekoto e le' ngoe, joale ho PG u ka ngola tjena, sebakeng sa

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

Tlhahlobo ea botsoa lipotsong tsa PostgreSQL

BitmapOr vs UNION

Maemong a mang, Bitmap Heap Scan e tla re jella chelete e ngata - mohlala, maemong a rona, ha lirekoto tse ngata li fihlela maemo a hlokahalang. Re e fumane hobane KAPA boemo bo fetohile BitmapOr- tshebetso ka moralo.
Ha re khutleleng bothateng ba mantlha - re hloka ho fumana rekoto e tsamaellanang mang kapa mang ho tloha maemong - ke hore, ha ho hlokahale ho batla lirekoto tsohle tsa 59K tlas'a maemo ana ka bobeli. Ho na le mokhoa oa ho sebetsa boemo bo le bong, le ho ea ho ea bobeli feela ha ho sa fumanoa letho ho ea pele. Moralo o latelang o tla re thusa:

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

"External" LIMIT 1 e netefatsa hore patlo e fela ha rekoto ea pele e fumanoa. 'Me haeba e se e fumaneha bolokong ba pele, block ea bobeli e ke ke ea etsoa (ha e eso phethoe mabapi le).

"Ho pata maemo a thata tlas'a CASE"

Ho na le nako e thata haholo potsong ea mantlha - ho sheba boemo khahlano le tafole e amanang le "DocumentExtension". Ho sa tsotelehe 'nete ea maemo a mang polelong (mohlala, d.“Tlosa” HASE NNETE), khokahano ena e lula e etsoa le "litšenyehelo tsa lisebelisoa". Ho tla sebelisoa tse ngata kapa tse fokolang - ho itšetlehile ka boholo ba tafole ena.
Empa o ka fetola potso e le hore ho batla rekoto e amanang le eona ho etsahale ha feela ho hlokahala:

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

Hang ho tloha tafoleng e amanang le rona ha ho na masimo a hlokahalang bakeng sa sephetho, ebe re na le monyetla oa ho fetola JOIN boemong ba subquery.
Ha re tloheng masimo a indexed "ka ntle ho li-brackets tsa CASE", eketsa maemo a bonolo ho tloha ho rekoto ho ea ho WHEN block - 'me joale potso e "boima" e etsoa feela ha e fetela ho THEN.

Sefane sa ka ke "Total"

Re bokella potso e hlahang ka mekhanikhei e hlalositsoeng ka holimo:

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;

Ho fetola [ho] li-index

Leihlo le koetlisitsoeng le hlokometse hore maemo a thathamisitsoeng ho li-subblock tsa UNION a fapane hanyane - ke hobane re se re ntse re e-na le li-index tse loketseng tafoleng. 'Me haeba li ne li le sieo, ho ka ba molemo ho theha: Tokomane(Motho3, Mofuta oa Tokomane) и Tokomane(Mofuta oa Tokomane, Mosebetsi).
mabapi le tatellano ea masimo maemong a ROWHo ea ka pono ea moralo, ehlile, u ka ngola (A, B) = (constA, constB)le (B, A) = (constB, constA). Empa ha o rekota ka tatellano ea masimo a index, kopo e joalo e bonolo ho e lokisa hamorao.
Ho na le eng moralong?
PostgreSQL Antipatterns: JOIN e kotsi le ORs
[sheba ho explain.tensor.ru]

Ka bomalimabe, re ne re le malimabe 'me ha ho letho le ileng la fumanoa lebaleng la pele la UNION, kahoo la bobeli le ne le ntse le bolaoa. Leha ho le joalo - feela 0.037ms le 11 buffers!
Re potlakisitse kopo le ho fokotsa ho pompa data mohopolong makhetlo a likete tse 'maloa, ho sebelisa mekhoa e bonolo e bonolo - sephetho se setle ka kopi-peista e nyane. 🙂

Source: www.habr.com

Eketsa ka tlhaloso