🥇Рецепти за болни SQL заявки

Преди няколко месеца обявихме explain.tensor.ru — публичен сервиз за анализ и визуализация на плановете на заявките към PostgreSQL.

През това време вече сте го използвали над 6000 пъти, но една от удобните функции вероятно е останала незабелязана — това са структурните подсказки, които изглеждат приблизително така:

Прислушвайте се към тях и вашите заявки "ще станат гладки и копринени". 🙂

А ако бъдем сериозни, много ситуации, които правят заявката бавна и "гладна" за ресурси, са типични и могат да бъдат разпознати по структурата и данните на плана.

В този случай всеки отделен разработчик няма да трябва да търси вариант за оптимизация самостоятелно, разчитайки единствено на своя опит — можем да му подсказваме какво става, каква може да бъде причината и как може да се подходи към решението. Както направихме.

Нека да разгледаме по-подробно тези случаи — как се определят и към какви препоръки водят.

За по-добро потапяне в темата първо можете да чуете съответния блок от моето изложение на 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); -- индекс за foreign key

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

Както предполагахме, намерихме всички 30 записа. Но отне 60% от времето — защото направихме и 30 търсения по индекса. А можем ли да направим по-малко?

Веднага е видно, че по индекса са извадени над 100 записа, които след това са били всички сортирани, а след това е останал само един.

Поправяме:

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

Дори на такова простично извадка — 8.5 пъти по-бързо и с 33 пъти по-малко четения.Ефектът ще бъде по-очевиден, колкото повече "факти" имате за всяко значение fk.

Забелязвам, че такъв индекс ще работи като "префиксен" не по-лошо от предишния и за други заявки с fk, където сортировките по pk не е имало и няма (повече за това можете да прочетете в моята статия за търсене на неефективни индекси). Включително, той ще осигури и нормална поддръжка на явен foreign key по това поле.

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

Кога възниква

Покажете всички договори за клиента «ООО Колокольчик», сключени от името на «НАО Лютик».

Как да идентифицираме

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

Препоръки

Създаване съставен индекс по полетата от и двете изходни, или разширете едно от съществуващите с полета от второто.

Пример:

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); -- отбор по конкретна двойка

Поправяме:

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

Тук печалбата е по-малко, тъй като Bitmap Heap Scan е достатъчно ефективен сам по себе си. Но все пак в 7 пъти по-бързо и с 2.5 пъти по-малко четения.

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

Кога възниква

Покажете първите 20 най-стари «свои» или ненасочени заявки за обработка, като свои в приоритет.

Как да идентифицираме

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

Препоръки

Да използваме UNION [ALL] за обединяване на подзаявки по всеки от OR-блоковете на условията.

Пример:

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;

Поправяме:

(
  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, повече и не е нужно

Възползвахме се от това, че всичките 20 нужни записа бяха веднага получени в първия блок, поради което вторият, с по-«скъп» Bitmap Heap Scan, дори не беше изпълнен — в резултат в 22 пъти по-бързо, в 44 пъти по-малко четения!

По-подробен разказ за този метод на оптимизация на конкретни примери може да се прочете в статиите PostgreSQL Antipatterns: вредни JOIN и OR и PostgreSQL Antipatterns: приказка за итеративното доработване на търсенето по наименование, или «Оптимизация напред и назад».
Обобщен вариант на подреден отбор по няколко ключа (а не само по двойка 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 по-горе.

Пример:

СЪЗДАДИ СТОЛ tbl AS
ИЗБЕРИ
  generate_series(1, 100000) pk -- 100K "факти"
, СЛУЧАЙ
    КОГАТО random() < 1::real/16 ТОГАВА NULL
    ИНАЧЕ (random() * 100)::integer -- 100 различни външни ключа
  КРАЙ fk_own
, (random() < 1::real/50) critical; -- 1:50, че заявката е "критична"

СЪЗДАЙ ИНДЕКС НА tbl(pk);
СЪЗДАЙ ИНДЕКС НА tbl(fk_own, pk);

ИЗБЕРИ
  *
ОТ
  tbl
КЪДЕ
  critical
НАРЕДИ ПО
  pk
ОГРАНИЧИ 20;

Поправяме:

СЪЗДАЙ ИНДЕКС НА tbl(pk)
  КЪДЕ critical; -- добавихме "статично" условие за филтриране

Както виждаме, филтрирането от плана напълно изчезна, а заявката стана в 5 пъти по-бърза.

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

Кога възниква

Разнообразни опити за създаване на собствена опашка за обработка на задачи, когато голям брой актуализации/изтривания на записи в таблицата водят до ситуация с много "мъртви" записи.

Как да идентифицираме

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF)  64

Препоръки

Редовно ръчно провеждане на VACUUM [FULL] или достигане на адекватно често изпълнение autovacuum чрез прецизно настройване на неговите параметри, в това число за конкретната таблица.

В повечето случаи подобни проблеми се оказват предизвикани от лошо компониране на заявки при повиквания с бизнес логика като тези, които бяха обсъдени в PostgreSQL Антипатерни: борба с ордите на „мъртвите“..
Но трябва да се разбере, че дори VACUUM FULL не може да помогне винаги. За такива случаи е добре да се запознаете с алгоритъма от статията DBA: когато VACUUM не помага — чистим таблицата ръчно.

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

Кога възниква

Изглежда, че сме прочели малко, всичко е по индекса и никого ненужно не сме филтрирали — а все пак прочетените страници са значително повече, отколкото бихме желали.

Как да идентифицираме

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF)  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;

Изглежда всичко е наред, дори по индекса, но по някакъв начин е подозрително - на всяка от 20-те прочетени записи се наложи да се извлекат по 4 страници данни, 32KB на запис - не е ли твърде много? И името на индекса tbl_fk_org_fk_cli_idx предизвиква размисли.

Поправяме:

CREATE INDEX ON tbl(fk_cli);

Неочаквано - 10 пъти по-бързо, и 4 пъти по-малко четене!

Други примери за ситуации с неефективно използване на индекси можете да видите в статията DBA: откриваме безполезни индекси.

#7: CTE × CTE

Кога възниква

В запитването събрахме "дебели" CTE от различни таблици, а след това решихме да направим между тях JOIN.

Случаят е актуален за версии под v12 или запитвания с WITH MATERIALIZED.

Как да идентифицираме

-> CTE Scan
   && цикли > 10
   && цикли × (редове + RRbF) > 10000
      -- твърде голямо декартово произведение CTE

Препоръки

Внимателно анализирайте запитването - а нужни ли са изобщо CTE тук? Если все-таки да, то да приложите "ословоряне" в hstore/json по модела, описан в PostgreSQL Антипатерни: печатиме зборникот против тешките 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;

Поправяме:

SET work_mem = '128MB'; -- преди изпълнението на запитването

По разбираеми причини, ако се използва само памет, а не диск, запитването също ще се изпълнява много по-бързо. В същото време част от натоварването от HDD се отстранява.

Но трябва да разберете, че не може винаги да се отделя много-много памет - просто няма да стигне за всички.

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

Кога възниква

В базата данни бяха вкарани много записи, но не успяха да преминат ANALYZE.

Как да идентифицираме

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

Препоръки

Да проведем все пак ANALYZE.

По-подробно, ситуация е описана в PostgreSQL Antipatterns: статистиката е всичко.

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

Кога възниква

Случи се чакане на блокировка, наложена от конкурентна заявка, или липсваха хардуерни ресурси CPU/хипервизор.

Как да идентифицираме

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- четенето беше малко, но отне твърде много време

Препоръки

Използвайте външна система за мониторинг на сървъри за наличие на блокировки или ненормално потребление на ресурси. За нашия подход към организиране на този процес за стотици сървъри вече говорихме тук и тук.

Източник: habr.com

Рецепти за боледуващи SQL заявки

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

Кога възниква

Как да идентифицираме

Препоръки

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

Кога възниква

Как да идентифицираме

Препоръки

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

Кога възниква

Как да идентифицираме

Препоръки

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

Кога възниква

Как да идентифицираме

Препоръки

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

Кога възниква

Как да идентифицираме

Препоръки

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

Кога възниква

Как да идентифицираме

Препоръки

#7: CTE × CTE

Кога възниква

Как да идентифицираме

Препоръки

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

Кога възниква

Как да идентифицираме

Препоръки

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

Кога възниква

Как да идентифицираме

Препоръки

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

Кога възниква

Как да идентифицираме

Препоръки