🥇PostgreSQL Antipatterns: Kev Koom Tes Ua Tsis Zoo thiab ORs

Ceev faj ntawm kev ua haujlwm uas nqa cov buffers ...
Siv cov lus nug me me ua piv txwv, cia peb saib qee qhov kev qhia thoob ntiaj teb los txhim kho cov lus nug hauv PostgreSQL. Txawm hais tias koj siv lawv los tsis yog nyob ntawm koj, tab sis nws tsim nyog paub txog lawv.

Hauv qee qhov txuas ntxiv ntawm PG qhov xwm txheej yuav hloov pauv raws li lub sijhawm teem sijhawm yuav ntse dua, tab sis rau 9.4 / 9.6 nws zoo li kwv yees li qub, ib yam li hauv cov piv txwv ntawm no.

Cia peb ua qhov kev thov tiag tiag:

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;

hais txog lub rooj thiab cov npeCov npe "Lavxias teb sab" ntawm cov teb thiab cov ntxhuav tuaj yeem kho txawv, tab sis qhov no yog qhov teeb meem ntawm saj. Vim lub ntawm no Tensor tsis muaj cov neeg tsim tawm txawv teb chaws, thiab PostgreSQL tso cai rau peb muab cov npe txawm tias hauv hieroglyphs, yog tias lawv nyob rau hauv cov lus qhia, ces peb nyiam lub npe khoom unambiguously thiab meej kom tsis muaj discrepancies.
Cia peb saib ntawm qhov kev npaj ua tiav:

[saib ntawm piav qhia.tensor.ru]

144ms thiab yuav luag 53K buffers - uas yog, ntau dua 400MB ntawm cov ntaub ntawv! Thiab peb yuav muaj hmoo yog tias tag nrho lawv nyob hauv lub cache los ntawm lub sijhawm peb thov, txwv tsis pub nws yuav siv sijhawm ntev dua thaum nyeem los ntawm disk.

Lub algorithm yog qhov tseem ceeb tshaj plaws!

Txhawm rau ua kom zoo rau qee qhov kev thov, koj yuav tsum xub nkag siab tias nws yuav tsum ua li cas.
Cia peb tawm ntawm txoj kev loj hlob ntawm cov qauv database nws tus kheej sab nraud ntawm cov kab lus no rau tam sim no, thiab pom zoo tias peb tuaj yeem "pheej yig" rov sau ntawv thov thiab/lossis yob rau hauv lub hauv paus qee yam ntawm peb xav tau indices.

Yog li thov:
- txheeb xyuas qhov muaj tsawg kawg yog qee cov ntaub ntawv
- nyob rau hauv cov xwm txheej peb xav tau thiab ntawm ib yam
- qhov twg tus sau lossis tus ua yeeb yam yog tus neeg ua haujlwm peb xav tau

JOIN + LIMIT 1

Feem ntau nws yooj yim dua rau tus tsim tawm los sau cov lus nug uas muaj ntau lub rooj sib koom ua ntej, thiab tom qab ntawd tsuas yog ib cov ntaub ntawv tseem tshuav los ntawm tag nrho cov txheej txheem no. Tab sis yooj yim dua rau tus tsim tawm tsis txhais hais tias muaj txiaj ntsig zoo rau cov ntaub ntawv.
Hauv peb rooj plaub tsuas muaj 3 lub rooj - thiab dab tsi yog qhov cuam tshuam ...

Cia peb xub tshem tawm qhov kev sib txuas nrog "Cov Ntaub Ntawv Hom", thiab tib lub sijhawm qhia rau cov ntaub ntawv hais tias peb hom ntaub ntawv yog tshwj xeeb (peb paub qhov no, tab sis tus teem sijhawm tseem tsis tau paub txog):

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

Yog lawm, yog tias lub rooj / CTE muaj ib daim teb ntawm ib cov ntaub ntawv, ces hauv PG koj tuaj yeem sau tau zoo li no, tsis yog

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

Kev ntsuam xyuas tub nkeeg hauv PostgreSQL cov lus nug

BitmapOr vs UNION

Qee qhov xwm txheej, Bitmap Heap Scan yuav raug nqi ntau rau peb - piv txwv li, hauv peb qhov xwm txheej, thaum cov ntaub ntawv ntau heev ua tau raws li qhov xav tau. Peb tau txais nws vim LOSSIS cov xwm txheej hloov mus rau BitmapOr- kev ua haujlwm hauv kev npaj.
Cia peb rov qab mus rau qhov teeb meem qub - peb yuav tsum nrhiav cov ntaub ntawv sib xws leej twg los ntawm cov xwm txheej - uas yog, tsis tas yuav tshawb nrhiav tag nrho 59K cov ntaub ntawv nyob rau hauv ob qho tib si. Muaj ib txoj hauv kev los ua haujlwm tawm ib qho xwm txheej, thiab mus rau qhov thib ob tsuas yog thaum tsis pom dab tsi hauv thawj. Cov qauv hauv qab no yuav pab peb:

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

"Sab nraud" LIMIT 1 xyuas kom meej tias qhov kev tshawb nrhiav xaus thaum pom thawj cov ntaub ntawv. Thiab yog hais tias nws twb pom nyob rau hauv thawj block, lub thib ob block yuav tsis raug tua (yeej tsis raug tua nyob rau hauv hwm).

"Npaum cov xwm txheej nyuaj hauv CASE"

Muaj lub sijhawm tsis yooj yim heev hauv cov lus nug thawj - kuaj xyuas cov xwm txheej tiv thaiv cov lus cuam tshuam "DocumentExtension". Txawm hais tias qhov tseeb ntawm lwm yam xwm txheej hauv qhov kev qhia (piv txwv li, d. “Deleted” TSIS YOG), qhov kev sib txuas no yeej ib txwm ua tiav thiab "tus nqi peev txheej". Ntau lossis tsawg ntawm lawv yuav raug siv - nyob ntawm qhov loj ntawm lub rooj no.
Tab sis koj tuaj yeem hloov kho cov lus nug kom kev tshawb nrhiav cov ntaub ntawv cuam tshuam tsuas yog thaum nws tsim nyog tiag tiag:

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

Ib zaug los ntawm lub rooj sib txuas rau peb tsis muaj cov teb uas xav tau rau qhov tshwm sim, tom qab ntawd peb muaj lub sijhawm los tig JOIN rau hauv ib qho xwm txheej ntawm cov lus nug.
Cia peb tawm ntawm qhov ntsuas "sab nraum CASE brackets", ntxiv cov xwm txheej yooj yim los ntawm cov ntaub ntawv mus rau THAUM thaiv - thiab tam sim no cov lus nug "hnyav" tsuas yog ua tiav thaum dhau mus rau THAUM.

Kuv lub xeem yog "Tag nrho"

Peb sau cov lus nug tshwm sim nrog tag nrho cov tshuab tau piav qhia saum toj no:

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;

Kho [rau] indexes

Ib lub qhov muag kawm tau pom tias cov xwm txheej indexed hauv UNION subblocks sib txawv me ntsis - qhov no yog vim peb twb muaj qhov tsim nyog indexes ntawm lub rooj. Thiab yog tias lawv tsis muaj, nws yuav tsim nyog tsim: Cov ntaub ntawv (Tib neeg 3, DocumentType) и Cov Ntaub Ntawv (Cov Ntaub Ntawv Hom, Cov Neeg Ua Haujlwm).
hais txog qhov kev txiav txim ntawm teb nyob rau hauv ROW tej yam kev mobLos ntawm tus neeg npaj qhov kev xav, tau kawg, koj tuaj yeem sau tau (A, B) = (constA, constB)thiab (B, A) = (constB, constA). Tab sis thaum kaw nyob rau hauv kev txiav txim ntawm cov teb nyob rau hauv lub index, xws li kev thov tsuas yog yooj yim dua rau kev debug tom qab.
Dab tsi hauv txoj kev npaj?

[saib ntawm piav qhia.tensor.ru]

Hmoov tsis zoo, peb tsis muaj hmoo thiab tsis muaj dab tsi pom hauv thawj UNION thaiv, yog li qhov thib ob tseem raug tua. Tab sis txawm li ntawd - nkaus xwb 0.037ms thiab 11 buffers!
Peb tau ua kom nrawm dua qhov kev thov thiab txo cov ntaub ntawv tso rau hauv lub cim xeeb ntau txhiab zaus, siv cov tswv yim yooj yim - ib qho txiaj ntsig zoo nrog me ntsis luam-paste. 🙂

Tau qhov twg los: www.hab.com

PostgreSQL Antipatterns: Kev Koom Tes Ua Tsis Zoo thiab ORs