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

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

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

Слушайте ги и молбите ви ще „станат гладки и копринени“. 🙂

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

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

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

За да се потопите по-добре в темата, можете първо да чуете съответния блок от моят доклад на PGConf.Russia 2020и едва след това преминете към подробен анализ на всеки пример:

#1: индекс „недостатъчно сортиране“

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

Показване на последната фактура за клиента "LLC Kolokolchik".

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

-> 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;

[вижте expand.tensor.ru]

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

Коригиране:

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

[вижте expand.tensor.ru]

Дори на такава примитивна проба - 8.5 пъти по-бързо и 33 пъти по-малко четения. Колкото повече „факти“ имате за всяка стойност, толкова по-очевиден е ефектът fk.

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

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

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

Покажете всички споразумения за клиента “LLC Kolokolchik”, сключени от името на “NAO Buttercup”.

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

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

[вижте expand.tensor.ru]

Коригиране:

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

[вижте expand.tensor.ru]

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

#3: Обединяване на индекси (BitmapOr)

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

Покажете първите 20 най-стари „нас“ или неразпределени заявки за обработка, като вашата е с приоритет.

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

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

препоръки

употреба СЪЮЗ [ВСИЧКИ] за комбиниране на подзаявки за всеки от ИЛИ-блоковете от условия.

Пример:

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;

[вижте expand.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, больше и не надо

[вижте expand.tensor.ru]

Възползвахме се от факта, че всички 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 < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

препоръки

Създайте [още] специализирани индекс с WHERE условие или включете допълнителни полета в индекса.

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

Ако условието за филтриране могат да приемат различни значения, тогава е по-добре да разширите индекса с тези полета - както в ситуацията с 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;

[вижте expand.tensor.ru]

Коригиране:

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

[вижте expand.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

препоръки

Погледнете внимателно структурата на използвания индекс и ключовите полета, посочени в заявката - най-вероятно част от индекса не е посочена. Най-вероятно ще трябва да създадете подобен индекс, но без префиксните полета или научете се да повтаряте техните ценности.

Пример:

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;

[вижте expand.tensor.ru]

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

Коригиране:

CREATE INDEX ON tbl(fk_cli);

[вижте expand.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: нека ударим тежкия JOIN с речник.

#8: размяна на диск (временно записано)

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

Еднократната обработка (сортиране или уникализация) на голям брой записи не се вписва в паметта, разпределена за това.

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

-> *
   && temp written > 0

препоръки

Ако обемът на паметта, използвана от операцията, не надвишава много определената стойност на параметъра work_mem, струва си да го коригираме. Можете веднага в конфигурацията за всички или можете през SET [LOCAL] за конкретна заявка/транзакция.

Пример:

SHOW work_mem;
-- "16MB"

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

[вижте expand.tensor.ru]

Коригиране:

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

[вижте expand.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 -- читали мало, но слишком долго

препоръки

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

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