🥇EXPLAIN-in susduğu və onu necə danışdırmaq barədə

İnkişaf etdirici DBA-yə və ya iş sahibi PostgreSQL üzrə məsləhətçiyə gəldikdə klassik sual, demək olar ki, həmişə eyni cür səslənir: «Niyə sorğular verilənlər bazasında bu qədər uzun çəkir?»

Ənənəvi səbəblər dəstəsi:

səmərəsiz alqoritm
bir neçə on min qeyd üçün bir neçə CTE-nin JOIN-un edildiyi zaman
müvafiq statistikalar olmaması
əgər cədvəldəki faktiki məlumatların dağılımı ANALYZE ilə son dəfə toplandığından kəskin fərqlidirsə
resurs limiti
və ayrılmış CPU gücü azdır, yaddaş bir neçə giqabayta çatır və disk bazanın bütün «istəkləri» ilə yetişə bilmir
bloklamalar rəqib proseslər tərəfindən

Və bloklamalar tutmaq və analiz etmək üçün kifayət qədər mürəkkəbdir, qalanı üçün isə bizə kifayət edir sorğu planı, bu, EXPLAIN operatoru vasitəsilə əldə edilə bilər (daha yaxşısı, dərhal EXPLAIN (ANALYZE, BUFERLAR) ilə ...) və ya auto_explain modulu.

Amma, eyni sənədlərdə deyildiyi kimi,

«Planın başa düşülməsi bir sənətdir, və bunu öyrənmək üçün müəyyən bir təcrübə lazımdır, ...»

Amma uyğun bir alətdən istifadə edərək onsuz da edə bilərsiniz!

Sorğu planı necə görünür? Belə olur:

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

və ya belə:

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

Amma planı «siyahıdan» oxumaq çox çətindir və görünüşsüzdür:

nöqtədə alt ağacın resursları üzrə cəmi
yəni, konkret bir nodun yerinə yetirilməsi üçün nə qədər vaxt sərf edildiyini başa düşmək və ya cədvəldən oxumağın nə qədər məlumatı diskdən çıxardığını bilmək üçün birini digərindən çıxarmaq lazımdır
noda vaxtı loops ilə vurulmalıdır
bəli, çıxarmaq hələ də ediləcək «zehni» ən çətin əməliyyatlardan biri deyil — çünki yerinə yetirmə vaxtı nodun bir dəfə yerinə yetirilməsi üçün ortalama göstərilir, və onların sayı yüzlərlə ola bilər
və bütün bunlar birlikdə əsas sual üzərində cavab verməyə mane olur — bəs kimdir «ən zəif halka»?

Biz bütün bunları bir neçə yüz proqramçımıza izah etməyə çalışanda, bunun xaricdən belə göründüyünü anladıq:

Deməli, bizə lazımdır…

Alət

Burada biz plan və tələb üzrə «kim günahkardır və nə etməli» suallarını anlamağa kömək edən bütün əsas mexanikaları toplamağa çalışdıq. Və təcrübəmizin bir hissəsini icma ilə paylaşdıq.
Tanış olun və istifadə edin — explain.tensor.ru

Planların görünüşlülüyü

Bir plan bu cür görünəndə, onu anlamaq asandırmı?

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

Çox da deyil.

Amma belə olursa, qısaldılmış şəkildə, əsas göstəricilər ayrılandan sonra — artıq daha aydındır:

Amma plan bir az daha mürəkkəbdirsə — köməyə gəlir vaxt paylama piechartı düğümlərə görə:

Amma ən mürəkkəb variantlar üçün köməyə çatır icra diaqramı:

Məsələn, bəzən planın bir neçə faktiki kökü ola bilən kifayət qədər qeyri-adi hallar olur:

Struktur təminatları

Amma əgər planın bütün strukturu və onun problemli yerləri artıq açıqdırsa — niyə onları inkişaf etdirici üçün işıqlandırmayıb, «rus dilində» izah etməyək?

Belə tövsiyə şablonlarından biz artıq bir neçə on ədəd topladıq.

Sətir-sətir sorğu profilerı

İndi, əgər analiz edilən plana orijinal sorğu əlavə etsəniz, hər bir ayrı operatora nə qədər vaxt sərf olunduğunu görə bilərsiniz — təxminən belə:

… və ya hətta belə:

Sorğudakı parametr qoyulması

Əgər siz plana yalnız sorğunu deyil, həm də onun parametrini DETAIL log xəttəsindən bağlamışsınızsa, o zaman onu əlavə olaraq aşağıdakı variantlardan birində kopyalayacaqsınız:

sorğuda dəyərlərin qoyulması ilə
öz bazanızda birbaşa icra üçün və daha sonrakı profilləşdirmə üçün
```
SELECT 'const', 'param'::text;
```
PREPARE/EXECUTE vasitəsilə dəyərlərin qoyulması ilə
planlayıcının işləməsini simulyasiya etmək üçün, parametr hissəsi görməzdən gələ biləcəyi zaman — məsələn, sekmentasiyalı cədvəllərlə işləyərkən
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```