Ընդմիջումները փաստաբանության SQL-նամակների համար.

Ընթերցել է ուշագրավ առաջիկա ամսվա մենք հայտարարել ենք explain.tensor.ru — հանրային սերվիս запросների պլանների վերլուծություններում և տեսողական ներկայացման համար PostgreSQL-ի համար։

Մեր կողմից անցած ժամանակահատվածում դուք արդեն օգտագործել եք այն ավելի քան 6000 անգամ, բայց կարող է մի շարք հարմարավետ գործառույթներ չնկիտված մնալ — դա է ստեղծվելը, որը երևում է մոտավորապես այստեղ։

Եղեք նրանց ուշադրությունը և ձեր հարցումները «դառնալու են հարթ ու թավշյա»։ 🙂

Իսկ եթե լուրջ, ապա շատ իրավիճակներ, որոնք հարցման արագությունը դանդաղ են և «ռեսուրսներ պահանջող» տիպիկ են և կարող են ճանաչվել հարցման կառուցվածքով և պլանային տվյալներով.

Այս պարագայում յուրաքանչյուր Developer-ին չի լինի անհրաժեշտ ինքնուրույն փնտրել օպտիմացման տարբերակը, հենվելով միայն իր փորձության վրա — մենք կարող ենք նրան հանդիպել, թե ինչ է կատարվում այստեղ, ինչ կարող է լինել պատճառը, և ինչպես լուծման կողմնորոշվել. Մենք դա իսկապես արել ենք։

Եկեք կողմից դիտենք այս դեպքերը՝ ինչպես դրանք որոշվում են և ինչ առաջարկներ են արտացոլում։

Լավագույնը ճանապարհորդելու համար, նախ կարող եք լսել համապատասխան հատվածը իմ զեկույցից PGConf.Russia 2020, հետո անցնել բարդ նախադասությունների մանրամասն վերլուծությանը։

#1: индексная «недосортировка»

Երբ է առաջանում

Ցուցադրել վերջին հաշիվը «ՕՕ Կոլոկոլչիկ» հաճախորդի համար։

Ինչպես ճշտել

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

Հոլովականներ

Օգտագործած ինդեքս երկարացնել դասակարգման տարածքներով.

Օրինակ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K " փաստեր"
, (random() * 1000)::integer fk_cli; -- 1K տարբեր արտաքին բանալի

CREATE INDEX ON tbl(fk_cli); -- բանալի արտաքին բանալիի համար

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- կոնկրետ կապով ընտրված
ORDER BY
  pk DESC -- ուզում ենք միայն մեկ «վերջին» գրանցումը
LIMIT 1;

[տեսնել explain.tensor.ru]

Անմիջապես կարելի է նկատել, որ ինդեքսով ապահովվելը խաղարկվել է ավելի քան 100 գրանցման, որոնք հետո բոլորը դասակարգվում էին, իսկ հետո միայն մեկն է թողնվել։

Ստուգենք.

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- ավելացրեցինք դասակարգման բանալին

[տեսնել explain.tensor.ru]

Վստահաբար այս պարզ ընտրովության դեպքում — 8.5 անգամ ավելի արագ և 33 անգամ ավելի քիչ ընթերցումներ. Արդյունքը կլինի այնքան ավելի նկատելի, որքան ավելի շատ են ձեր «փաստերը» յուրաքանչյուր արժեքի համար fk.

Շեշտում եմ, որ այսպիսի ինդեքսը կլինի «prefix» ոչ ավելի վատ, քան նախորդն ու մյուս հարցումների դեպքում fk, որտեղ դասակարգումներ ըստ pk չկար և չկա (այս մասին ավելի մանրամասն կարող եք կարդալ իմ հոդվածում անտարբեր ինդեքսների որոնման մասին). Ընտրանքը ապահովելու է։ հայտարարի արտաքին բանալի այդ դաշտում։

#2: пересечение индексов (BitmapAnd)

Երբ է առաջանում

Ցուցադրել բոլոր պայմանները «ՕՕ Կոլոկոլչիկ» հաճախորդի համար, ստորագրված «ՆԱՕ Լյուտիկ» անունից։

Ինչպես ճշտել

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Հոլովականներ

Ստեղծել հասկարմամբ ինդեքս երկու ուղիղ աղբյուրների դաշտերով կամ երկարացնել մեկ առկաի դաշտերով երկրորդից։

Օրինակ:

ՍՏԱՆՈՒՄ Է TBL ԱՎԵԼԱՑՆԵԼ
ԸՆԹ՛ՐՈՒԹՅՈՒՆ
  generate_series(1, 100000) pk      -- 100K "փաստներ"
, (random() *  100)::integer fk_org  -- 100 տարբեր արտաքին բանալիներ
, (random() * 1000)::integer fk_cli; -- 1K տարբեր արտաքին բանալիներ

ՍՏԱՆՈՒՄ Է ՐԵՅՍ ֆիռ կայքում(fk_org); -- բանակցային բանալու արժեք
ՍՏԱՆՈՒՄ Է ՐԵՅՍ ֆիռ կայքում(fk_cli); -- բանակցային բանալու արժեք

ԸՆԹ՛ՐՈՒԹՅՈՒՆ
  *
ՈՒ
  tbl
ՈՒ
  (fk_org, fk_cli) = (1, 999); -- կոնկրետ զույգի ընտրություն

[տեսնել explain.tensor.ru]

Ստուգենք.

ՋԱՐԻՆ TBL_FK_ORG_IDX;
ՍՏԱՆՈՒՄ Է ՐԵՅՍ ֆիռ կայքում(fk_org, fk_cli);

[տեսնել explain.tensor.ru]

Այստեղ շահույթը ավելի փոքր է, քանի որ Bitmap Heap Scan-ը բավականին արդյունավետ է ինքն իրեն։ Բայց այնուամենայնիվ 7 անգամ ավելի արագ և 2.5 անգամ ավելի քիչ ընթերցումներ.

#3: объединение индексов (BitmapOr)

Երբ է առաջանում

Հայտնաբերել առաջին 20 «իմ» կամ նշանակված հայտեր մշակման համար՝ իսկապես իմ դեպքում առաջնահերթություն տալով։

Ինչպես ճշտել

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Հոլովականներ

Օգտագործել UNION [ALL] միավորված ենթարկված բլոկների շուրջ յուրաքանչյուր OR-պահանջների պայմաններում։

Օրինակ:

ՍՏԱՆՈՒՄ Է TBL ԱՎԵԼԱՑՆԵԼ
ԸՆԹ՛ՐՈՒԹՅՈՒՆ
  generate_series(1, 100000) pk  -- 100K "փաստներ"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- 1:16 հավանականությամբ "նիկոլ" գրառումը
    ELSE (random() * 100)::integer -- 100 տարբեր արտաքին բանալիներ
  END fk_own;

ՍՏԱՆՈՒՄ Է ՐԵՅՍ ֆիռ կայքում(fk_own, pk); -- «նման» սորտավորման վարկանիշային արժեք

ԸՆԹ՛ՐՈՒԹՅՈՒՆ
  *
ՈՒ
  tbl
ՈՒ
  fk_own = 1 ՈՒ -- իմ
  fk_own IS NULL -- ... կամ «նիկոլ»
ԻՐՈՒՆ
  pk
, (fk_own = 1) DESC -- առաջինը «իմ»
ԺՈՂՈՎ 20;

[տեսնել explain.tensor.ru]

Ստուգենք.

(
  ԸՆԹ՛ՐՈՒԹՅՈՒՆ
    *
  tbl
  ՈՒ
    fk_own = 1 -- առաջինը «իմ» 20
  ՈՒ
    pk
  ԺՈՂՈՎ 20
)
UNION ALL
(
  ԸՆԹ՛ՐՈՒԹՅՈՒՆ
    *
  tbl
  ՈՒ
    fk_own IS NULL -- հետո «նիկոլ» 20
  ՈՒ
    pk
  ԺՈՂՈՎ 20
)
ԺՈՂՈՎ 20; -- բայց ընդհանուր - 20, ավելի էլ չի հարկավոր

[տեսնել explain.tensor.ru]

Մենք օգտվեցինք նրանից, որ բոլոր 20 անհրաժեշտ գրառումները արդեն իսկ իսկ առաջին բլոկում ձեռք բերվեցին, հետևաբար երկրորդը, ավելի « թանկ» Bitmap Heap Scan-ով, նույնիսկ չի կատարվել — վերջում 22 անգամ ավելի արագ, 44 անգամ ավելի քիչ ընթերցումներ!

Այս միջոցի ավելի մանրամասն պատմությունը կոնկրետ օրինակների վրա կարող եք կարդալ հոդվածներում PostgreSQL Antipatterns: վնասակար JOIN և OR և PostgreSQL անթույլատրելի օրինակներ: պատմություն անվանման միջոցով որոնման քանակային վերամշակման մասին, թե՝ "Օպտիմացում առաջ և հետ".

Ընդհանուր տարբերակը միավորված ընտրության շուրջ մի քանի բանալիներով (ոչ միայն const/NULL զույգի շուրջ) ուսումնասիրվում է հոդվածում SQL HowTo: գրում ենք while-ցիկլը ուղղակի հարցումներում, կամ «Էլեմենտար եռուղու».

#4: читаем много лишнего

Երբ է առաջանում

Գլխավորում է, երբ ցանկություն կա «դիտեք ևս մեկ քաղվածք» արդեն գոյություն ունեցող հարցմանը:

«Ահա, թե դուք չունեք այդպիսի, բայց Պարկուճներ են, որոնք աչքերի պարունակում են?» ՀՖ «Բրիլլյանտային ձեռքը»

Օրինակ, վերակազմավորելով վերևի խնդիրը, ցուցադրել առաջին 20 ամենահին «կրիտիկական» հայտեր մշակման համար, անկախ դրանց նշանակությունից.

Ինչպես ճշտել

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows 80% կարդացած
   && loops × RRbF > 100 -- և ոչ մի դեպքում ավելի քան 100 գրառումներ ընդհանուր

Հոլովականներ

Ստեղծել [ավելորդ] մասնագիտացված վարկանիշային հասցեավորում ըստ WHERE-պահանջի կամ ընդգրկել ցուցանիշի մեջ լրացուցիչ դաշտեր։

Եթե ֆիլտրի պայմանը «ստատիկ» է ձեր խնդիրների շուրջ՝ այսինքն չի ենթադրում ընդլայնում շարունակ շինությունների ցանկում ապագայում — լավագույնը օգտագործել WHERE-ալիք։ Այս կատեգորիայում նկարագրվում են տարբեր boolean/enum-սաֆիլները։

Եթե սակայն ֆիլտրի պայմանը հնարավոր է ընդունել տարբեր արժեքներ, ապա լավագույնը է տարածել այս դաշտերով — ինչպես BitmapAnd վերևում։

Օրինակ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "մեթոդաբանություններ"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 տարբեր արտաքին բանալիներ
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, որ հայտը "կրիտիկական"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

[տեսնել explain.tensor.ru]

Ստուգենք.

CREATE INDEX ON tbl(pk)
  WHERE critical; -- ավելացրած "ստատիկ" ֆիլտրի պայման

[տեսնել explain.tensor.ru]

Ինչպես տեսնում ենք, պլանից ֆիլտրումը լրիվ բացակայում է, և հարցումը դարձել է 5 անգամ արագ.

#5: разреженная таблица

Երբ է առաջանում

Տարբեր փորձեր սեփական հերթելու առաջացման համար, երբ բազմաթիվ տվյալների թարմացումները/հեռացումները ստեղծում են բոլոր «մահով» գրառումների մեծ քանակություն:

Ինչպես ճշտել

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- կկարդացվի ավելի քան 1KB յուրաքանչյուր գրառման համար
   && shared hit + shared read > 64

Հոլովականներ

Գորել մանավանդ, ձեռքով անցկացնել VACUUM [FULL] կամ հասնել բավարար հաճախություն autovacuum պարամետրերի ճշգրտմամբ, այդ թվում կոնկրետ աղյուսակի համար.

Այսպիսի խնդիրները սովորաբար առաջանում են հարցումների վատ համադրմամբ, երբ բանակցային տրամաքներից դիմողական запросներ իրականացվում են: PostgreSQL անվերահսկվողներին: պայքարում ենք «մահացածների» հորձանքի դեմ.

Բայց պետք է հասկանալ, որ նույնիսկ VACUUM FULL-ը չի կարող օգտակար լինել միշտ։ Այդպիսի դեպքերի համար անհրաժեշտ է ծանոթանալ հոդվածի ալգորիթմին DBA: երբ VACUUM ը անցնում է — մաքրենք աղյուսակը ձեռքով.

#6: чтение с «середины» индекса

Երբ է առաջանում

Վստահություն առաջացնում է կարծես, որ մի քիչ կարդացել ենք, և բոլորի ամբողջությամբ լավ է, ու ոչ մեկին ավելորդ չենք ֆիլտրել — բայց ամեն դեպքում կարդացել է զգալիորեն ավելի էջեր, քան ցանկալի էր:

Ինչպես ճշտել

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- կկարդացվի ավելի քան 1KB յուրաքանչյուր գրառման համար
   && shared hit + shared read > 64

Հոլովականներ

Ամենայն ուշադրություն դարձրեք օգտագործված ինդեքսի կառուցվածքին և հարցման մեջ նշանակված բանալիներին — հավանաբար, ինդեքսի մի մաս չունեցող. Հավանաբար, ձեզ հարկավոր կլինի ստեղծել նմանատիպ ինդեքս, բայց առանց նախնական դաշտերի կամ սովորել դրանք տանել.

Օրինակ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "մեթոդաբանություններ"
, (random() *  100)::integer fk_org  -- 100 տարբեր արտաքին բանալիներ
, (random() * 1000)::integer fk_cli; -- 1K տարբեր արտաքին բանալիներ

CREATE INDEX ON tbl(fk_org, fk_cli); -- բոլորը գրեթե ինչպես #2
-- միայն այդպես մենք արդեն հաշվել էինք, որ առանձնահատուկ ինդեքսը fk_cli-ի համար ավելորդ է և հեռացրել ենք

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- բայց fk_org-ն չի սահմանվել, թեև ինդեքսում ավելի առաջ է
LIMIT 20;

[տեսնել explain.tensor.ru]

Տվում է, թե ամեն ինչ լավ է, նույնիսկ ինդեքսով, բայց ինչ-որSuspiciously — ամեն մեկի 20 կարդալով, անհրաժեշտ էր 4 տվյալների էջ կարդալ, 32KB ամեն գրառման համար — արդյոք դա ավել չի՞: Այստեղ իսթի նայեք հաշվի մեջ tbl_fk_org_fk_cli_idx մտածելու համար:

Ստուգենք.

CREATE INDEX ON tbl(fk_cli);

[տեսնել explain.tensor.ru]

Անսպասելի — 10 անգամ արագ, և 4 անգամ քիչ կարդալով!

Այլ օրինակներ արդյունավետ կրողիների վատ կիրառումներին կարող եք տեսնել հոդվածից DBA: գտնում ենք անօգտակար ինդեքսներ.

#7: CTE × CTE

Երբ է առաջանում

Հարցման մեջ պրոհենց «մնացորդ» CTE տարբեր աղյուսակներից, իսկ հետո որոշեցինք անել նրանց միջև JOIN.

Կեյսը այսօր չի գործում v12-ից ցածր տարբերակների կամ հարցումների հետ WITH MATERIALIZED.

Ինչպես ճշտել

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- չափազանց մեծ դեկարտյան արտ produse CTE

Հոլովականներ

Համաշխարհային ուսումնասիրություն է անհրաժեշտ՝ ինչպես է CTE- ն անհրաժեշտ այստեղ? Если все-таки да, то օգտագործել «հասկացություն» hstore/json- ում ժամանակակից մոդելում, նկարագրված PostgreSQL Antipatterns: խփենք բառարանը ծանր JOIN- ի.

#8: swap на диск (temp written)

Երբ է առաջանում

Միանգամյա մշակում (սորտավորում կամ յուրօրինակացում) մեծ թվով գրառումների մատչելի հիշողությունից դուրս է:

Ինչպես ճշտել

-> *
   && temp written > 0

Հոլովականներ

Եթե գործառնական օգտագործված հիշողությունը միայն զուտ գերազանցում է կարգավորված պարամետի նշանակությունը work_mem, արժե հարմարեցնել: Կարող եք անմիջապես կարգավորումներով՝ բոլորի համար, կամ սարքել SET [LOCAL] հատուկ հարցման/միջին գործաշրջանի համար.

Օրինակ:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[տեսնել explain.tensor.ru]

Ստուգենք.

SET work_mem = '128MB'; -- հարցումը կատարելու առաջ

[տեսնել explain.tensor.ru]

Տեղեկատվություն է, եթե միայն հիշողությունը օգտագործում է, այլ ոչ թե սկավառակը, ապա հարցումը կատարելու ժամանակը շատ ավելի արագ կլինի: Այս դեպքում նաև որոշ ծանրաբեռնվածությունը HDD- ից ենք վերցնում:

Բայց պետք է հասկանալ, որ շատ շատ հիշողություն հատկացնել միշտ հնարավոր չէ - այն պարզապես չի բավարարի բոլորի համար:

#9: неактуальная статистика

Երբ է առաջանում

Տվյալների բազան անմիջապես հրահանգեց շատ, բայց չի հասցրել անցկացնել ANALYZE.

Ինչպես ճշտել

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

Հոլովականներ

Կատարել թերևս ANALYZE.

Ավելին այս իրավիճակը մանրամասն նկարագրված է PostgreSQL Antipatterns: վիճակագրությունը գլխին է.

#10: «что-то пошло не так»

Երբ է առաջանում

Ստացվում է արգելքի սպասում, որը դարձրած է մրցակից հարցումով, կամ կոմպոզիցիաների համակարգի ռեսուրսների անբավարարություն:

Ինչպես ճշտել

-> *
   && (shared hit / 8K) + (shared read / 1K)  100ms -- քիչ ենք ընթերցում, բայց չափազանց երկար

Հոլովականներ

Օգտագործեք արտաքին համակարգ մոնիտորինգի համար սերվերները արգելքների կամ անտիպ ռեսուրսների սպառման առկայության շուրջ: Մեր կողմից կազմակերպելու այս գործընթացի տարբերակներին շուրջ հարյուրավոր սերվերների համար արդեն պատմել ենք այստեղ և այստեղ.

Ընտանիք: habr.com