Բաղադրատոմսեր հիվանդ SQL հարցումների համար

Ամիսներ առաջ մենք հայտարարեցինք բացատրել.tensor.ru - հանրային Ծառայություն հարցումների պլանների վերլուծության և պատկերացման համար դեպի PostgreSQL:

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

Լսեք նրանց, և ձեր խնդրանքները «մետաքսյա հարթ կդառնան»: 🙂

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

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

Եկեք մանրամասն նայենք այս դեպքերին, թե ինչպես են դրանք սահմանվում և ինչ առաջարկությունների են հանգեցնում:

Թեմայի մեջ ավելի լավ ընկղմվելու համար նախ կարող եք լսել համապատասխան բլոկը իմ զեկույցը PGConf.Russia 2020-ում, և միայն դրանից հետո անցեք յուրաքանչյուր օրինակի մանրամասն վերլուծությանը.

# 1: ինդեքս «անտեսակավորում»

Երբ է անում

Ցույց տալ վերջին հաշիվը հաճախորդի «Կոլոկոլչիկ» ՍՊԸ-ի համար:

Ինչպես բացահայտել

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

Առաջարկություններ

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

Example:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

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

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

Մենք ուղղում ենք.

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

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

Նույնիսկ նման պարզունակ նմուշի վրա - 8.5 անգամ ավելի արագ և 33 անգամ ավելի քիչ ընթերցումներ. Էֆեկտն ավելի պարզ կլինի, այնքան ավելի շատ «փաստ» ունես յուրաքանչյուր արժեքի համար։ fk.

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

#2. ինդեքսային խաչմերուկ (BitmapAnd)

Երբ է անում

Ցույց տալ «Լյուտիկ» ՓԲԸ-ի անունից կնքված «Կոլոկոլչիկ» ՍՊԸ-ի պատվիրատուի բոլոր պայմանագրերը:

Ինչպես բացահայտել

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

Առաջարկություններ

ստեղծել կոմպոզիտային ինդեքս ըստ դաշտերի երկու աղբյուրից կամ ընդլայնել առկա դաշտերից մեկը երկրորդից:

Example:

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); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

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

Մենք ուղղում ենք.

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

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

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

# 3. Համատեղելով ինդեքսները (BitmapOr)

Երբ է անում

Ցույց տվեք մշակման առաջին 20 ամենահին «սեփական» կամ չնշանակված հարցումները՝ առաջնահերթությամբ սեփականը:

Ինչպես բացահայտել

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

Առաջարկություններ

Օգտագործման համար ՄԻՈՒԹՅՈՒՆ [ԲՈԼՈՐ] պայմանի OR բլոկներից յուրաքանչյուրի համար ենթհարցումները համատեղելու համար:

Example:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

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

Մենք ուղղում ենք.

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

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

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

Ավելի մանրամասն պատմություն այս օպտիմալացման մեթոդի մասին կոնկրետ օրինակների վրա կարելի է կարդալ հոդվածներում PostgreSQL հակապատկերներ. վնասակար միացումներ և OR-ներ и PostgreSQL հակապատկերներ. Անունով որոնման կրկնվող ճշգրտման պատմություն կամ «Առաջ ու առաջ օպտիմիզացում».

Ընդհանրացված տարբերակ պատվիրված ընտրություն մի քանի ստեղներով (և ոչ միայն const/NULL զույգի համար) քննարկվում է հոդվածում SQL HowTo. գրել while-loop ուղղակիորեն հարցումում կամ «Elementary three-way».

#4. Մենք չափից շատ ենք կարդում

Երբ է անում

Որպես կանոն, դա տեղի է ունենում, երբ դուք ցանկանում եք «կցել մեկ այլ զտիչ» առկա հարցմանը:

«Եվ դուք չունեք նույնը, բայց մարգարիտ կոճակներով? " «Ադամանդե ձեռք» ֆիլմը

Օրինակ, վերը նշված առաջադրանքը փոփոխելով, ցուցադրեք մշակման առաջին 20 ամենահին «կարևոր» հարցումները՝ անկախ դրանց նպատակից:

Ինչպես բացահայտել

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

Առաջարկություններ

Ստեղծել [ավելի] մասնագիտացված ինդեքս WHERE կետով կամ ներառել լրացուցիչ դաշտեր ինդեքսում:

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

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

Example:

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;

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

Մենք ուղղում ենք.

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[նայեք բացատրություն.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

Առաջարկություններ

Պարբերաբար կատարել ձեռքով ՎԱԿՈՒՈՒՄ [ԼԻՎԱԾ] կամ հասնել բավական հաճախակի վերամշակման ավտովակուում ճշգրտելով դրա պարամետրերը, ներառյալ կոնկրետ սեղանի համար.

Շատ դեպքերում, նման խնդիրներն առաջանում են հարցումների վատ դասավորության պատճառով, երբ կանչվում են բիզնես տրամաբանությունից, ինչպիսիք են քննարկվածները. PostgreSQL Antipatterns. «մահացածների» ոհմակների կռիվ.

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

#6՝ ինդեքսի «միջին» կետից ընթերցում

Երբ է անում

Թվում է, թե նրանք մի փոքր կարդացել են, և ամեն ինչ ինդեքսավորվել է, և նրանք լրացուցիչ որևէ մեկին չեն զտել, բայց այնուամենայնիվ, զգալիորեն ավելի շատ էջեր են կարդացվել, քան մենք կցանկանայինք:

Ինչպես բացահայտել

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Առաջարկություններ

Ուշադիր նայեք օգտագործված ինդեքսի կառուցվածքին և հարցման մեջ նշված հիմնական դաշտերին, ամենայն հավանականությամբ, ինդեքսային մասը սահմանված չէ. Դուք, ամենայն հավանականությամբ, պետք է ստեղծեք նմանատիպ ինդեքս, բայց առանց նախածանցի դաշտերի, կամ սովորել կրկնել իրենց արժեքները.

Example:

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;

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

Թվում է, թե ամեն ինչ լավ է, նույնիսկ ինդեքսի առումով, բայց ինչ-որ տեղ կասկածելի. կարդացած 20 գրառումներից յուրաքանչյուրի համար պետք էր հանել 4 էջ տվյալներ, 32 ԿԲ մեկ ռեկորդում, համարձակ չէ՞: Այո և ինդեքսի անվանումը tbl_fk_org_fk_cli_idx հանգեցնում է մտքի.

Մենք ուղղում ենք.

CREATE INDEX ON tbl(fk_cli);

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

Հանկարծ - 10 անգամ ավելի արագ և 4 անգամ ավելի քիչ կարդալու համար!

Ինդեքսների անարդյունավետ օգտագործման այլ օրինակների համար տե՛ս հոդվածը DBA. գտնել անօգուտ ինդեքսներ.

#7՝ CTE × CTE

Երբ է անում

Խնդրանքով վաստակեց «չաղ» CTE տարբեր սեղաններից, իսկ հետո որոշեց անել նրանց միջև JOIN.

Գործը տեղին է v12-ի ստորև ներկայացված տարբերակների կամ հետ հարցումների համար WITH MATERIALIZED.

Ինչպես բացահայտել

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

Առաջարկություններ

Ուշադիր վերլուծեք հարցումը այստեղ ընդհանրապես անհրաժեշտ են CTE-ներ? Եթե ​​այո, ապա կիրառել «բառարան» hstore/json-ում ըստ նկարագրված մոդելի PostgreSQL Antipatterns. Dictionary Hit Heavy ՄԻԱՑԵՔ.

# 8. փոխանակում սկավառակի վրա (գրված ջերմաստիճան)

Երբ է անում

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

Ինչպես բացահայտել

-> *
   && temp written > 0

Առաջարկություններ

Եթե ​​գործողության կողմից օգտագործվող հիշողության ծավալը մեծապես չի գերազանցում պարամետրի սահմանված արժեքը work_mem, այն պետք է ուղղել։ Դուք կարող եք անմիջապես կազմաձևել բոլորի համար, կամ կարող եք անցնել SET [LOCAL] կոնկրետ հարցման/գործարքի համար։

Example:

SHOW work_mem;
-- "16MB"

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

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

Մենք ուղղում ենք.

SET work_mem = '128MB'; -- перед выполнением запроса

[նայեք բացատրություն.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) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

Առաջարկություններ

Օգտագործեք արտաքին մոնիտորինգի համակարգ սերվեր՝ ռեսուրսների արգելափակման կամ աննորմալ սպառման համար: Մենք արդեն խոսել ենք հարյուրավոր սերվերների համար այս գործընթացը կազմակերպելու մեր տարբերակի մասին։ այստեղ и այստեղ.

Source: www.habr.com