🥇PostgreSQL Antipatterns: JOIN zinazodhuru na AU

Jihadhari na utendakazi unaoleta bafa...
Kwa kutumia swali dogo kama mfano, hebu tuangalie baadhi ya mbinu za jumla za kuboresha maswali katika PostgreSQL. Ikiwa unazitumia au la ni juu yako, lakini inafaa kujua kuzihusu.

Katika baadhi ya matoleo yanayofuata ya PG hali inaweza kubadilika kadri kipanga ratiba kinavyokuwa nadhifu, lakini kwa 9.4/9.6 inaonekana takriban sawa, kama ilivyo katika mifano hapa.

Wacha tuchukue ombi la kweli:

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;

kuhusu meza na majina ya uwanjaMajina ya "Kirusi" ya mashamba na meza yanaweza kutibiwa tofauti, lakini hii ni suala la ladha. Kwa sababu ya hapa Tensor hakuna watengenezaji wa kigeni, na PostgreSQL inaruhusu sisi kutoa majina hata katika hieroglyphs, kama wao iliyoambatanishwa katika nukuu, basi tunapendelea kutaja vitu bila utata na kwa uwazi ili kusiwe na tofauti.
Wacha tuangalie mpango unaosababisha:

[tazama kwenye explain.tensor.ru]

144ms na karibu vibafa 53K - Hiyo ni, zaidi ya 400MB ya data! Na tutakuwa na bahati ikiwa wote wako kwenye cache wakati wa ombi letu, vinginevyo itachukua mara nyingi zaidi wakati wa kusoma kutoka kwa diski.

Algorithm ni muhimu zaidi!

Ili kwa namna fulani kuboresha ombi lolote, lazima kwanza uelewe kile kinachopaswa kufanya.
Wacha tuache ukuzaji wa muundo wa hifadhidata yenyewe nje ya wigo wa nakala hii kwa sasa, na tukubaliane kwamba tunaweza "bei nafuu" andika upya ombi na/au ingiza kwenye msingi baadhi ya mambo tunayohitaji bahati.

Kwa hivyo ombi:
- huangalia uwepo wa angalau hati fulani
- katika hali tunayohitaji na ya aina fulani
- ambapo mwandishi au mwigizaji ni mfanyakazi tunayehitaji

JIUNGE + KIKOMO 1

Mara nyingi ni rahisi kwa msanidi programu kuandika swali ambapo idadi kubwa ya jedwali huunganishwa kwanza, na kisha rekodi moja tu inabaki kutoka kwa seti hii yote. Lakini rahisi kwa msanidi programu haimaanishi ufanisi zaidi kwa hifadhidata.
Kwa upande wetu kulikuwa na meza 3 tu - na ni nini athari ...

Wacha kwanza tuondoe unganisho na jedwali la "Aina ya Hati", na wakati huo huo tuambie hifadhidata hiyo. rekodi yetu ya aina ni ya kipekee (tunajua hili, lakini kipanga ratiba bado hajui):

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

Ndio, ikiwa jedwali/CTE lina sehemu moja ya rekodi moja, basi katika PG unaweza hata kuandika kama hii, badala ya

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

Tathmini ya uvivu katika maswali ya PostgreSQL

BitmapOr dhidi ya MUUNGANO

Katika baadhi ya matukio, Bitmap Heap Scan itatugharimu sana - kwa mfano, katika hali yetu, wakati rekodi nyingi zinakidhi hali inayohitajika. Tumeipata kwa sababu AU hali imegeuzwa kuwa BitmapOr- operesheni katika mpango.
Wacha turudi kwenye shida ya asili - tunahitaji kupata rekodi inayolingana yeyote kutoka kwa masharti - yaani, hakuna haja ya kutafuta rekodi zote za 59K chini ya hali zote mbili. Kuna njia ya kusuluhisha hali moja, na nenda kwa pili tu wakati hakuna kitu kilichopatikana katika kwanza. Ubunifu ufuatao utatusaidia:

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

KIKOMO CHA 1 cha "Nje" huhakikisha kwamba utafutaji unaisha wakati rekodi ya kwanza inapatikana. Na ikiwa tayari imepatikana kwenye kizuizi cha kwanza, kizuizi cha pili hakitatekelezwa (haijawahi kutekelezwa kwa heshima ya).

"Kuficha hali ngumu chini ya KESI"

Kuna wakati usiofaa sana katika hoja asili - kuangalia hali dhidi ya jedwali linalohusiana "DocumentExtension". Bila kujali ukweli wa masharti mengine katika usemi (kwa mfano, d.“Imefutwa” SI KWELI), uunganisho huu daima unatekelezwa na "gharama ya rasilimali". Zaidi au chini yao zitatumika - inategemea ukubwa wa meza hii.
Lakini unaweza kurekebisha swali ili utafutaji wa rekodi inayohusiana ufanyike tu wakati inahitajika sana:

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

Mara moja kutoka kwa meza iliyounganishwa kwetu hakuna uwanja unaohitajika kwa matokeo, basi tunayo fursa ya kugeuza JIUNGE kuwa sharti kwenye hoja ndogo.
Hebu tuache sehemu zilizoorodheshwa "nje ya mabano ya CASE", ongeza masharti rahisi kutoka kwa rekodi hadi kizuizi cha WHEN - na sasa hoja "nzito" inatekelezwa tu wakati wa kupita kwa THEN.

Jina langu la mwisho ni "Jumla"

Tunakusanya swali linalotokana na mitambo yote iliyoelezwa hapo juu:

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;

Kurekebisha [kwa] faharasa

Jicho lililofunzwa liligundua kuwa hali zilizoorodheshwa katika vizuizi vidogo vya UNION ni tofauti kidogo - hii ni kwa sababu tayari tuna faharasa zinazofaa kwenye jedwali. Na ikiwa hazikuwepo, ingefaa kuunda: Hati(Mtu3, Aina ya Hati) и Hati (Aina ya Hati, Mfanyakazi).
kuhusu utaratibu wa mashamba katika hali ya ROWKutoka kwa mtazamo wa mpangaji, bila shaka, unaweza kuandika (A, B) = (constA, constB)Na (B, A) = (constB, constA). Lakini wakati wa kurekodi kwa mpangilio wa nyanja katika faharasa, ombi kama hilo ni rahisi zaidi kutatua baadaye.
Nini katika mpango?

[tazama kwenye explain.tensor.ru]

Kwa bahati mbaya, hatukubahatika na hakuna kitu kilichopatikana katika kizuizi cha kwanza cha UNION, kwa hivyo cha pili bado kilitekelezwa. Lakini hata hivyo - tu 0.037ms na vibafa 11!
Tumeongeza kasi ya ombi na kupunguza kasi ya kusukuma data kwenye kumbukumbu mara elfu kadhaa, kwa kutumia mbinu rahisi - matokeo mazuri na nakala-kuweka kidogo. 🙂

Chanzo: mapenzi.com

PostgreSQL Antipatterns: JOIN hatari na ORs