🥇PostgreSQL Antipatterns: zererdar JOIN û OR

Hay ji operasyonên ku tamponan tînin...
Wekî mînakek pirsek piçûk bikar bînin, ka em li hin nêzîkatiyên gerdûnî yên xweşbînkirina pirsan li PostgreSQL binêrin. Ma hûn wan bikar tînin an na li ser we ye, lê hêja ye ku hûn li ser wan zanibin.

Di hin guhertoyên paşîn ên PG-ê de dibe ku rewş biguhezîne her ku plansazker biaqiltir dibe, lê ji bo 9.4/9.6 ew bi qasî heman xuya dike, wekî di mînakên li vir de.

Ka em daxwazek pir rast bistînin:

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;

li ser navên tablo û zeviyanNavên "rûsî" yên zevî û tabloyan dikarin cûda bêne derman kirin, lê ev mijarek tama ye. Ji ber ku li vir li Tensor pêşdebirên biyanî tune ne, û PostgreSQL destûrê dide me ku em navên bi hieroglif jî bidin, heke ew di nav lêkeran de hatine girtin, wê demê em tercîh dikin ku navên tiştan bi awayekî zelal û zelal bi nav bikin da ku cudahî nebin.
Ka em li plana encamê binêrin:
PostgreSQL Antipatterns: JOIN û OR yên zirardar
[li ser şirove.tensor.ru binêre]

144ms û hema hema 53K tampon - ango ji 400MB zêdetir dane! Û em ê bextewar bin heke heya dema daxwaza me ew hemî di kaşê de bin, wekî din dema ku ji dîskê were xwendin dê gelek caran dirêjtir bigire.

Algorîtma herî girîng e!

Ji bo ku hûn daxwazek bi rengek xweşbîn bikin, divê hûn pêşî fêm bikin ka ew çi dike.
Ka em ji bo nuha pêşveçûna avahiya databasê bixwe li derveyî çarçoweya vê gotarê bihêlin, û bipejirînin ku em dikarin bi rengek "erzan" daxwazê ji nû ve binivîsin û/an hin tiştên ku em hewce ne bixin ser bingehê indexes.

Ji ber vê yekê daxwaz:
- hebûna hin belgeyan kontrol dike
- di rewşa ku em hewce ne û ji celebek diyarkirî
- li ku derê nivîskar an pêker karmendê me hewce ye

BİXWÎNE + SÎNOR 1

Pir caran ji bo pêşdebirek hêsantir e ku pirsek binivîse ku li wir hejmareke mezin ji tabloyan pêşî lê têne hev kirin, û dûv re tenê tomarek ji vê berhevokê dimîne. Lê ji bo pêşdebiran hêsantir nayê wateya ji bo databasê bikêrtir.
Di doza me de tenê 3 tablo hebûn - û bandor çi ye ...

Werin em pêşî ji girêdana bi tabloya "Tîpa Belgeyê" xilas bibin, û di heman demê de ji databasê re jî bibêjin ku tomar type me yekta ye (em vê yekê dizanin, lê plansaz hîna nizane):

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

Erê, heke tablo / CTE ji yek zeviyek tomarek yek pêk tê, wê hingê di PG de hûn dikarin bi vî rengî jî binivîsin, li şûna

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

Di pirsên PostgreSQL de nirxandina lazî

BitmapOr vs UNION

Di hin rewşan de, Bitmap Heap Scan dê ji me re pir biha bibe - mînakî, di rewşa me de, dema ku pir tomar şertê pêwîst bicîh tînin. Me ew girt ji ber ku Rewşa OR veguherî BitmapOr- operasyon di planê de.
Ka em vegerin pirsgirêka bingehîn - divê em tomarek têkildar bibînin kesek ji şert û mercan - ango ne hewce ye ku di her du şertan de li hemî tomarên 59K bigere. Rêyek heye ku meriv yek şertê bixebite, û herin ya duyemîn tenê gava ku di ya yekem de tiştek nehat dîtin. Sêwirana jêrîn dê alîkariya me bike:

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

SÎNORÊ "Derveyî" 1 piştrast dike ku gava yekem tomar were dîtin lêgerîn bi dawî dibe. Û heke ew jixwe di bloka yekem de were dîtin, bloka duyemîn dê neyê darve kirin (qet nehatin îdam kirin di derbarê).

"Di bin dozê de şert û mercên dijwar vedişêrin"

Di pirsa orîjînal de demek pir nerehet heye - kontrolkirina statûyê li hember tabloya têkildar "DocumentExtension". Tevî rastiya şertên din ên di îfadeyê de (mînak, d."Jêbirin" NE RAST E), ev pêwendiya her dem tête darve kirin û "çavkaniyan lêçû". Zêdetir an kêmtir ji wan dê werin xerc kirin - bi mezinahiya vê tabloyê ve girêdayî ye.
Lê hûn dikarin pirsê biguhezînin da ku lêgerîna tomarek têkildar tenê gava ku ew bi rastî hewce be pêk tê:

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

Carekê ji tabloya girêdayî me ji bo encamê yek ji qadan hewce ne, wê hingê me fersendek heye ku em JOIN-ê li ser jêrpirsiyekê veguherînin şertek.
Ka em qadên îndekskirî bihêlin "li derveyî bendikên CASE", şert û mercên hêsan ji tomarê li bloka WHEN zêde bikin - û naha pirsa "giran" tenê dema ku derbasî TÊNÊ dibe tê darve kirin.

Paşnavê min "Total" e

Em bi hemî mekanîzmayên ku li jor hatine destnîşan kirin lêpirsîna encam berhev dikin:

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;

Eyarkirina [to] indexan

Çavek perwerdekirî ferq kir ku şert û mercên pêvekirî yên di binblokên UNION de hinekî cûda ne - ev ji ber ku me berê li ser sifrê navnîşên guncan hene. Û eger ew nebûna, ew ê hêja biafirîne: Belge (Kes 3, Cureya Belge) и Belge (Tîpa Belge, Karmend).
li ser rêza zeviyan di şertên ROW deJi xala plansazker, bê guman, hûn dikarin binivîsin (A, B) = (constA, constB)û (B, A) = (constB, constA). Lê dema tomarkirinê bi rêza qadên di îndeksê de, daxwazek wusa bi hêsanî hêsantir e ku paşê xelet bike.
Di planê de çi heye?
PostgreSQL Antipatterns: JOIN û OR yên zirardar
[li ser şirove.tensor.ru binêre]

Mixabin, em bêbext bûn û di bloka UNION ya yekem de tiştek nehat dîtin, ji ber vê yekê ya duyemîn hîn jî hate darve kirin. Lê her weha - tenê 0.037ms û 11 tampon!
Me daxwazê lez kir û pompkirina daneyê di bîranînê de kêm kir çend hezar car, bi karanîna teknîkên pir hêsan - encamek baş bi piçûkek kopî-paste. 🙂

Source: www.habr.com