🥇Mga Antipattern ng PostgreSQL: mapaminsalang JOIN at OR

Mag-ingat sa mga operasyong nagdadala ng mga buffer...
Gamit ang isang maliit na query bilang isang halimbawa, tingnan natin ang ilang unibersal na diskarte sa pag-optimize ng mga query sa PostgreSQL. Kung gagamitin mo ang mga ito o hindi, nasa iyo, ngunit sulit na malaman ang tungkol sa mga ito.

Sa ilang kasunod na bersyon ng PG maaaring magbago ang sitwasyon habang nagiging mas matalino ang scheduler, ngunit para sa 9.4/9.6 ay halos pareho ito, tulad ng sa mga halimbawa dito.

Kunin natin ang isang tunay na kahilingan:

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;

tungkol sa mga pangalan ng talahanayan at fieldAng "Russian" na mga pangalan ng mga patlang at talahanayan ay maaaring tratuhin nang iba, ngunit ito ay isang bagay ng panlasa. Dahil ang dito sa Tensor walang mga dayuhang developer, at pinapayagan kami ng PostgreSQL na magbigay ng mga pangalan kahit sa mga hieroglyph, kung sila nakapaloob sa mga quotes, pagkatapos ay mas gusto naming pangalanan ang mga bagay nang hindi malabo at malinaw upang walang mga pagkakaiba.
Tingnan natin ang resultang plano:

[tingnan sa explain.tensor.ru]

144ms at halos 53K buffer - iyon ay, higit sa 400MB ng data! At kami ay magiging mapalad kung ang lahat ng mga ito ay nasa cache sa oras ng aming kahilingan, kung hindi man ay tatagal ito ng maraming beses kapag nabasa mula sa disk.

Ang algorithm ay pinakamahalaga!

Upang kahit papaano ay ma-optimize ang anumang kahilingan, dapat mo munang maunawaan kung ano ang dapat nitong gawin.
Iwanan natin ang pagbuo ng istraktura ng database mismo sa labas ng saklaw ng artikulong ito sa ngayon, at sumang-ayon na maaari tayong medyo "mura" muling isulat ang kahilingan at/o igulong sa base ang ilan sa mga bagay na kailangan natin Ini-index.

Kaya ang kahilingan:
— sinusuri ang pagkakaroon ng hindi bababa sa ilang dokumento
- sa kondisyon na kailangan natin at ng isang tiyak na uri
- kung saan ang may-akda o gumaganap ay ang empleyado na kailangan natin

SUMALI + LIMIT 1

Kadalasan ay mas madali para sa isang developer na magsulat ng isang query kung saan ang isang malaking bilang ng mga talahanayan ay unang pinagsama, at pagkatapos ay isang tala na lamang ang natitira mula sa buong hanay na ito. Ngunit ang mas madali para sa developer ay hindi nangangahulugang mas mahusay para sa database.
Sa aming kaso mayroon lamang 3 talahanayan - at ano ang epekto...

Tanggalin muna natin ang koneksyon sa talahanayang "Uri ng Dokumento", at sabay na sabihin sa database na ang aming uri ng tala ay natatangi (alam namin ito, ngunit wala pang ideya ang scheduler):

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

Oo, kung ang talahanayan/CTE ay binubuo ng isang patlang ng iisang talaan, sa PG maaari ka ring sumulat ng ganito, sa halip na

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

Tamad na pagsusuri sa mga query sa PostgreSQL

BitmapOr vs UNION

Sa ilang mga kaso, malaki ang gastos sa amin ng Bitmap Heap Scan - halimbawa, sa aming sitwasyon, kapag napakaraming record ang nakakatugon sa kinakailangang kondisyon. Nakuha namin ito dahil O kundisyon ay naging BitmapOr- operasyon sa plano.
Bumalik tayo sa orihinal na problema - kailangan nating makahanap ng katumbas na rekord sa alinman mula sa mga kundisyon - iyon ay, hindi na kailangang hanapin ang lahat ng 59K na tala sa ilalim ng parehong mga kundisyon. Mayroong isang paraan upang magawa ang isang kundisyon, at pumunta sa pangalawa lamang kapag walang nakita sa una. Ang sumusunod na disenyo ay makakatulong sa amin:

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

Tinitiyak ng LIMIT 1 ng “External” na matatapos ang paghahanap kapag natagpuan ang unang tala. At kung ito ay natagpuan na sa unang bloke, ang pangalawang bloke ay hindi isasagawa (hindi kailanman pinaandar sa paggalang sa).

"Pagtatago ng mahihirap na kondisyon sa ilalim ng CASE"

Mayroong labis na hindi maginhawang sandali sa orihinal na query - pagsuri sa katayuan laban sa nauugnay na talahanayan na "DocumentExtension". Anuman ang katotohanan ng iba pang mga kundisyon sa expression (halimbawa, d.HINDI TOTOO ang “Tinanggal”.), ang koneksyon na ito ay palaging isinasagawa at "nagkakahalaga ng mga mapagkukunan". Higit o mas kaunti sa mga ito ang gagastusin - depende sa laki ng talahanayang ito.
Ngunit maaari mong baguhin ang query upang ang paghahanap para sa isang nauugnay na tala ay nangyayari lamang kapag ito ay talagang kinakailangan:

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

Sabay mula sa naka-link na table sa amin wala sa mga patlang ang kailangan para sa resulta, pagkatapos ay mayroon kaming pagkakataon na gawing kondisyon ang SUMALI sa isang subquery.
Iwanan natin ang mga naka-index na field na "sa labas ng mga CASE bracket", magdagdag ng mga simpleng kundisyon mula sa talaan hanggang sa WHEN block - at ngayon ang "mabigat" na query ay isinasagawa lamang kapag pumasa sa THEN.

Ang aking apelyido ay "Kabuuan"

Kinokolekta namin ang resultang query kasama ang lahat ng mekanika na inilarawan sa itaas:

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;

Pagsasaayos [sa] mga index

Napansin ng isang sinanay na mata na ang mga naka-index na kundisyon sa mga subblock ng UNION ay bahagyang naiiba - ito ay dahil mayroon na tayong angkop na mga index sa talahanayan. At kung wala ang mga ito, sulit na likhain: Dokumento(Person3, DocumentType) и Dokumento(DocumentType, Empleyado).
tungkol sa pagkakasunud-sunod ng mga field sa mga kondisyon ng ROWMula sa punto ng view ng tagaplano, siyempre, maaari kang magsulat (A, B) = (constA, constB)At (B, A) = (constB, constA). Ngunit kapag nagre-record sa pagkakasunud-sunod ng mga patlang sa index, ang naturang kahilingan ay mas maginhawang i-debug sa ibang pagkakataon.
Ano ang nasa plano?

[tingnan sa explain.tensor.ru]

Sa kasamaang palad, hindi kami pinalad at walang nakita sa unang bloke ng UNION, kaya ang pangalawa ay pinaandar pa rin. Ngunit kahit na - lamang 0.037ms at 11 buffer!
Pinabilis namin ang kahilingan at binawasan ang pagbomba ng data sa memorya ilang libong beses, gamit ang medyo simpleng mga diskarte - isang magandang resulta na may kaunting copy-paste. 🙂

Pinagmulan: www.habr.com

Mga PostgreSQL Antipattern: mga nakakapinsalang JOIN at OR