🥇PostgreSQL Antipatterns: վնասակար JOIN և OR

Ոչ մի դեպքում մի φοգեք ապահովագրությունից, buffers տրամադրող…
Նախագահն է, թողեք, որ փոքր հարցում ուսումնասիրենք որոշ համընդհանուր մոտեցումներ 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;

առնչվող նշանների և դաշտերիԴա, թե ինչպես ենք վերաբերվում «ռուհեր» տեսակի դաշտերին ու աղյուսակներին, կարող է տարբեր կերպ ընկալվել, բայց դա՝ առարկայի խնդիրը: Որպեսզի մեր «Թենզերում» ոչ մի օտար ծրագրավորող չկա, իսկ PostgreSQL-ն մեզ թույլ է տալիս անվանել թե թերագծերով, եթե դրանք ճանապարհված են մեջբերումներում, ուստի մենք նախընտրում ենք կոչել օբյեկտները պարզ ու հասկանալի, որպեսզի տարբեր մեկնություններ չառաջանան:
Շարունակենք ստացված պլանի վրա․
PostgreSQL Antipatterns: վնասակար JOIN և OR
[տեսնել explain.tensor.ru]

144ms և մոտ 53K buffers — այսինքն՝ ավելի քան 400MB տվյալներ։ Եվ մեզ երջանիկ է, եթե դրանք բոլորը գտնվում են կեշի մեջ մեր հարցման պահի, հակառակ դեպքում դա կդառնա մի քանի անգամ ավելի երկար, երբ կարդում ենք լիցքավորման ժամանակ։

Ալգորիղմը ամենակարևորն է։

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

Այսպես, հարցումը․
— ստուգում է ինչ-որ փաստաթղթի առկայությունը
— ցանկալի կարգավիճակով և որոշակի տեսակի
— որտեղ հեղինակն է կամ կատարողը ցանկալի աշխատակիցը

JOIN + LIMIT 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 մեզ համար շատ արժեցող կլինի՝ օրինակ, մեր իրավիճակում, երբ շատ գրառումներ ենթարկվում են պահանջվող պայմաններին։ Մենք ստացանք դա OR-պայմանից, որը վերածվեց BitmapOr-ընտրանքներին պլանի մեջ։Վերադառնանք սկզբնական խնդրի — պետք է գտնել գրությունը, որը համապատասխանում է
թե որևէ առավելագույներից — այլ բառերով, չունեցեք՝ թե բոլոր 59K գրառումների համար երկու պայմաններով։ Կա եղանակ՝ մեկ շփումը մշակելու, ևս բացի Երկրորդին անցնել միայն այն ժամանակ, երբ առաջինից ոչինչ չի գտնվել. Որն է օգնելու մեզ այս կոնստրուկցիան.

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

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

«Թաքցնում ենք CASE-ի տակ» բարդ պայմանները

Սկզբնական հարցում կա չափազանց անհարմար պահ՝ կապված «Դոկումենտի ընդլայնում» կապված աղյակում պայմանների ստուգման հետ։ Անկախ մյուս պայմանների ճիշտ լինելուց (օրինակի համար, d.«Ջնջված» ԱՆՍՊԱՍՆ Է), այս միացումը միշտ իրականացվում է և «ծախսում է ռեսուրսներ»։ Ավելին կամ պակաս դրանց ծախսարկումը կախված է տվյալ աղյակի ծավալից:
Ամեն դեպքում, հնարավոր է մոդիֆիկացնել հարցը, որպեսզի կապված գրառումների որոնումը կատարվի միայն անհրաժեշտության դեպքում.

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»-ից դուրս՝ պարզ պայմանները գրառման մեջ տեղադրել ՎԵՀ-բլոկում՝ և այժմ «ծանր» հարցը իրականացվում է միայն երբ անցնում ենք 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), և . Բայց գրելու դեպքումինդեքսում դաշտերի կարգով , այս պահը պարզապես ավելի հեշտ է հետո շտկել։Ինչ է պլանում:
Что в плане?
PostgreSQL Antipatterns: վնասակար JOIN և OR
[տեսնել explain.tensor.ru]

Ցավոք, մենք վրիպեցինք, և առաջին UNION-բլոկում ոչինչ չունենք, ուստի երկրորդը կշարունակվի կատարումը: Բայց անգամ այդ դեպքում — ընդամենը 0.037ms և 11 buffers!
Մենք արագացրեցինք հարցումը և կրճատեցինք «տեղեկատվության» փոխանցումը հասկանալի հիշողության մեջ հազարային անգամ, օգտվելով բավականին հեշտ մեթոդաբանություններից — ոչ վատ արդյունք փոքրպիսի կոպի-պաստի համար: 🙂

Ընտանիք: habr.com