ProHoster > Блог > Gudanarwa > PostgreSQL Antipatterns: JOINs masu cutarwa da ORs

PostgreSQL Antipatterns: JOINs masu cutarwa da ORs

Hattara da ayyukan da ke kawo buffers...
Yin amfani da ƙaramin tambaya azaman misali, bari mu kalli wasu hanyoyin duniya don inganta tambayoyin a PostgreSQL. Ko kuna amfani da su ko a'a ya rage naku, amma yana da kyau ku sani game da su.

A wasu nau'ikan PG na gaba yanayin na iya canzawa yayin da mai tsarawa ya zama mafi wayo, amma don 9.4/9.6 yana kama da kusan iri ɗaya, kamar a cikin misalai anan.

Bari mu ɗauki buƙata ta gaske:

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;

game da tebur da sunayen filinSunan "Rasha" na filayen da tebur za a iya bi da su daban, amma wannan batu ne na dandano. Domin da nan a Tensor babu masu haɓaka ƙasashen waje, kuma PostgreSQL yana ba mu damar ba da sunaye ko da a cikin hieroglyphs, idan sun kewaye a cikin quotes, to mun gwammace mu sanya sunayen abubuwa ba tare da wata shakka ba kuma a sarari don kada a sami bambance-bambance.
Bari mu kalli tsarin da aka samu:
PostgreSQL Antipatterns: JOINs masu cutarwa da ORs
[duba bayanin.tensor.ru]

144ms da kusan 53K buffers - wato sama da 400MB na bayanai! Kuma za mu yi sa'a idan dukansu suna cikin cache a lokacin buƙatarmu, in ba haka ba zai ɗauki lokaci da yawa lokacin karantawa daga faifai.

Algorithm shine mafi mahimmanci!

Domin inganta kowane buƙatu ko ta yaya, dole ne ku fara fahimtar abin da yakamata kuyi.
Bari mu bar ci gaban tsarin bayanan da kansa a waje da iyakokin wannan labarin a yanzu, kuma mu yarda cewa za mu iya “mai rahusa” sake rubuta bukatar da/ko mirgine kan tushe wasu abubuwan da muke buƙata fihirisa.

Don haka bukatar:
- yana bincika wanzuwar aƙalla wasu takaddun
- a cikin yanayin da muke buƙata kuma na wani nau'i
- inda marubuci ko mai yin wasan kwaikwayo shine ma'aikacin da muke bukata

SHIGA + IYAKA 1

Sau da yawa yana da sauƙi ga mai haɓakawa ya rubuta tambaya inda aka fara haɗa ɗimbin teburi, sannan rikodin guda ɗaya ne kawai ya rage daga wannan saitin. Amma sauƙi ga mai haɓakawa ba yana nufin mafi inganci ga ma'ajin bayanai ba.
A cikin yanayinmu akwai tebur 3 kawai - kuma menene tasirin ...

Bari mu fara kawar da haɗin gwiwa tare da tebur "Nau'in Takardu", kuma a lokaci guda gaya wa bayanan bayanan nau'in rikodin mu na musamman ne (mun san wannan, amma mai tsara tsarin bai da masaniya tukuna):

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

Haka ne, idan tebur / CTE ya ƙunshi filin guda ɗaya na rikodin guda ɗaya, to a cikin PG har ma za ku iya rubuta kamar haka, maimakon haka.

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

Rage ƙima a cikin tambayoyin PostgreSQL

BitmapOr vs UNION

A wasu lokuta, Bitmap Heap Scan zai kashe mu da yawa - alal misali, a halin da muke ciki, lokacin da bayanai da yawa suka cika yanayin da ake buƙata. Mun samu saboda KO yanayin ya juya ya zama BitmapOr- aiki a cikin tsarin.
Bari mu koma ga ainihin matsalar - muna buƙatar nemo rikodin daidai kowa daga sharuɗɗan - wato, babu buƙatar bincika duk bayanan 59K a ƙarƙashin yanayi biyu. Akwai hanyar yin aiki da yanayi ɗaya, kuma je zuwa na biyu kawai lokacin da ba a sami komai a farkon ba. Zane mai zuwa zai taimake mu:

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

LIMIT 1 "External" yana tabbatar da cewa binciken yana ƙare lokacin da aka samo rikodin farko. Kuma idan an riga an samo shi a cikin block na farko, toshe na biyu ba za a aiwatar da shi ba (ba a kashe shi ba dangane da).

"Boye yanayi mai wahala a ƙarƙashin CASE"

Akwai lokacin da bai dace ba a cikin ainihin tambayar - duba matsayin akan tebur mai alaƙa "Takardu Extension". Ko da kuwa gaskiyar wasu sharuɗɗan a cikin magana (misali, d.“Share” BA GASKIYA BANE), ana aiwatar da wannan haɗin koyaushe kuma "yana kashe albarkatu". Fiye ko žasa daga cikinsu za a kashe - ya dogara da girman wannan tebur.
Amma kuna iya canza tambayar ta yadda binciken mai alaƙa ya faru ne kawai lokacin da ya zama dole:

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

Sau ɗaya daga teburin da aka haɗa zuwa gare mu babu ɗayan filayen da ake buƙata don sakamakon, to muna da damar da za mu mayar da JOIN zuwa wani yanayi a kan subquery.
Bari mu bar filayen da aka lissafta "a waje da maƙallan CASE", ƙara yanayi masu sauƙi daga rikodin zuwa WHEN block - kuma yanzu ana aiwatar da tambayar "nauyi" kawai lokacin wucewa zuwa THEN.

Sunana na ƙarshe shine "Total"

Muna tattara sakamakon binciken tare da duk makanikai da aka kwatanta a sama:

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;

Daidaita [zuwa] fihirisa

Ido mai horarwa ya lura cewa yanayin da aka yi la'akari a cikin shingen UNION ya ɗan bambanta - wannan saboda mun riga mun sami fihirisa masu dacewa akan tebur. Kuma idan ba su kasance ba, zai zama darajar ƙirƙira: Takardu (Mutum3, Nau'in Takardu) и Takardun (Nau'in Takardu, Ma'aikaci).
game da tsari na filayen cikin yanayin ROWDaga mahangar mai tsarawa, ba shakka, kuna iya rubutawa (A, B) = (constA, constB)kuma (B, A) = (constB, constA). Amma lokacin yin rikodi a cikin tsari na filayen a cikin index, Irin wannan buƙatar ya fi dacewa don gyara kuskure daga baya.
Me ke cikin shirin?
PostgreSQL Antipatterns: JOINs masu cutarwa da ORs
[duba bayanin.tensor.ru]

Abin takaici, mun yi rashin sa'a kuma ba a sami wani abu a cikin rukunin farko na UNION ba, don haka na biyun har yanzu an kashe shi. Amma duk da haka - kawai 0.037ms da 11 buffers!
Mun hanzarta buƙatun kuma mun rage fitar da bayanai a ƙwaƙwalwar ajiya sau dubu da dama, Yin amfani da fasaha masu sauƙi masu sauƙi - sakamako mai kyau tare da ɗan kwafi-manna. 🙂

source: www.habr.com

Add a comment