🥇Mga Antipattern sa PostgreSQL: makadaot nga mga JOIN ug OR

Pagbantay sa mga operasyon nga nagdala og buffers...
Gamit ang gamay nga pangutana isip pananglitan, atong tan-awon ang pipila ka unibersal nga pamaagi sa pag-optimize sa mga pangutana sa PostgreSQL. Kung gamiton nimo kini o dili naa kanimo, apan takus nga mahibal-an ang bahin niini.

Sa pipila ka sunod-sunod nga mga bersyon sa PG ang sitwasyon mahimong mausab samtang ang scheduler mahimong mas maalamon, apan alang sa 9.4 / 9.6 kini tan-awon halos pareho, sama sa mga pananglitan dinhi.

Atong kuhaon ang tinuod nga hangyo:

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;

mahitungod sa mga ngalan sa lamesa ug umaAng "Russian" nga mga ngalan sa mga uma ug mga lamesa mahimong pagtratar sa lahi, apan kini usa ka butang sa lami. Tungod kay ang dinhi sa Tensor walay langyaw nga developers, ug PostgreSQL nagtugot kanato sa paghatag og mga ngalan bisan sa hieroglyphs, kon sila gilakip sa mga kinutlo, unya mas gusto namo nga hinganlan ang mga butang nga dili klaro ug klaro aron walay mga kalainan.
Atong tan-awon ang resulta nga plano:

[tan-awa sa explain.tensor.ru]

144ms ug hapit 53K buffers - kana mao, labaw pa sa 400MB nga datos! Ug swerte kami kung silang tanan naa sa cache sa oras sa among hangyo, kung dili kini magdugay sa daghang mga higayon kung mabasa gikan sa disk.

Ang algorithm mao ang labing hinungdanon!

Aron ma-optimize ang bisan unsang hangyo, kinahanglan nimo nga masabtan una kung unsa ang kinahanglan buhaton.
Atong biyaan ang pag-uswag sa istruktura sa database mismo sa gawas sa sakup sa kini nga artikulo sa karon, ug magkauyon nga mahimo naton nga "barato" isulat pag-usab ang hangyo ug/o roll ngadto sa base sa pipila sa mga butang nga atong gikinahanglan mga indeks.

Busa ang hangyo:
- nagsusi sa paglungtad sa labing menos pipila ka dokumento
- sa kondisyon nga atong gikinahanglan ug sa usa ka matang
- diin ang tagsulat o performer mao ang empleyado nga atong gikinahanglan

Apil + LIMIT 1

Sa kasagaran mas sayon alang sa usa ka developer ang pagsulat og usa ka pangutana diin ang usa ka dako nga gidaghanon sa mga lamesa unang giapil, ug unya usa na lang ka rekord ang nagpabilin gikan niining tibuok nga set. Apan mas sayon alang sa developer wala magpasabot nga mas episyente alang sa database.
Sa among kaso adunay 3 ra nga mga lamesa - ug unsa ang epekto ...

Atong wagtangon una ang koneksyon sa lamesa nga "Type sa Dokumento", ug sa samang higayon isulti ang database nga talagsaon ang among type record (nahibal-an namon kini, apan ang scheduler wala pay ideya):

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

Oo, kung ang lamesa / CTE naglangkob sa usa ka uma sa usa ka rekord, nan sa PG mahimo ka nga magsulat sama niini, imbes nga

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

Lay evaluation sa PostgreSQL nga mga pangutana

BitmapOr batok sa UNION

Sa pipila ka mga kaso, ang Bitmap Heap Scan mogasto og dako - pananglitan, sa among sitwasyon, kung daghang mga rekord ang nakab-ot sa gikinahanglan nga kondisyon. Nakuha namo kini tungod kay OR kahimtang nahimong BitmapOr- operasyon sa plano.
Balikan nato ang orihinal nga problema - kinahanglan natong pangitaon ang katugbang nga rekord bisan unsa gikan sa mga kondisyon - nga mao, dili na kinahanglan pangitaon ang tanan nga 59K nga mga rekord sa ilawom sa duha nga mga kondisyon. Adunay usa ka paagi sa pagtrabaho sa usa ka kondisyon, ug adto sa ikaduha lamang kung walay nakit-an sa una. Ang mosunod nga disenyo makatabang kanato:

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

Ang "External" LIMIT 1 nagsiguro nga ang pagpangita matapos kung makit-an ang una nga rekord. Ug kung nakit-an na kini sa una nga bloke, ang ikaduha nga bloke dili ipatuman (wala gayud gipatay bahin sa).

"Pagtago sa lisud nga mga kahimtang ubos sa CASE"

Adunay usa ka labi ka dili kombenyente nga higayon sa orihinal nga pangutana - pagsusi sa kahimtang batok sa may kalabutan nga lamesa nga "DocumentExtension". Bisan unsa pa ang kamatuoran sa ubang mga kondisyon sa ekspresyon (pananglitan, d. “Deleted” DILI TINUOD), kini nga koneksyon kanunay nga gipatuman ug "naggasto sa mga kapanguhaan". Daghan o kulang kanila ang gastohon - depende sa gidak-on niini nga lamesa.
Apan mahimo nimong usbon ang pangutana aron ang pagpangita alang sa usa ka may kalabutan nga rekord mahitabo lamang kung kini gikinahanglan:

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

Kausa gikan sa gisumpay nga lamesa sa amon walay bisan usa sa mga uma ang gikinahanglan alang sa resulta, unya aduna kitay kahigayonan nga himoon ang JOIN ngadto sa kondisyon sa subquery.
Ibilin nato ang mga na-index nga field "sa gawas sa CASE bracket", idugang ang yano nga mga kondisyon gikan sa rekord ngadto sa WHEN block - ug karon ang "bug-at" nga pangutana gipatuman lamang kung moagi sa THEN.

Ang akong apelyido kay "Total"

Gikolekta namo ang resulta nga pangutana uban sa tanang mekaniko nga gihulagway sa ibabaw:

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;

Pag-adjust [sa] mga index

Namatikdan sa usa ka nabansay nga mata nga ang mga kondisyon nga gi-index sa mga subblock sa UNION gamay ra - kini tungod kay naa na kami mga angay nga mga indeks sa lamesa. Ug kung wala sila, angay nga buhaton: Dokumento(Tawo3, DocumentType) и Dokumento(DocumentType, Empleyado).
mahitungod sa han-ay sa mga natad sa mga kondisyon sa ROWGikan sa punto sa panglantaw sa tigplano, siyempre, makasulat ka (A, B) = (constA, constB)ug (B, A) = (constB, constA). Apan sa pagrekord sa han-ay sa mga natad sa indeks, ang ingon nga hangyo mas sayon nga i-debug sa ulahi.
Unsay naa sa plano?

[tan-awa sa explain.tensor.ru]

Ikasubo, wala kami swerte ug wala’y nakit-an sa una nga bloke sa UNION, mao nga gipatay gihapon ang ikaduha. Apan bisan pa - lamang 0.037ms ug 11 buffers!
Gipadali namo ang hangyo ug gipakunhod ang data pumping sa memorya pila ka libo ka beses, gamit ang medyo yano nga mga teknik - usa ka maayong resulta nga adunay gamay nga copy-paste. 🙂

Source: www.habr.com

Mga Antipattern sa PostgreSQL: makadaot nga mga JOIN ug OR