🥇Пра што маўчыць EXPLAIN, і як яго разгаварыць

Класічнае пытанне, з якім распрацоўшчык прыходзіць да свайго DBA або ўладальнік бізнэсу - да кансультанта па PostgreSQL, амаль заўсёды гучыць аднолькава: "Чаму запыты выконваюцца на базе так доўга?"

Традыцыйны набор прычын:

неэфектыўны алгарытм
калі вы вырашылі зрабіць JOIN некалькіх CTE па пары дзясяткаў тысяч запісаў
неактуальная статыстыка
калі фактычнае размеркаванне дадзеных у табліцы ўжо моцна адрозніваецца ад сабранай ANALYZE'ам у апошні раз
«затык» па рэсурсах
і ўжо бракуе вылучаных вылічальных магутнасцяў CPU, увесь час прапампоўваюцца гігабайты памяці або кружэлка не паспявае за ўсімі «хатэлкамі» БД
блакавання ад канкуруючых працэсаў

І калі блакіроўкі дастаткова складаныя ў злове і аналізе, то для ўсяго астатняга нам дастаткова плана запыту, які можна атрымаць з дапамогай аператара EXPLAIN (лепш, вядома, адразу EXPLAIN (ANALYZE, BUFFERS) …) або модуля auto_explain.

Але, як сказана ў той жа дакументацыі,

"Разуменне плана - гэта мастацтва, і каб авалодаць ім, патрэбен пэўны вопыт, ..."

Але можна абыйсціся і без яго, калі скарыстацца прыдатнай прыладай!

Як звычайна выглядае план запыту? Неяк вось так:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

ці вось так:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

Але чытаць план тэкстам "з ліста" - вельмі складана і ненаглядна:

у вузле выводзіцца сума па рэсурсах поддерева
гэта значыць каб зразумець, колькі сышло чакай на выкананне пэўнага вузла, ці колькі менавіта вось гэтае чытанне з табліцы падняло дадзеных з дыска - трэба неяк адымаць адно з іншага
час вузла неабходна памнажаць на loops
так, адніманне яшчэ не самая складаная аперацыя, якую трэба рабіць "у розуме" - бо час выканання паказваецца асераднёнае для аднаго выканання вузла, а іх могуць быць сотні
ну, і ўсё гэта разам замінае адказаць на галоўнае пытанне - дык хто ж «самае слабое звяно»?

Калі мы паспрабавалі растлумачыць усё гэта некалькім сотням нашых распрацоўшчыкаў, то зразумелі, што з боку гэта выглядае прыкладна вось так:

А, значыць, нам патрэбен…

Інструмент

У ім мы пастараліся сабраць усе ключавыя механікі, якія дапамагаюць па плане і запыце зразумець, "хто вінаваты і што рабіць". Ну, і часткай свайго досведу падзяліцца з супольнасцю.
Сустракайце і карыстайцеся - explain.tensor.ru

Нагляднасць планаў

Ці лёгка зразумець план, калі ён выглядае так?

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

Не вельмі.

Але вось так, у скарочаным выглядзе, Калі ключавыя паказчыкі аддзеленыя - ужо значна больш зразумела:

Але калі план больш складаны - на дапамогу прыйдзе piechart размеркавання часу па вузлах:

Ну, а для самых складаных варыянтаў на дапамогу спяшаецца дыяграма выканання:

Напрыклад, бываюць дастаткова нетрывіяльныя сітуацыі, калі план можа мець больш за адзін фактычны корань:

Структурныя падказкі

Ну, а калі ўся структура плана і яго хворыя месцы ўжо раскладзены і бачныя - чаму б не падсвятліць іх распрацоўшчыку, і не растлумачыць "рускай мовай"?

Такіх шаблонаў рэкамендацый мы сабралі ўжо некалькі дзясяткаў.

парадковы прафайлер запыту

Цяпер, калі на аналізаваны план накласці зыходны запыт, то можна ўбачыць, колькі часу пайшло на кожны асобны аператар - прыкладна вось так:

… ці нават так:

Падстаноўка параметраў у запыт

Калі вы "прычапілі" да плана не толькі запыт, але і яго параметры з DETAIL-радкі лога, то зможаце скапіяваць яго дадаткова ў адным з варыянтаў:

з падстаноўкай значэнняў у запыт
для непасрэднага выканання на сваёй базе і далейшай прафіліроўкі
```
SELECT 'const', 'param'::text;
```
з падстаноўкай значэнняў праз PREPARE/EXECUTE
для эмуляцыі працы планавальніка, калі параметрычная частка можа быць праігнараваная - напрыклад, пры працы на секцыянаваных табліцах
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Архіў планаў

Устаўляйце, аналізуйце, дзяліцеся з калегамі! Планы застануцца ў архіве, і вы зможаце вярнуцца да іх пазней: explain.tensor.ru/archive

Але калі не хочаце, каб ваш план убачылі іншыя, не забудзьцеся паставіць галачку "не публікаваць у архіве".

У наступных артыкулах я раскажу пра тыя складанасці і рашэнні, якія ўзнікаюць пры аналізе плана.

Крыніца: habr.com

Пра што маўчыць EXPLAIN, і як яго разгаварыць