🥇PostgreSQL Antipatterns: zvinokuvadza JOINs uye ORs

Ngwarira maoparesheni anounza mabhafa...
Tichishandisa mubvunzo mudiki semuenzaniso, ngatitarisei dzimwe nzira dzepasirese dzekugadzirisa mibvunzo muPostgreSQL. Kuti unozvishandisa here kana kuti kwete zviri kwauri, asi zvakakosha kuziva nezvavo.

Mune mamwe mavhezheni anotevera ePG mamiriro anogona kuchinja sezvo mugadziri anove akangwara, asi kune 9.4 / 9.6 inotaridzika zvakafanana, semumienzaniso iri pano.

Ngatitore chikumbiro chaicho:

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;

nezvetafura nemazita emundaMazita e "Russian" eminda nematafura anogona kurapwa zvakasiyana, asi iyi inyaya yekuravira. Nokuti iyo pano paTensor hapana vagadziri vekunze, uye PostgreSQL inotitendera kuti tipe mazita kunyangwe mune hieroglyphs, kana ivo zvakavharirwa mumakotesheni, tobva tasarudza kutumidza zvinhu zvisina kujeka uye zvakajeka kuitira kuti pasave nekusiyana.
Ngatitarisei chirongwa chinobuda:

[tarisa ku explain.tensor.ru]

144ms uye ingangoita 53K buffers - ndiko kuti, kupfuura 400MB yedata! Uye isu tichava nerombo rakanaka kana vese vari mu cache panguva yekukumbira kwedu, zvikasadaro zvinotora nguva yakawanda nguva yakareba kana ichiverengwa kubva ku diski.

Iyo algorithm ndiyo inonyanya kukosha!

Kuti neimwe nzira kukwidziridza chero chikumbiro, unofanira kutanga wanzwisisa zvachinofanira kuita.
Ngatisiyei kuvandudzwa kweiyo dhatabhesi chimiro kunze kwechikamu chechinyorwa ichi ikozvino, uye tibvumirane kuti isu tinogona "zvakachipa" nyora zvakare chikumbiro uye/kana kumonera pahwaro zvimwe zvezvinhu zvatinoda indices.

Saka chikumbiro:
- inotarisa kuvepo kweimwe gwaro
- muchimiro chatinoda uye cherumwe rudzi
- uko munyori kana mutambi ari mushandi watinoda

JOIN + LIMIT 1

Kazhinji zviri nyore kuti mugadziri anyore mubvunzo apo nhamba huru yematafura inotanga kubatanidzwa, uyezve rekodhi rimwe chete rinosara kubva pane iyi yese. Asi zviri nyore kune mugadziri hazvirevi zvakanyanya kushanda kune database.
Kwatiri kwaingova nematafura matatu - uye chii chiri kuitika ...

Ngatitangei kubvisa kubatana ne "Document Type" tafura, uye panguva imwechete tiudze database kuti. mhando yedu rekodhi rakasiyana (isu tinoziva izvi, asi mugadziri haasati aziva):

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

Ehe, kana tafura/CTE iine ndima imwechete yerekodhi imwe chete, saka muPG unogona kunyora seizvi, pane

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

Simbe kuongororwa muPostgreSQL mibvunzo

BitmapOr vs UNION

Mune zvimwe zviitiko, Bitmap Heap Scan ichatidhurira zvakanyanya - semuenzaniso, mumamiriro edu ezvinhu, kana marekodhi akawanda achisangana nemamiriro anodiwa. Takazviwana nekuti KANA mamiriro akashandurwa kuita BitmapOr- kushanda muchirongwa.
Ngatidzokere kune dambudziko rekutanga - tinoda kuwana rekodhi rinoenderana chero munhu kubva mumamiriro ezvinhu - ndiko kuti, hapana chikonzero chekutsvaga zvinyorwa zvose zve59K pasi pemamiriro ose maviri. Pane nzira yekugadzirisa imwe mamiriro, uye enda kune wechipiri chete pasina kuwanikwa kwekutanga. Iyo inotevera dhizaini ichatibatsira:

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

"External" LIMIT 1 inovimbisa kuti kutsvaga kunopera kana rekodhi rekutanga rawanikwa. Uye kana yatowanikwa mubhokisi rekutanga, yechipiri bhuroka haizouraiwa (haana kumbourayiwa maererano).

"Kuvanza mamiriro ezvinhu akaoma pasi peCASE"

Pane imwe nguva isingaite zvakanyanya mumubvunzo wekutanga - kutarisa chimiro uchipesana netafura ine hukama "DocumentExtension". Pasinei nechokwadi chemamwe mamiriro mukutaura (semuenzaniso, d.“Zvakabviswa” HAZVISI CHOKWADI), kubatana uku kunogara kuchiitwa uye "zvinodhura zviwanikwa". Zvimwe kana zvishoma zvadzo zvichashandiswa - zvinoenderana nehukuru hwetafura iyi.
Asi iwe unogona kugadzirisa mubvunzo kuitira kuti kutsvaga rekodhi ine hukama kuitike chete pazvinenge zvichinyatsodiwa:

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

Kamwe kubva patafura yakabatana kwatiri hapana yeminda inodiwa kune mhedzisiro, saka isu tine mukana wekushandura JOIN kuita mamiriro pane subquery.
Ngatisiyei minda yakamisikidzwa "kunze kwemabhuraketi eCASE", wedzera mamiriro akareruka kubva parekodhi kuenda kuWHEN block - uye ikozvino "inorema" kubvunza kunoitwa chete kana ichipfuura kune THEN.

Zita rangu rekupedzisira ndi "Total"

Isu tinounganidza mhedzisiro yemubvunzo nemakanika ese anotsanangurwa pamusoro:

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;

Kugadzirisa [ku] indexes

Ziso rakadzidziswa rakaona kuti mamiriro akaiswa muUNION subblocks akasiyana zvishoma - izvi imhaka yekuti isu tatova nema indexes akakodzera patafura. Uye kana ivo vasipo, zvingave zvakakodzera kugadzira: Gwaro(Munhu3, GwaroType) и Gwaro (DocumentType, Mushandi).
nezve kurongeka kweminda muROW mamiriroKubva pakuona kwemurongi, hongu, unogona kunyora (A, B) = (constA, constB)uye (B, A) = (constB, constA). Asi pakurekodha mukurongeka kweminda mune index, chikumbiro chakadaro chiri nyore kugadzirisa gare gare.
Chii chiri muhurongwa?

[tarisa ku explain.tensor.ru]

Nehurombo, isu takanga tisina rombo rakanaka uye hapana chakawanikwa mune yekutanga UNION block, saka yechipiri yakanga ichiri kuurayiwa. Asi kunyange zvakadaro - chete 0.037ms uye 11 mabhafa!
Isu takamhanyisa chikumbiro uye tadzikisa data kupomba mundangariro zviuru zvakati wandei, uchishandisa nzira dzakareruka - mhedzisiro yakanaka ine diki kopi-paste. 🙂

Source: www.habr.com

PostgreSQL Antipatterns: Inokuvadza JOINs uye ORs