🥇PostgreSQL Antipatterns: KU biirista waxyeelada leh iyo OR-yada

Ka digtoonow hawlgallada keena kaydka...
Anoo adeegsanayna su'aal yar tusaale ahaan, aynu eegno habab caalami ah oo lagu wanaajinayo su'aalaha PostgreSQL. Haddii aad isticmaasho iyo in kale adiga ayay ku xiran tahay, laakiin waxaa habboon in aad wax ka ogaato.

Qaar ka mid ah noocyada soo socda ee PG xaaladdu way isbedeli kartaa marka jadwalku noqdo mid caqli badan, laakiin 9.4 / 9.6 waxay u egtahay qiyaastii isku mid, sida tusaalooyinka halkan.

Aynu qaadano codsi dhab ah:

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;

ku saabsan magacyada miiska iyo goobtaMagacyada "Ruushka" ee beeraha iyo miisaska waxaa loola dhaqmi karaa si kala duwan, laakiin tani waa arrin dhadhan. Sababtoo ah halkan Tensor ma jiraan horumariyayaal ajnabi ah, iyo PostgreSQL waxay noo ogolaataa inaan magacyo ku bixinno xitaa hieroglyphs, haddii ay ku lifaaqan xigashooyin, ka dib waxaan doorbideynaa inaan u magacowno walxaha si aan mugdi ku jirin oo cad si aysan u dhicin wax khilaaf ah.
Aan eegno qorshaha natiijada:

[fiiri sharaxaad.tensor.ru]

144ms iyo ku dhawaad 53k kayd - taasi waa, in ka badan 400MB oo xog ah! Waxaan nasiib yeelan doonaa haddii dhammaantood ay ku jiraan kaydka wakhtiga codsigayaga, haddii kale waxay qaadan doontaa marar badan oo dheeraad ah marka laga akhriyo diskka.

Algorithm waa tan ugu muhiimsan!

Si aad si uun u wanaajiso codsi kasta, waa inaad marka hore fahantaa waxa la samaynayo.
Aan ka tagno horumarinta qaab dhismeedka xogta lafteeda meel ka baxsan baaxadda maqaalkan hadda, oo aan ku heshiinno inaan "qiimihiisu" ahaan karno dib u qor codsiga iyo/ama ku rog saldhigga qaar ka mid ah waxyaabaha aan u baahanahay indices.

Haddaba codsiga:
- hubinaya jiritaanka ugu yaraan dukumeenti qaar
- xaaladda aan u baahanahay iyo nooc gaar ah
- meesha qoraaga ama wax-qabadku yahay shaqaalaha aan u baahanahay

KU BIIR + LIMIT 1

Inta badan way u fududdahay horumariyaha inuu qoro su'aal halkaas oo tiro badan oo miisas ah marka hore la isku daro, ka dibna hal diiwaan oo kaliya ayaa ka hadhay dhammaan qaybahan. Laakin u sahlan horumariyaha macnaheedu maaha wax ku ool badan kaydka xogta.
Xaaladeena waxaa jiray 3 miis oo kaliya - maxayse saameynaysaa...

Aynu marka hore ka takhalusno xidhiidhka miiska "Nooca Dukumentiga", isla mar ahaantaana u sheeg database-ka taas Diiwaanka noocayagu waa mid gaar ah (Waan ognahay tan, laakiin jadwaleeyaha weli wax fikrad ah kama haysto):

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

Haa, haddii miiska / CTE uu ka kooban yahay hal goob oo rikoodh ah, markaa PG waxaad xitaa u qori kartaa sidan oo kale, halkii aad ka qori lahayd.

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

Qiimaynta caajiska ah ee su'aalaha PostgreSQL

BitmapOr vs UNION

Xaaladaha qaarkood, Bitmap Heap Scan waxay nagu kici doontaa kharash badan - tusaale ahaan, xaaladdeenna, marka diiwaanno badan ay buuxiyaan shuruudaha loo baahan yahay. Waxaan u helnay sababtoo ah AMA xaaladdu waxay noqotay BitmapOr- hawlgalka qorshaha.
Aan ku soo laabano dhibaatadii asalka ahayd - waxaan u baahanahay inaan helno rikoor u dhigma qof kasta laga soo bilaabo shuruudaha - taasi waa, looma baahna in la raadiyo dhammaan diiwaannada 59K ee labada shuruudood. Waxaa jirta hab lagu xalliyo hal shuruud, iyo Tag labaad kaliya marka aan waxba laga helin kii hore. Naqshadaynta soo socota ayaa ina caawin doonta:

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

XADKA "Dibadda" 1 waxay hubisaa in raadintu dhammaanayso marka diiwaanka ugu horreeya la helo. Oo haddii hore loo helay block hore, block labaad lama fulin doono (waligeed lama dilin marka la eego).

"Qarinta xaaladaha adag ee hoos yimaada CASE"

Waxaa jirta wakhti aad u dhib badan su'aashii asalka ahayd - hubinta heerka ka dhanka ah miiska la xidhiidha "DocumentExtension". Iyadoo aan loo eegin runta xaaladaha kale ee tibaaxaha (tusaale ahaan, d."la tirtiray" RUN MAAHA), xidhiidhkan had iyo jeer waa la fuliyay oo "kharashka kheyraadka". In ka badan ama ka yar ayaa la kharash gareeyaa - waxay kuxirantahay xajmiga miiskan.
Laakin waxaad wax ka beddeli kartaa su'aasha si raadinta diiwaanka la xiriirta ay u dhacdo kaliya marka ay lagama maarmaan tahay:

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

Mar laga soo bilaabo miiska ku xidhan noo mid ka mid ah beeraha looma baahna natiijada, ka dib waxaan fursad u haysanaa inaan ku biirno u bedelno shuruud ku saabsan subquery.
Aan ka tagno meelaha la tilmaamey "ka baxsan xargaha CASE", ku dar shuruudaha fudud ee diiwaanka ilaa WHEN block - oo hadda su'aasha "culus" waxaa la fuliyaa kaliya marka loo gudbayo MARKA.

Magacayga dambe waa "Total"

Waxaan ku soo aruurineynaa weydiinta natiijada dhammaan makaanikada kor lagu tilmaamay:

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;

Isku hagaajinta [tusmooyinka].

Indho tababaran ayaa ogaatay in shuruudaha la tilmaamay ee UNION blocks-hoosaadyadu ay waxyar ka duwan yihiin - tani waa sababta oo ah waxaan horeyba miiska u saarnay tusmooyin ku habboon. Oo haddii aysan jirin, waxaa habboonaan lahayd in la abuuro: Dukumeentiga (Qofka3, Nooca Dukumentiga) и Dukumeenti (Nooca Dukumeenti, Shaqaale).
ku saabsan sida ay u kala horreeyaan goobaha xaaladaha ROWMarka laga eego aragtida qorshe-haye, dabcan, waad qori kartaa (A, B) = (constA, constB)iyo (B, A) = (constB, constA). Laakiin marka la duubo sida ay u kala horreeyaan beeraha tusmada, Codsiga noocan oo kale ah ayaa si fudud aad ugu habboon in dib loo saxo.
Maxaa qorshaha ku jira?

[fiiri sharaxaad.tensor.ru]

Nasiib darro, nasiib-darro ayaa nagu dhacday, waxna lagama helin xaruntii ugu horreysay ee UNION, sidaas darteed kii labaad waa la dilay. Laakiin xitaa sidaas - kaliya 0.037ms iyo 11 kayd!
Codsiga waanu dedejinay waxaana yareynay ku shubista xogta xusuusta dhowr kun oo jeer, iyadoo la adeegsanayo farsamooyin fudud oo fudud - natiijo wanaagsan oo leh koobi yar oo koobi ah. 🙂

Source: www.habr.com

PostgreSQL Antipatterns: KU biirista waxyeelada leh iyo OR-yada