Плани виконання в MariaDB і PostgreSQL: знаходьте реальне вузьке місце, а не симптоми

Практична ментальна модель: плани — це гіпотези

Розглядайте план виконання так само, як хронологію інциденту. Це доказ, а не вирок. Як у MariaDB, так і в PostgreSQL оптимізатори вибирають плани на основі моделі вартості. Моделі вартості залежать від статистик, конфігураційних припущень і деталей реалізації. Вони часто праві. Іноді вони кумедно помиляються, і роблять це у найпередбачуваніший спосіб: зміна розподілу даних, зміна параметрів або зміна конкурентності.

Робочий процес, який реально працює в продакшні, має три рівні:

1. Підтвердіть симптом: час виконання, частота, рівень конкурентності і що означає «повільно» (p95? p99? wall time? CPU time?).
2. Знайдіть ресурсне вузьке місце: CPU, I/O, пам’ять, блокування, мережа або щось поза базою даних.
3. Використайте план, щоб пояснити вузьке місце: а не навпаки.

Якщо ви починаєте з плану і шукаєте рядок, який «потрібно полагодити», ви часто лікуєте симптоми. Ви також впровадите «покращення продуктивності», що підвищить ризик: новий індекс — надмірність, більша ампліфікація записів або регресія плану в понеділок.

Парафразована ідея (приписують): Werner Vogels давно наполягав, що «все ламається, проектуйте з урахуванням цього». Та сама установка застосовується до планів виконання: припускайте, що іноді вони помилкові, і створюйте запобіжні механізми.

Цікаві факти й історія (бо це пояснює сьогоднішні дивності)

Деякі загадки продуктивності стають зрозумілішими, якщо знати, звідки походять рушії. Ось конкретні контексти, які мають значення, коли ви читаєте плани під час тиску:

• Оптимізатор PostgreSQL сильно орієнтований на статистику, і він із задоволенням вибере «правильний» план, який буде невірним для ваших параметрів, якщо ви не примусите кращі оцінки селективності.
• MariaDB успадкувала словник EXPLAIN від MySQL («Using temporary», «Using filesort»), який описує тактики реалізації більше, ніж корінні причини. Це корисно, але не повна діагностика.
• InnoDB давно став дефолтним рушієм MySQL, і MariaDB зберегла цю лінію. Багато «тонкого налаштування запитів» насправді — це поведінка буферного пулу InnoDB під іншою назвою.
• Postgres має MVCC в основі; «bloat» і поведінка vacuum можуть різко змінювати I/O-шаблони без зміни тексту запиту.
• Postgres підтримує кілька типів індексів (B-tree, GIN, GiST, BRIN, hash). Це означає, що «додати індекс» — не єдине рішення; це архітектурний вибір.
• MariaDB має optimizer trace і прапорці перемикання, які можна вмикати на сесію, що чудово для експериментів і погано для консистентності, якщо їх зловживають.
• Postgres поступово ввів паралельні запити; якщо у вашому плані раптом з’явилися workers, ви можете вимірювати планування CPU та тиск пам’яті, а не «швидкість SQL».
• MySQL/MariaDB історично покладалися на nested loop join як базову стратегію, тоді як Postgres має ширший набір стратегій з’єднань і охоче вибере їх, якщо статистики підказують.
• Postgres може кешувати generic plan для підготовлених заяв, що чудово, поки селективність параметрів не змінюється суттєво.

Жарт №1: Оптимізатор як впевнений колега: завжди має план і завжди впевнений, що він правий.

Швидкий план діагностики (перші/другі/треті перевірки)

Коли продакшн горить, а дашборд CEO холодний, у вас немає часу на філософський аналіз плану. Вам потрібна коротка послідовність, яка швидко знайде реальне вузьке місце і не дозволить «фіксам» робити гірше.

Перше: визначте, чи запит чекає, чи працює

• Чекає: очікування блокувань, I/O-очікування, мережеві очікування, черга на CPU, затримки контрольних точок.
• Працює: спалює CPU, читає багато сторінок, сортує, хешує, матеріалізує проміжні результати.

Друге: перевірте конкурентність і contention

• Скільки сесій виконує той самий запит?
• Чи був thundering herd після релізу або закінчення кешу?
• Чи писання блокує читання (або навпаки) через обсяг транзакції?

Третє: підтвердіть, що план відповідає реальності

• Postgres: порівняйте EXPLAIN (ANALYZE, BUFFERS) — оцінені рядки проти фактичних та кількість буферних читань.
• MariaDB: використайте EXPLAIN FORMAT=JSON, перевірте вибрані індекси та підтвердіть лічильники handler read.

Четверте: оберіть важіль

• Перепис запиту (відсікати ряди раніше, уникати неявних приведень типів, уникати функцій на індексованих колонках).
• Зміна індекса (правильний порядок ключів, частковий/функціональний індекс у Postgres, складовий індекс замість одиночного).
• Статистика і обслуговування (ANALYZE, гістограми в MariaDB, VACUUM у Postgres).
• Операційні обмежувачі (connection pool, work_mem/tmp_table_size, обмеження IOPS, шумний сусід).

MariaDB vs PostgreSQL: що план каже (і чого не каже)

Плани PostgreSQL: явні, вимірювані й іноді оманливі

Postgres дає вам дерево виконання з вартісними оцінками, оцінками рядків і (з ANALYZE) фактичним часом і кількістю рядків на вузол. Це золото. Воно також спокушає людей трактувати план як детермінований факт. Це не так.

У Postgres найцінніше порівняння — це оцінені рядки проти фактичних рядків. Коли вони різко розходяться, оптимізатор приймає рішення на підставі поганих вхідних даних: застарілих статистик, кореляцій, які він не може змоделювати, селективності параметрів або перекосу даних. Саме тут план стає крихтами, що ведуть до реального вузького місця.

Ще одна суперсила Postgres — це BUFFERS. Це показує, чи запит потрапляє в кешовані сторінки, чи читає зі сховища. «Повільний запит» плюс багато shared read buffers — це I/O-історія. «Повільний запит» з небагато читань, але високим CPU — це історія обчислень (або блокування, що маскуються під обчислення).

Плани MariaDB: прагматичні, з відкритими внутрішніми механізмами оптимізатора

Традиційний вивід EXPLAIN у MariaDB компактний: порядок з’єднань, вибраний індекс і прапорці на кшталт «Using where», «Using temporary», «Using filesort». Ці прапорці не є моральними оцінками. Вони — підказки реалізації. «Using filesort» не означає «сортування на диску». Це означає, що використовується алгоритм filesort, який може або не може відкачувати на диск.

Для серйозної роботи краще використовувати EXPLAIN FORMAT=JSON і — коли треба знати, чому план обрано — OPTIMIZER_TRACE. MariaDB може показати розглянуті альтернативи та рішення по вартості. Саме так ви ловите випадки, коли рушій обрав план тому, що вважав фільтр вибірковим, а він таким не був.

Що спільного в обох рушіях: звичні підозрювані

В обох — MariaDB і Postgres — більшість «проблем з планом» належать до короткого списку:

• Погані оцінки кардинальності: перекоси, кореляція, застарілі статистики, селективність, що залежить від параметрів.
• Неправильний шлях доступу: index scan, коли має бути sequential scan (так, буває), або навпаки.
• Невідповідна стратегія з’єднань: nested loops у великий таблиці, hash join що відкачується, merge join що забагато сортує.
• Spill для сорту/хешу: налаштування пам’яті занадто малі або конкурентність занадто висока.
• Блокування: «повільний запит» не є повільним — він просто заблокований.
• Затримка сховища: плани виглядають добре; диск — ні.

Практичні завдання: команди, виходи та рішення (12+)

Це перевірені в полі ходи. Кожне завдання містить виконувану команду, приклад виходу, що це означає, і яке рішення прийняти далі. Використовуйте їх як набір інструментів, а не як догму.

Завдання 1 (Postgres): знайти сесії й чи вони чекають

cr0x@server:~$ psql -d appdb -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS age, left(query,120) AS q FROM pg_stat_activity WHERE datname='appdb' AND state<>'idle' ORDER BY age DESC LIMIT 10;"
 pid  | usename | state  | wait_event_type | wait_event |   age    | q
------+--------+--------+-----------------+------------+----------+----------------------------------------------------------
 8421 | app    | active | Lock            | relation   | 00:01:12 | UPDATE orders SET status='paid' WHERE id=$1
 9110 | app    | active | IO              | DataFileRead | 00:00:43 | SELECT ... FROM order_items WHERE order_id=$1

Значення: Один запит заблокований на relation lock, інший чекає на читання.

Рішення: Якщо це Lock, не тюньте запит — знайдіть блокувальника. Якщо це IO, перевірте затримку сховища і кількість буферних читань перед додаванням індекса.

Завдання 2 (Postgres): ідентифікувати блокувальників і жертв

cr0x@server:~$ psql -d appdb -c "SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query, blocking.query AS blocking_query FROM pg_locks blocked_locks JOIN pg_stat_activity blocked ON blocked.pid = blocked_locks.pid JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;"
 blocked_pid | blocking_pid |         blocked_query         |         blocking_query
------------+--------------+-------------------------------+----------------------------------
       8421 |         8333 | UPDATE orders SET status=...  | ALTER TABLE orders ADD COLUMN...

Значення: DDL блокує DML. Це не проблема плану запиту.

Рішення: Зупиніть DDL або перенесіть його в безпечне вікно/спосіб. Потім поверніться до початкового запиту, якщо він досі повільний.

Завдання 3 (Postgres): отримати виміряний план з активністю буферів

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM order_items WHERE order_id=12345;"
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using order_items_order_id_idx on public.order_items  (cost=0.43..42.10 rows=12 width=128) (actual time=0.085..12.611 rows=9800 loops=1)
   Output: id, order_id, sku, qty, price
   Index Cond: (order_items.order_id = 12345)
   Buffers: shared hit=12 read=8102
 Planning Time: 0.214 ms
 Execution Time: 13.004 ms

Значення: Індекс використовується, але читає тисячі сторінок з диску. Предикат не надто селективний або таблиця роздута/не кластеризована.

Рішення: Розгляньте покривний індекс (include), зменшення кількості вибраних колонок або боротьбу з bloat. Також перевірте I/O-затримку.

Завдання 4 (Postgres): порівняти оцінки й фактичні рядки, щоб помітити проблеми статистик

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE) SELECT * FROM users WHERE email LIKE '%@example.com';"
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..18250.00 rows=100 width=256) (actual time=0.040..210.221 rows=48210 loops=1)
   Filter: (email ~~ '%@example.com'::text)
   Rows Removed by Filter: 151790
 Planning Time: 0.251 ms
 Execution Time: 213.904 ms

Значення: Оцінка (100) сильно відрізняється від фактичних (48k). Також ведучий дефіс у шаблоні вимикає стандартний B-tree індекс.

Рішення: Якщо цей запит важливий, переробіть предикат (зберігайте домен окремо) або використайте триграм-індекс (розширення Postgres). Не звинувачуйте тут «seq scan»; звинувачуйте предикат.

Завдання 5 (Postgres): перевірити, чи відбуваються тимчасові файли (spill)

cr0x@server:~$ psql -d appdb -c "SELECT datname, temp_files, temp_bytes FROM pg_stat_database WHERE datname='appdb';"
 datname | temp_files | temp_bytes
---------+------------+------------
 appdb   |       1821 |  987654321

Значення: БД відкачує на диск сорти/хеші або має великі проміжні результати. Часто це історія work_mem і конкурентності.

Рішення: Знайдіть запити, що роблять великі сорти/хеші; тонко налаштуйте пам’ять на сесію або перепишіть запит, щоб зменшити рядки раніше. Не підвищуйте work_mem глобально бездумно.

Завдання 6 (Postgres): подивитися найбільших споживачів часу (потребує pg_stat_statements)

cr0x@server:~$ psql -d appdb -c "SELECT left(query,100) AS q, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
                         q                         | calls | total_exec_time | mean_exec_time |  rows
---------------------------------------------------+-------+-----------------+----------------+-------
 SELECT ... FROM order_items WHERE order_id=$1      | 92000 |       812345.12 |          8.83  | 901234
 SELECT ... FROM users WHERE email LIKE $1          |  5000 |       401122.88 |         80.22  |  48210

Значення: Не всі повільні запити важливі. Загальний час показує, що спалює ваш бюджет.

Рішення: Почніть з топа за загальним часом (і p95/p99 з вашого APM). Оптимізуйте там, де це дає найбільший ефект, а не там, де соромно дивитися.

Завдання 7 (MariaDB): знайти активні потоки і на що вони чекають

cr0x@server:~$ mariadb -e "SHOW FULL PROCESSLIST;"
Id	User	Host	db	Command	Time	State	Info
231	app	10.0.2.41:51012	appdb	Query	65	Waiting for table metadata lock	ALTER TABLE orders ADD COLUMN note TEXT
244	app	10.0.2.77:52910	appdb	Query	42	Statistics	SELECT ... FROM order_items WHERE order_id=12345

Значення: Metadata lock блокує інші. Зверніть увагу на стани «Statistics» або інші стани, які можуть вказувати на роботу оптимізатора або поведінку рушія зберігання.

Рішення: Вирішіть metadata lock перш за все. Потім виміряйте виконання запиту; зміни плану не виправлять заблоковані потоки.

Завдання 8 (MariaDB): показати план у JSON (детальніше, ніж табличний EXPLAIN)

cr0x@server:~$ mariadb -e "EXPLAIN FORMAT=JSON SELECT * FROM order_items WHERE order_id=12345\G"
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "order_items",
      "access_type": "ref",
      "possible_keys": ["order_id_idx"],
      "key": "order_id_idx",
      "key_length": "8",
      "rows": 12000,
      "filtered": 100,
      "attached_condition": "(`order_items`.`order_id` = 12345)"
    }
  }
}

Значення: MariaDB очікує ~12k рядків. Якщо фактичних 9.8k — добре. Якщо 900k — статистики брешуть або предикат не той, який ви думаєте.

Рішення: Підтвердіть кількість рядків цільовим запитом і освіжіть статистики при потребі.

Завдання 9 (MariaDB): увімкнути optimizer trace, щоб побачити, чому обрано план

cr0x@server:~$ mariadb -e "SET optimizer_trace='enabled=on'; SELECT * FROM order_items WHERE order_id=12345; SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G"
TRACE
{
  "steps": [
    {
      "join_optimization": {
        "table_dependencies": [
          {"table": "order_items", "row_may_be_null": false}
        ],
        "rows_estimation": [
          {"table": "order_items", "range_analysis": {"chosen_range_access_summary": "ref on order_id_idx"}}
        ]
      }
    }
  ]
}

Значення: Ви бачите розглянуті альтернативи. Так ви доводите «оптимізатор вірив X».

Рішення: Якщо вибір оптимізатора ґрунтується на помилковій оцінці, виправте статистики/розподіл (гістограми) або перепишіть запит. Не починайте з примусового вибору індекса як першого кроку.

Завдання 10 (MariaDB): перевірити handler reads, щоб помітити скани таблиць і випадковий I/O

cr0x@server:~$ mariadb -e "SHOW SESSION STATUS LIKE 'Handler_read%';"
Variable_name	Value
Handler_read_first	0
Handler_read_key	12045
Handler_read_next	980000
Handler_read_rnd_next	4500000

Значення: Багато Handler_read_rnd_next часто вказує на повні скани таблиць. Багато read_next може означати range scan, що проходить багато рядків.

Рішення: Якщо скани великі, зменшіть рядки раніше (кращі предикати, кращі індекси) або прийміть скан, якщо він дешевший за випадковий I/O по поганому індексу.

Завдання 11 (Система): підтвердити, чи хост обмежений I/O

cr0x@server:~$ iostat -x 1 3
Linux 6.1.0 (db01) 	12/30/2025 	_x86_64_	(8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.10    0.00    6.20   31.50    0.00   50.20

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         820.0   240.0 65440.0 18200.0  18.2   0.8   98.0

Значення: Високий %iowait, високий await і %util близько 100%: сховище насичене. Ваш план може бути «добрим», але все одно повільним.

Рішення: Зменшіть читання (покривні індекси, менше колонок, кращі фільтри), виправте bloat або додайте IOPS. Якщо ви продовжите тюнити SQL, поки диск завантажений, ви просто перестановляєте меблі під час пожежної тривоги.

Завдання 12 (Система): перевірити чергу запуску CPU і насичення

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 8  0      0 512000  12000 980000    0    0   200   500 1200 2400 55 10 30  5  0
 9  0      0 508000  12000 978000    0    0   180   420 1180 2500 58 12 25  5  0

Значення: Черга запуску r на рівні або вище кількості CPU і високий us означають насичення CPU. Низький wa означає, що це не переважно I/O-очікування.

Рішення: Шукайте дорогі функції, занадто багато паралельних worker’ів (Postgres), «гарячі» цикли або відсутні індекси, що спричиняють CPU-важку фільтрацію. Також перевірте шифрування/стиснення та наплив з’єднань.

Завдання 13 (Postgres): перевірити стан autovacuum/vacuum і сигнали bloat

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
  relname    | n_dead_tup | n_live_tup | last_vacuum | last_autovacuum     | last_analyze        | last_autoanalyze
------------+------------+------------+-------------+----------------------+---------------------+------------------------
 order_items |    812345  |   9023410  |             | 2025-12-30 09:12:01  | 2025-12-30 08:05:10 | 2025-12-30 09:12:10

Значення: Багато мертвих кортежів: ризик bloat, більше сторінок для читання, гірше кешування, повільніші індексні скани.

Рішення: Налаштуйте autovacuum для гарячих таблиць, розгляньте VACUUM (або стратегію перезапису), якщо відстаєте. Плани запитів не врятують таблицю, яка фактично вдвічі більша за корисний розмір.

Завдання 14 (MariaDB/InnoDB): перевірити тиск на buffer pool і читання

cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';"
Variable_name	Value
Innodb_buffer_pool_read_requests	987654321
Innodb_buffer_pool_reads	12345678
Variable_name	Value
Innodb_buffer_pool_pages_total	1048576
Innodb_buffer_pool_pages_free	1024

Значення: Дуже мало вільних сторінок і нетривіальні фізичні читання можуть означати, що робочий набір не поміщається. Під навантаженням читання потраплятимуть на диск.

Рішення: Зменшіть робочий набір (видаліть непотрібні індекси, звузьте вибірку), збільште buffer pool, якщо безпечно, або виправте запит, щоб не торкатися стільки сторінок.

Завдання 15 (Postgres): перевірити, чи кеш плану підготовленого запиту вам шкодить

cr0x@server:~$ psql -d appdb -c "SHOW plan_cache_mode;"
 plan_cache_mode
-----------------
 auto

Значення: У режимі auto Postgres може вибирати generic plan для підготовлених запитів, коли вважає це загалом дешевшим.

Рішення: Якщо підозрюєте чутливість до параметрів, протестуйте з SET plan_cache_mode = force_custom_plan; у сесії і порівняйте продуктивність. Якщо це допомагає, виправте на рівні додатку/драйвера або структури запиту.

Три корпоративні історії з поля бою

1) Інцидент від неправильної гіпотези: «Seq Scan завжди погано»

Середня компанія мала аналітичну сторінку на Postgres. On-call отримав повідомлення: p95 ендпойнта з “добре” став “проблемним”, і CPU бази даних підскочив. Розробник кинув EXPLAIN з послідовним сканом по помірно великій таблиці і оголосив перемогу: «Потрібен індекс».

Вони додали складений індекс, що відповідав WHERE і ORDER BY. Записи стали повільнішими негайно. У стенді запит став швидшим. У продакшні p95 ледь покращився, а CPU залишився гарячим. Між тим, побудова індекса конкурувала за I/O і ускладнила інцидент.

Реальною проблемою була не скан. Це була конкурентність: після деплою ендпойнт почав викликатися двічі на перегляд сторінки через баг у фронтенді. Запит виконував розумний sequential scan, бо таблиця була досить мала, предикат не селективний і кешовані сторінки робили скан дешевим — поки кеш не зник через подвоєне навантаження.

Виправлення багу фронтенду знизило QPS удвічі. «Покращувальний» індекс став технічним боргом: підвищив ампліфікацію записів і роботу autovacuum, і насправді був зайвим. Зрештою його видалили після постмортему, коли емоції щодо seq scan вщухли.

2) Оптимізація, що відбилася бумерангом: примусити план

Інша команда використовувала MariaDB для API. Запит іноді погано поводився після великих імпортів. Хтось застосував FORCE INDEX, щоб примусити оптимізатор використовувати, здається, «правильний» індекс у EXPLAIN. Бенчмарки покращилися. Усі заспокоїлися.

Через два місяці нова фіча додала предикат, і примусовий індекс став неправильним. Запит тепер робив величезний range scan, а потім фільтрував більшість рядків. Оптимізатор хотів би перейти на інший ключ, але не міг. Латентність підскочила, і API почав тайм-аутитись під піковим трафіком.

Інцидент був особливо веселий, бо план виглядав «стабільним». Стабільність — пастка: ви зафіксували рішення, яке було вірним тільки для старого розподілу та форми запиту. У постмортемі команда видалила hint і замість цього виправила статистики та дизайн індексів. Вони також додали регресійний тест на латентність з репрезентативними даними, бо «працює на даних минулого місяця» — не стратегія.

3) Нудна, але правильна практика, що врятувала день: виміряні rollout-и і базлайни

Велика внутрішня платформа мала кластери на Postgres і MariaDB. Їх вже палили регресії планів, тому вони зробили нудну, але агресивну річ: для кожного критичного запиту зберігали базлайн з (a) нормалізованим текстом запиту, (b) очікуваними підрахунками рядків, (c) формою плану та (d) латентністю на фіксованому снапшоті даних.

Під час рутинного оновлення Postgres один запит змінився з індексного nested loop на hash join. Латентність у стенді подвоїлася. Ніхто не панікував, бо був diff: новий план відкачувався на диск через змінену поведінку пам’яті при вищій паралельності.

Вони підлаштували work_mem для тієї ролі, обмежили паралельні worker’и для сесії і перевірили через EXPLAIN (ANALYZE, BUFFERS), що spill-и зникли. Оновлення пройшло без драм. Найкраще: нікому не довелося сперечатися на основі відчуттів. Регресію плану виявили до користувачів.

Жарт №2: Нічого не каже «корпорація», як тиждень роботи, щоб уникнути вихідних із проблемами.

Шаблони вузьких місць: як помітити реальний ліміт

1) Очікування блокувань, що маскуються під повільні запити

Якщо запит повільний тому, що заблокований, ваш план здебільшого не має значення. У Postgres дивіться на wait_event_type. У MariaDB перевіряйте стани processlist, як metadata locks. Класична пастка — тюнити UPDATE, що «повільний», бо чекає за довгу транзакцію, DDL або batch job, який забув зробити commit.

Коренева причина часто в обсязі транзакції. Виправлення зазвичай не в індексі. Це менші транзакції, інша поведінка блокувань або планове виконання DDL.

2) Виконання, обмежене I/O: план нормальний, але сховище ні

Ви можете мати ідеальний index scan, що все одно читає тисячі сторінок, бо дані розкидані і не в кеші. Плани показують що має відбутися; BUFFERS і системні метрики показують що сталося.

I/O-шаблони:

• План Postgres показує багато shared read buffers і помірний CPU.
• Хост показує високий iowait, високий await, високе %util пристрою.
• Латентність погіршується з ростом конкурентності (черги на пристрої).

Виправлення: покривні індекси, зменшення вибірки колонок, партиціонування для локальності, vacuum для bloat або визнання, що потрібні додаткові IOPS. Іноді найкраща оптимізація — купити те сховище, яке ви думали, що вже маєте.

3) Виконання, обмежене CPU: дорогі предикати і покрокова біль

CPU-обмежені запити часто включають:

• Функції на колонках у WHERE (особливо немутуючі в Postgres; або такі, що перешкоджають використанню індексу).
• Парсинг JSON, регулярні вирази, LIKE з підстановками, порівняння з важкою правилами колації.
• Вибух з’єднань через неправильний порядок або недооцінену кардинальність.

Плани допомагають визначити, де ряди розростаються. Але не пропустіть неочевидних споживачів CPU: правила колації, розпакування/розархівація, або приведення типів по рядку.

4) Тиск пам’яті: spill-и сорту/хешу, потім усе стає «випадково повільним»

У Postgres spill-и видно як тимчасові файли і в деталях вузлів для сорту/хешу в ANALYZE. У MariaDB «Using temporary» і «Using filesort» — підказки, але потрібно підтвердити, чи відбулося вилучення на диск. Налаштування пам’яті на сесію/запит; конкурентність множить їх. Тут наївне тюнування часто призводить до простоїв.

5) Брехня кардинальності: коли оцінки — вигадки

Якщо оцінені рядки відрізняються від фактичних у 10× або 100×, ставте це як основну діагностику. Це призводить до поганого вибору з’єднань, індексів і непередбачуваної продуктивності.

Поширені причини:

• Застарілі статистики (масові завантаження, сильна зміна, перекоси).
• Кореляція між колонками (оптимізатор вважає незалежність).
• Селективність, що залежить від параметрів (prepared statements, ORM).
• Невразливі предикати (функції, приведення типів).

Виправлення статистик не гламурне, але часто це правильний перший крок, бо поліпшує кожен запит, що залежить від них.

Поширені помилки (симптом → корінь → виправлення)

«Бачимо Seq Scan, отже це проблема»

Симптом: Postgres показує Seq Scan; хтось хоче індекс.

Корінь: Предикат не селективний, або індекс спричинить випадковий I/O гірший за скан, або запит повертає велику частку таблиці.

Виправлення: Виміряйте через EXPLAIN (ANALYZE, BUFFERS). Якщо більшість сторінок все одно зачіпається, прийміть seq scan або переробіть запит. Індексація не є чеснотою за замовчуванням.

«Using filesort означає, що сортування на диску»

Симптом: MariaDB EXPLAIN показує Using filesort, запит повільний.

Корінь: Використовується алгоритм filesort; він може бути в пам’яті або відкачуватися, залежно від пам’яті і розміру результату.

Виправлення: Підтвердіть через серверні статуси і метрики тимчасових таблиць, зменшіть результат, додайте складений індекс, що відповідає ORDER BY + WHERE, де доречно, і уникайте сортування величезних проміжних результатів.

«Додали індекс і стало гірше»

Симптом: Читання трохи покращилися, але записи й реплікація стали гіршими.

Корінь: Ампліфікація записів і додаткове обслуговування (vacuum/autovacuum, page splits, redo/undo) переважили виграш у читанні; також можлива регресія плану з використанням нового індекса неправильно.

Виправлення: Перевіряйте на робочому навантаженні. Віддавайте перевагу вузьким покривним індексам; видаляйте невикористані; розглядайте часткові індекси (Postgres) для гарячих підмножин.

«План не змінився, але сьогодні повільніше»

Симптом: Та сама форма плану, але вища латентність.

Корінь: Змінилася затримка сховища, посилився витіснення кешу, зріс bloat, підвищилась конкурентність або тиск чекпоінтів.

Виправлення: Перевірте iostat/vmstat, буферні читання проти хітів, autovacuum і мертві кортежі, і конкурентність. Спочатку трактуйте як системну проблему.

«У стенді швидко, а в проді повільно»

Симптом: Бенчмарк виглядає добре; у продакшні все гуде.

Корінь: Інший розподіл даних, відсутність перекосів, різний стан кешу, різна конкурентність, різні налаштування (work_mem/tmp_table_size), різне сховище.

Виправлення: Використовуйте репрезентативні снапшоти даних, запускайте з навантаженням, захоплюйте реальні плани з ANALYZE/JSON і порівнюйте буферні читання та тимчасове використання.

«Ми примусили оптимізатор і тепер стабільно»

Симптом: Hint/перемикач оптимізатора «вирішив» проблему продуктивності.

Корінь: Ви заморозили вибір, що залежить від розподілу даних; майбутні зміни зроблять його неправильним.

Виправлення: Виправте статистики і дизайн схеми; залишайте підказки як останню міру з моніторингом і планом їхнього видалення.

«Додавання RAM все виправить»

Симптом: Висока латентність, хтось звинувачує кеш.

Корінь: Іноді правда; часто це погані запити, що роблять зайву роботу, або contention блокувань, або насичення IOPS.

Виправлення: Підтвердіть через метрики буферів і I/O wait. Додавайте RAM лише тоді, коли робочий набір справді не поміщається і шаблон доступу виграє від цього.

Чеклісти / покроковий план

Покрокова триаж (15–30 хвилин)

1. Обрати правильну ціль: визначте запити з найбільшим загальним часом або топ p95/p99 (pg_stat_statements / slow log).
2. Перевірити очікування vs роботу: Postgres pg_stat_activity; MariaDB SHOW FULL PROCESSLIST.
3. Перевірити насичення системи: iostat -x, vmstat.
4. Захопити виміряний план: Postgres EXPLAIN (ANALYZE, BUFFERS); MariaDB EXPLAIN FORMAT=JSON плюс handler reads і trace за потреби.
5. Порівняти оцінки: якщо оцінки неправильні, спочатку виправте статистики (ANALYZE, гістограми).
6. Перевірити spill-и: тимчасові файли Postgres; тимчасові таблиці і поведінка сортування в MariaDB через статусні лічильники.
7. Оберіть важіль: перепис запиту, дизайн індексів, обслуговування/статистика або інфраструктура/конкурентність.
8. Перевірте з планом відкату: виміряйте до/після, підтвердіть відсутність регресій на записах, реплікації та сховищі.

Чекліст рішень щодо індексів (не вгадуйте)

• Чи редукує предикат рядки значно? Якщо ні, індекс може більше нашкодити, ніж допомогти.
• Чи потрібне сортування/limit? Складені індекси можуть замінити сортування на індексний порядок.
• Чи вибираєте ви багато колонок? Розгляньте покривні індекси (Postgres INCLUDE; MariaDB вторинний індекс InnoDB включає PK, але не всі колонки).
• Чи чутливий запит до параметрів? Уникайте висновків за одним «типовим» значенням.
• Яка вартість записів? Думайте про rate вставок/оновлень, а не лише про читання.

Чекліст статистик і обслуговування

• Postgres: чи autovacuum та analyze встигають за гарячими таблицями?
• Postgres: чи багато мертвих кортежів і давній last_autovacuum?
• MariaDB: чи потрібні гістограми для перекошених колонок?
• Обидва: чи змінився розподіл даних (імпорти, backfills, ріст орендарів)?

FAQ

1) Що читати легше: MariaDB EXPLAIN чи Postgres EXPLAIN?

Postgres, бо дає фактичний час і підрахунки рядків на вузол з ANALYZE. MariaDB теж читабельний, але часто потрібен формат JSON і додаткові лічильники статусу, щоб підтвердити реальність.

2) Чи завжди запускати EXPLAIN ANALYZE в продакшні?

Ні. Воно виконує запит. Для безпечної діагностики в продакшні використовуйте репліку або контрольований зразок. Якщо треба запускати, обмежте ліміти й прогін під час низького навантаження.

3) Чому Postgres обирає seq scan, коли є індекс?

Тому що скан може бути дешевшим за випадковий I/O, коли велика частина таблиці підходить або коли таблиця невелика, або коли кешовані сторінки роблять скан швидким. Оптимізатор часто правий.

4) У MariaDB що означає «Using temporary» насправді?

Це означає, що для групування, сортування або проміжних результатів використовується внутрішня тимчасова таблиця. Вона може бути в пам’яті або на диску залежно від розміру й налаштувань. Сприймайте це як «є проміжний результат» і виміряйте, чи відбувається spill.

5) Як виявити регресію плану після деплою?

Порівняйте нормалізовані fingerprint-и запитів і виміряні плани до/після. У Postgres захопіть EXPLAIN (ANALYZE, BUFFERS) і перевірте точність оцінок. У MariaDB порівняйте JSON-план і handler reads та час виконання під репрезентативним навантаженням.

6) Коли «додати індекс» — правильна відповідь?

Коли предикат селективний, запит достатньо частий, щоб виправдати вартість запису, і індекс підтримує доступний шаблон (фільтр, сортування, join key). Якщо це пакетний запит раз на день, ваш «фікс індексом» може стати довічним податком.

7) Чому моя оптимізація відбилася при конкурентності?

Бо пам’ять на запит множиться на кількість сесій, бо кеш тещиниться по-іншому, і бо чергування I/O нелінійне. План, що швидкий при конкурентності 1, може розвалитися при 200.

8) Який найшвидший спосіб зрозуміти, чи це сховище?

Дивіться метрики хоста: iostat -x await/util, плюс базові докази на рівні БД: буферні читання Postgres і події очікування; фізичні читання InnoDB і тиск буферного пулу. Якщо пристрій насичений, тюнінг запитів має обмежену ефективність без зменшення читань.

9) MariaDB чи Postgres: який оптимізатор «кращий»?

Жоден магічно не кращий; обидва сильні й обидва мають сліпі плями. Postgres легше валідувати, бо він показує більше ранньої правди в плані. MariaDB дає optimizer trace, що може бути дуже інформативним, коли потрібно знати «чому».

Висновок: практичні наступні кроки

Якщо ви візьмете одну операційну звичку з цього: перестаньте ставити плани як список завдань. Почніть сприймати їх як гіпотезу, яку ви підтверджуєте через очікування, буфери й насичення системи. Найшвидший шлях виправити продуктивність — виправити правильну річ.

Наступні кроки, які ви можете зробити цього тижня:

1. Увімкніть і використовуйте базування запитів на рівні запиту (pg_stat_statements для Postgres; slow log + агрегація для MariaDB), щоб оптимізувати те, що реально коштує вам часу.
2. Прийміть швидкий план діагностики: очікування vs робота, потім contention, потім виміряні плани.
3. Введіть політику «жодних неоглянутих індексів»: кожен індекс потребує вимірюваної вигоди і оцінки вартості записів.
4. Для ваших топ-5 запитів збережіть відомий хороший план і виміряний профайл виконання (buffers/temp usage). Перевіряйте після змін схеми й оновлень.

Ваш майбутній я, який буде на колах у 02:00, оцінить це. Не емоційно. Практично.