PostgreSQL հակապատկերներ. վնասակար միացումներ և OR-ներ

Զգուշացեք բուֆերներ բերող գործողություններից...
Օգտագործելով փոքրիկ հարցումը որպես օրինակ՝ եկեք դիտարկենք PostgreSQL-ում հարցումների օպտիմալացման մի քանի ունիվերսալ մոտեցումներ: Օգտագործեք դրանք, թե ոչ, կախված է ձեզանից, բայց արժե իմանալ դրանց մասին:

PG-ի որոշ հետագա տարբերակներում իրավիճակը կարող է փոխվել, քանի որ ժամանակացույցը դառնում է ավելի խելացի, բայց 9.4/9.6-ի համար այն մոտավորապես նույնն է թվում, ինչպես այստեղ ներկայացված օրինակներում:

Եկեք մի շատ իրական խնդրանք ընդունենք.

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;

աղյուսակների և դաշտերի անունների մասինԴաշտերի և աղյուսակների «ռուսական» անվանումներին կարելի է այլ կերպ վերաբերվել, բայց դա ճաշակի հարց է։ Քանի որ այստեղ՝ Tensor-ում չկան օտարերկրյա մշակողներ, և PostgreSQL-ը մեզ թույլ է տալիս անուններ տալ նույնիսկ հիերոգլիֆներով, եթե դրանք փակցված չակերտների մեջ, ապա նախընտրում ենք օբյեկտները անվանել միանշանակ և հստակ, որպեսզի անհամապատասխանություններ չլինեն։
Դիտարկենք արդյունքում ստացված պլանը.

[նայեք բացատրություն.tensor.ru-ին]

144 ms և գրեթե 53K բուֆերներ - այսինքն ավելի քան 400 ՄԲ տվյալ: Եվ մեր բախտը կբերի, եթե դրանք բոլորը լինեն քեշում մինչև մեր խնդրանքը, հակառակ դեպքում սկավառակից կարդալիս շատ անգամ ավելի երկար կպահանջվի:

Ալգորիթմն ամենակարևորն է:

Ցանկացած հարցում ինչ-որ կերպ օպտիմալացնելու համար նախ պետք է հասկանալ, թե ինչ պետք է անի:
Եկեք առայժմ թողնենք տվյալների բազայի կառուցվածքի զարգացումը այս հոդվածի շրջանակներից դուրս և համաձայնենք, որ մենք կարող ենք համեմատաբար «էժան»: վերաշարադրել հարցումը և/կամ գլորում ենք հիմքի վրա մեզ անհրաժեշտ որոշ իրեր Ինդեքսը.

Այսպիսով, խնդրանքը.
— ստուգում է առնվազն որոշ փաստաթղթի առկայությունը
- մեզ անհրաժեշտ վիճակում և որոշակի տեսակի
- որտեղ հեղինակը կամ կատարողը մեզ անհրաժեշտ աշխատողն է

ՄԻԱՑԵՔ + ՍԱՀՄԱՆԱՓԱԿ 1

Հաճախ ծրագրավորողի համար ավելի հեշտ է հարցում գրել, որտեղ սկզբում միացվում են մեծ թվով աղյուսակներ, իսկ հետո այս ամբողջ հավաքածուից մնում է միայն մեկ գրառում: Սակայն ծրագրավորողի համար ավելի հեշտ չի նշանակում տվյալների բազայի համար ավելի արդյունավետ:
Մեր դեպքում կար ընդամենը 3 աղյուսակ, և ի՞նչ էֆեկտ...

Եկեք նախ ձերբազատվենք «Փաստաթղթի տեսակ» աղյուսակի հետ կապից և միևնույն ժամանակ շտեմարանին ասենք, որ. մեր տեսակի ռեկորդը եզակի է (մենք գիտենք սա, բայց ժամանակացույցը դեռ գաղափար չունի).

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

Այո, եթե աղյուսակը/CTE-ը բաղկացած է մեկ գրառման մեկ դաշտից, ապա PG-ում կարող եք նույնիսկ գրել այսպես՝ փոխարենը.

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

Ծույլ գնահատում PostgreSQL հարցումներում

BitmapOr ընդդեմ UNION

Որոշ դեպքերում, Bitmap Heap Scan-ը մեզ շատ կարժենա, օրինակ՝ մեր իրավիճակում, երբ բավականին շատ գրառումներ համապատասխանում են պահանջվող պայմանին: Մենք դա ստացանք, քանի որ ԿԱՄ վիճակը վերածվեց BitmapOr-ի- շահագործումը պլանում.
Վերադառնանք բուն խնդրին. մենք պետք է համապատասխան գրառում գտնենք ցանկացածին պայմաններից, այսինքն՝ կարիք չկա որոնել բոլոր 59K գրառումները երկու պայմաններում: Մեկ պայման մշակելու միջոց կա, և գնալ երկրորդին միայն այն ժամանակ, երբ առաջինում ոչինչ չի հայտնաբերվել. Հետևյալ դիզայնը կօգնի մեզ.

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

«Արտաքին» LIMIT 1-ը ապահովում է, որ որոնումն ավարտվի, երբ գտնվի առաջին գրառումը: Եվ եթե այն արդեն հայտնաբերվել է առաջին բլոկում, ապա երկրորդ բլոկը չի կատարվի (երբեք չի կատարվել առնչությամբ).

«Դժվար պայմանները ՔԱՂԱՔՈՒՄ»

Բնօրինակ հարցման մեջ չափազանց անհարմար պահ կա՝ «DocumentExtension» համապատասխան աղյուսակի հետ կարգավիճակի ստուգում: Անկախ արտահայտության այլ պայմանների ճշմարտացիությունից (օրինակ. դ. «Ջնջվածը» ՃԻՇՏ ՉԻ), այս կապը միշտ իրականացվում է և «ծախսում է ռեսուրսները»: Դրանցից քիչ թե շատ կծախսվի - կախված է այս աղյուսակի չափից:
Բայց դուք կարող եք փոփոխել հարցումը, որպեսզի համապատասխան գրառումների որոնումը տեղի ունենա միայն այն դեպքում, երբ դա իսկապես անհրաժեշտ է.

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

Մի անգամ կապված աղյուսակից մեզ Արդյունքի համար ոչ մի դաշտ պետք չէ, այնուհետև մենք հնարավորություն ունենք JOIN-ը վերածել պայմանի ենթահարցման:
Եկեք թողնենք ինդեքսավորված դաշտերը «CASE փակագծերից դուրս», գրառումից պարզ պայմաններ ավելացնենք WHEN բլոկին, և այժմ «ծանր» հարցումը կատարվում է միայն THEN-ին անցնելիս:

Իմ ազգանունն է «Ընդամենը»

Ստացված հարցումը մենք հավաքում ենք վերը նկարագրված բոլոր մեխանիզմներով.

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;

ինդեքսների ճշգրտում

Մարզված աչքը նկատեց, որ UNION ենթաբլոկների ինդեքսավորման պայմանները մի փոքր տարբերվում են, քանի որ մենք արդեն ունենք համապատասխան ինդեքսներ սեղանին: Եվ եթե դրանք չլինեին, արժեր ստեղծել. Փաստաթուղթ (անձ 3, փաստաթղթի տեսակ) и Փաստաթուղթ (Փաստաթղթի տեսակ, աշխատող).
ROW պայմաններում դաշտերի հերթականության մասինՊլանավորողի տեսանկյունից, իհարկե, կարող եք գրել (A, B) = (constA, constB)Իսկ (B, A) = (constB, constA). Բայց ձայնագրելիս ինդեքսի դաշտերի հերթականությամբ, նման հարցումը պարզապես ավելի հարմար է ավելի ուշ կարգաբերելու համար։
Ի՞նչ կա պլանում:

[նայեք բացատրություն.tensor.ru-ին]

Ցավոք սրտի, մեր բախտը չբերեց, և առաջին ՀԱՄԱԽՄԲՈՒՄ ոչինչ չգտնվեց, ուստի երկրորդը դեռ մահապատժի ենթարկվեց։ Բայց նույնիսկ այդպես - միայն 0.037ms և 11 բուֆեր!
Մենք արագացրել ենք հարցումը և կրճատել տվյալների պոմպային հիշողության մեջ մի քանի հազար անգամ, բավականին պարզ տեխնիկայի կիրառմամբ՝ լավ արդյունք մի փոքր copy-paste-ով։ 🙂

Source: www.habr.com