ProHoster > Blog > Rianachd > Antipatterns PostgreSQL: JOINs cronail agus ORs

Antipatterns PostgreSQL: JOINs cronail agus ORs

Bi faiceallach mu ghnìomhachd a bheir bufairean ...
A’ cleachdadh ceist bheag mar eisimpleir, leig dhuinn sùil a thoirt air cuid de dhòighean-obrach uile-choitcheann airson ceistean a mheudachadh ann am PostgreSQL. Tha e an urra riut fhèin co-dhiù an cleachd thu iad no nach eil, ach is fhiach fios a bhith agad mun deidhinn.

Ann an cuid de dhreachan às deidh sin de PG faodaidh an suidheachadh atharrachadh mar a bhios an clàr-ama a’ fàs nas buige, ach airson 9.4/9.6 tha e a’ coimhead timcheall air an aon rud, mar a tha sna h-eisimpleirean an seo.

Gabhaidh sinn iarrtas fìor:

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;

mu ainmean bùird agus achaidheanFaodar dèiligeadh ri ainmean achaidhean is bùird “Ruiseanach” ann an dòigh eadar-dhealaichte, ach tha seo na chùis blas. Air sgàth gu bheil an an seo aig Tensor chan eil luchd-leasachaidh cèin ann, agus leigidh PostgreSQL leinn ainmean a thoirt seachad eadhon ann an hieroglyphs, ma tha dùinte ann an luachan, an uairsin is fheàrr leinn nithean ainmeachadh gu soilleir agus gu soilleir gus nach bi eadar-dhealachaidhean ann.
Bheir sinn sùil air a’ phlana a thig às:
Antipatterns PostgreSQL: JOINs cronail agus ORs
[sealladh aig explain.tensor.ru]

144ms agus faisg air 53K bufairean - is e sin, barrachd air 400MB de dhàta! Agus bidh sinn fortanach ma tha iad uile san tasgadan ro àm ar n-iarrtas, air neo bheir e iomadh uair nas fhaide nuair a thèid a leughadh bhon diosc.

Tha an algairim as cudromaiche!

Gus iarrtas sam bith a bharrachadh ann an dòigh air choreigin, feumaidh tu an toiseach tuigsinn dè a bu chòir dha a dhèanamh.
Fàgaidh sinn leasachadh structar an stòr-dàta fhèin taobh a-muigh raon an artaigil seo airson a-nis, agus aontaich sinn gun urrainn dhuinn gu ìre mhath “saor” ath-sgrìobhadh an iarrtas agus/no rolaich air a’ bhunait cuid de na rudan a tha a dhìth oirnn clàran-amais.

Mar sin an t-iarrtas:
- a’ dèanamh cinnteach gu bheil co-dhiù sgrìobhainn ann
- anns an t-suidheachadh a tha a dhìth oirnn agus de sheòrsa sònraichte
- far a bheil an t-ùghdar no neach-cluiche an neach-obrach a dh'fheumas sinn

Thig còmhla + LIMIT 1

Gu math tric tha e nas fhasa do leasaiche ceist a sgrìobhadh far a bheil àireamh mhòr de chlàran air an ceangal an toiseach, agus an uairsin chan eil ach aon chlàr air fhàgail bhon t-seata iomlan seo. Ach chan eil e nas fhasa don leasaiche a bhith a 'ciallachadh nas èifeachdaiche airson an stòr-dàta.
Anns a 'chùis againn cha robh ann ach 3 clàran - agus dè a' bhuaidh a th 'ann ...

Feuch an cuir sinn às don cheangal leis a’ chlàr “Seòrsa Sgrìobhainn”, agus aig an aon àm innis don stòr-dàta tha an seòrsa clàr againn gun samhail (tha fios againn air seo, ach chan eil beachd aig a’ chlàr-ama fhathast):

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

Tha, ma tha an clàr / CTE air a dhèanamh suas de raon singilte de chlàr singilte, an uairsin ann am PG faodaidh tu eadhon sgrìobhadh mar seo, an àite

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

Measadh leisg ann an ceistean PostgreSQL

BitmapOr vs AONADH

Ann an cuid de chùisean, cosgaidh Bitmap Heap Scan tòrr dhuinn - mar eisimpleir, nar suidheachadh, nuair a choinnicheas tòrr chlàran ris a’ chumha a tha a dhìth. Fhuair sinn e air sgàth NO suidheachadh air a thionndadh gu BitmapOr- obrachadh sa phlana.
Tillidh sinn chun duilgheadas tùsail - feumaidh sinn clàr co-fhreagarrach a lorg do neach sam bith bho na cumhaichean - is e sin, chan eil feum air a h-uile clàr 59K a lorg fon dà chumhachan. Tha dòigh ann air aon staid obrachadh a-mach, agus rachaibh chun an dàrna fear a-mhàin nuair nach deach dad a lorg anns a 'chiad fhear. Cuidichidh an dealbhadh a leanas sinn:

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

Tha “taobh a-muigh” LIMIT 1 a’ dèanamh cinnteach gun tig an rannsachadh gu crìch nuair a lorgar a’ chiad chlàr. Agus ma lorgar e mu thràth sa chiad bhloc, cha tèid an dàrna bloc a chuir gu bàs (cha deach a chur gu bàs a thaobh).

“A’ falach suidheachaidhean duilich fo CASE”

Tha àm air leth mì-ghoireasach anns a’ cheist thùsail - a’ sgrùdadh an inbhe mu choinneamh a’ chlàr co-cheangailte “DocumentExtension”. Ge bith dè an fhìrinn mu shuidheachaidhean eile san abairt (mar eisimpleir, d. CHAN EIL “Sguab às” TRUE), tha an ceangal seo an-còmhnaidh air a chuir gu bàs agus “cosgais e goireasan”. Thèid barrachd no nas lugha dhiubh a chosg - an crochadh air meud a 'bhùird seo.
Ach faodaidh tu a’ cheist atharrachadh gus nach tachair sgrùdadh airson clàr co-cheangailte ach nuair a tha fìor fheum air:

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

Aon uair bhon chlàr ceangailte thugainn chan eil feum air gin de na raointean airson an toradh, an uairsin tha cothrom againn JOIN a thionndadh gu suidheachadh air subquery.
Fàgaidh sinn na raointean clàr-amais “taobh a-muigh camagan CASE”, cuir suidheachaidhean sìmplidh bhon chlàr chun bhloc WHEN - agus a-nis chan eil a’ cheist “trom” air a chuir gu bàs ach nuair a thèid thu gu THEN.

'S e "Iomlan" an t-ainm mu dheireadh a tha orm

Bidh sinn a’ cruinneachadh a’ cheist a thig às leis na meacanaig air fad a tha air am mìneachadh gu h-àrd:

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;

Ag atharrachadh [gu] clàran-amais

Mhothaich sùil ionnsaichte gu bheil na suidheachaidhean clàr-amais ann am fo-bhlocaichean UNION beagan eadar-dhealaichte - tha seo air sgàth gu bheil clàran-amais iomchaidh againn mu thràth air a ’bhòrd. Agus mura robh iad ann, b’ fhiach an cruthachadh: Sgrìobhainn (Duine 3, Seòrsa Sgrìobhainn) и Sgrìobhainn (Seòrsa Sgrìobhainn, Neach-obrach).
mu òrdugh nan raointean ann an suidheachaidhean ROWBho shealladh an dealbhaiche, gu dearbh, faodaidh tu sgrìobhadh (A, B) = (constA, constB)agus (B, A) = (constB, constA). Ach nuair a bhios tu a’ clàradh ann an òrdugh nan raointean sa chlàr-amais, tha iarrtas mar seo dìreach nas fhasa a dheasbad nas fhaide air adhart.
Dè tha sa phlana?
Antipatterns PostgreSQL: JOINs cronail agus ORs
[sealladh aig explain.tensor.ru]

Gu mì-fhortanach, bha sinn mì-shealbhach agus cha deach dad a lorg anns a 'chiad bhloc UNION, agus mar sin chaidh an dàrna fear a chur gu bàs fhathast. Ach eadhon mar sin - a-mhàin 0.037ms agus 11 bufair!
Tha sinn air an iarrtas a luathachadh agus air pumpadh dàta a lughdachadh mar chuimhne grunn mhìltean uair, a 'cleachdadh dhòighean sìmplidh - deagh thoradh le beagan lethbhreac-paste. 🙂

Source: www.habr.com

Cuir beachd ann