🥇Apa yang dibungkam EXPLAIN dan bagaimana cara membuatnya berbicara

Pertanyaan klasik yang diajukan pengembang ke DBA-nya atau pemilik bisnis ke konsultan PostgreSQL hampir selalu terdengar sama: “Mengapa permintaan di database memerlukan waktu begitu lama?”

Serangkaian alasan tradisional:

algoritma yang tidak efisien
ketika Anda memutuskan untuk BERGABUNG dengan beberapa CTE dalam beberapa puluh ribu catatan
statistik usang
jika sebaran data sebenarnya dalam tabel sudah sangat berbeda dengan yang dikumpulkan oleh ANALYZE terakhir kali
"pasang" sumber daya
dan tidak ada lagi daya komputasi khusus yang cukup pada CPU, gigabyte memori terus-menerus dipompa, atau disk tidak dapat memenuhi semua “keinginan” database
pemblokiran dari proses yang bersaing

Dan jika pemblokiran cukup sulit untuk ditangkap dan dianalisis, maka kita memerlukan hal lain rencana kueri, yang dapat diperoleh dengan menggunakan JELASKAN operator (Sebaiknya segera JELASKAN (ANALISIS, BUFFERS)...) atau modul auto_explain.

Namun, sebagaimana dinyatakan dalam dokumentasi yang sama,

“Memahami sebuah rencana adalah sebuah seni, dan untuk menguasainya membutuhkan sejumlah pengalaman…”

Tapi Anda bisa melakukannya tanpanya jika Anda menggunakan alat yang tepat!

Seperti apa biasanya rencana kueri? Sesuatu seperti itu:

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

atau seperti ini:

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

Tetapi membaca rencana dalam teks “dari lembaran” sangat sulit dan tidak jelas:

ditampilkan di node jumlah berdasarkan sumber daya subpohon
yaitu, untuk memahami berapa banyak waktu yang diperlukan untuk mengeksekusi node tertentu, atau berapa tepatnya pembacaan dari tabel ini membawa data dari disk, Anda perlu mengurangi satu dari yang lain.
waktu simpul diperlukan kalikan dengan loop
ya, pengurangan bukanlah operasi paling rumit yang perlu dilakukan "di kepala" - lagipula, waktu eksekusi ditunjukkan sebagai rata-rata untuk satu eksekusi sebuah node, dan jumlahnya bisa ratusan
baiklah, dan semua ini bersama-sama menghalangi kita untuk menjawab pertanyaan utama - jadi siapa "tautan terlemah"?

Ketika kami mencoba menjelaskan semua ini kepada beberapa ratus pengembang kami, kami menyadari bahwa dari luar terlihat seperti ini:

Dan itu berarti kita perlu...

Alat

Di dalamnya kami mencoba mengumpulkan semua mekanisme utama yang membantu memahami “siapa yang harus disalahkan dan apa yang harus dilakukan” sesuai dengan rencana dan permintaan. Nah, dan bagikan sebagian pengalaman Anda dengan komunitas.
Temui dan gunakan - jelaskan.tensor.ru

Visibilitas rencana

Apakah mudah untuk memahami rencananya jika terlihat seperti ini?

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

Tidak juga.

Tapi seperti ini, dalam bentuk yang disingkatketika indikator-indikator utama dipisahkan, semuanya menjadi lebih jelas:

Namun jika rencananya lebih rumit, dia akan datang untuk menyelamatkan distribusi waktu diagram lingkaran berdasarkan node:

Nah, untuk pilihan yang paling sulit, dia sedang terburu-buru membantu grafik kemajuan:

Misalnya, ada situasi yang tidak sepele ketika sebuah rencana mungkin memiliki lebih dari satu akar sebenarnya:

Petunjuk struktural

Nah, jika seluruh struktur rencana dan kelemahannya sudah ditata dan terlihat, mengapa tidak menyorotinya kepada pengembang dan menjelaskannya dalam “bahasa Rusia”?

Kami telah mengumpulkan beberapa lusin templat rekomendasi tersebut.

Profiler kueri baris demi baris

Sekarang, jika Anda menempatkan kueri asli ke dalam rencana yang dianalisis, Anda dapat melihat berapa banyak waktu yang dihabiskan untuk setiap pernyataan - kira-kira seperti ini:

...atau bahkan seperti ini:

Mengganti parameter ke dalam permintaan

Jika Anda “melampirkan” tidak hanya permintaan ke paket, tetapi juga parameternya dari baris DETAIL log, Anda juga dapat menyalinnya di salah satu opsi:

dengan substitusi nilai dalam kueri
untuk eksekusi langsung di basis Anda dan pembuatan profil lebih lanjut
```
SELECT 'const', 'param'::text;
```
dengan substitusi nilai melalui PREPARE/EXECUTE
untuk meniru pekerjaan penjadwal, ketika bagian parametrik dapat diabaikan - misalnya, saat mengerjakan tabel yang dipartisi
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Arsip rencana

Tempel, analisis, bagikan dengan kolega! Rencana akan tetap diarsipkan dan Anda dapat kembali lagi nanti: jelaskan.tensor.ru/archive

Namun jika Anda tidak ingin orang lain melihat rencana Anda, jangan lupa mencentang kotak “jangan publikasikan dalam arsip”.

Dalam artikel berikut saya akan berbicara tentang kesulitan dan keputusan yang muncul saat menganalisis sebuah rencana.

Sumber: www.habr.com

Apa yang EXPLAIN diamkan dan bagaimana membuatnya dibicarakan