PostgreSQL Query Profiler: cara memadankan pelan dan pertanyaan

Ramai yang dah guna explain.tensor.ru - perkhidmatan visualisasi pelan PostgreSQL kami mungkin tidak menyedari salah satu kuasa besarnya - mengubah sekeping log pelayan yang sukar dibaca...

... ke dalam pertanyaan yang direka dengan indah dengan pembayang kontekstual untuk nod pelan yang sepadan:

Dalam transkrip bahagian kedua beliau ini laporan di PGConf.Russia 2020 Saya akan memberitahu anda bagaimana kami berjaya melakukan ini.

Transkrip bahagian pertama, khusus untuk masalah prestasi pertanyaan biasa dan penyelesaiannya, boleh didapati dalam artikel "Resipi untuk pertanyaan SQL yang sakit".

Mula-mula, mari kita mula mewarna - dan kita tidak lagi akan mewarnai pelan itu, kita telah mewarnainya, kita sudah mempunyainya cantik dan mudah difahami, tetapi permintaan.

Nampaknya kepada kami bahawa dengan "helaian" yang tidak diformat sedemikian, permintaan yang ditarik dari log kelihatan sangat hodoh dan oleh itu menyusahkan.


Terutama apabila pemaju "melekatkan" badan permintaan dalam kod (ini, sudah tentu, antipattern, tetapi ia berlaku) dalam satu baris. ngeri!

Mari kita lukis ini dengan lebih cantik.


Dan jika kita boleh melukis ini dengan cantik, iaitu, buka dan susun semula badan permintaan, maka kita boleh "lampirkan" pembayang pada setiap objek permintaan ini - apa yang berlaku pada titik yang sepadan dalam rancangan.

Pokok sintaks pertanyaan

Untuk melakukan ini, permintaan mesti dihuraikan terlebih dahulu.


Kerana kita ada teras sistem berjalan pada NodeJS, kemudian kami membuat modul untuknya, anda boleh cari di GitHub. Sebenarnya, ini adalah "pengikatan" yang dilanjutkan kepada bahagian dalaman parser PostgreSQL itu sendiri. Iaitu, tatabahasa hanya disusun binari dan pengikatan dibuat kepadanya daripada NodeJS. Kami mengambil modul orang lain sebagai asas - tidak ada rahsia besar di sini.

Kami menyuap badan permintaan sebagai input kepada fungsi kami - pada output kami mendapat pokok sintaks yang dihuraikan dalam bentuk objek JSON.


Kini kita boleh menjalankan melalui pokok ini dalam arah yang bertentangan dan memasang permintaan dengan inden, pewarnaan dan pemformatan yang kita mahu. Tidak, ini tidak boleh disesuaikan, tetapi kami nampaknya ini mudah.

Pertanyaan pemetaan dan nod pelan

Sekarang mari kita lihat bagaimana kita boleh menggabungkan rancangan yang kami analisis pada langkah pertama dan pertanyaan yang kami analisis pada langkah kedua.

Mari kita ambil contoh mudah - kita mempunyai pertanyaan yang menjana CTE dan membaca daripadanya dua kali. Dia menjana rancangan sedemikian.

CTE

Jika anda melihatnya dengan teliti, sehingga versi 12 (atau bermula daripadanya dengan kata kunci MATERIALIZED) pembentukan CTE adalah penghalang mutlak bagi perancang.


Ini bermakna jika kita melihat penjanaan CTE di suatu tempat dalam permintaan dan nod di suatu tempat dalam rancangan CTE, maka nod ini pasti "berlawan" antara satu sama lain, kita boleh segera menggabungkannya.

Masalah dengan asterisk: CTE boleh bersarang.

Terdapat yang sangat teruk bersarang, dan juga yang mempunyai nama yang sama. Sebagai contoh, anda boleh di dalam CTE A membuat CTE X, dan pada tahap yang sama di dalam CTE B buat sekali lagi CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

Apabila membandingkan, anda mesti memahami ini. Memahami ini "dengan mata anda" - walaupun melihat rancangan, walaupun melihat badan permintaan - sangat sukar. Jika penjanaan CTE anda rumit, bersarang, dan permintaannya besar, maka ia tidak sedarkan diri sepenuhnya.

UNION

Jika kita mempunyai kata kunci dalam pertanyaan UNION [ALL] (pengendali menyertai dua sampel), maka dalam pelan ia sepadan dengan sama ada nod Append, atau beberapa Recursive Union.


Itu yang "di atas" di atas UNION - ini adalah keturunan pertama nod kami, iaitu "di bawah" - yang kedua. Jika melalui UNION kami mempunyai beberapa blok "terpaku" sekaligus, kemudian Append-masih akan ada hanya satu nod, tetapi ia tidak akan mempunyai dua, tetapi banyak anak - mengikut urutan mereka pergi, masing-masing:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

Masalah dengan asterisk: dalam penjanaan persampelan rekursif (WITH RECURSIVE) juga boleh lebih daripada satu UNION. Tetapi hanya blok terakhir selepas yang terakhir sentiasa rekursif UNION. Semua di atas adalah satu, tetapi berbeza UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

Anda juga perlu dapat "mengekalkan" contoh sedemikian. Dalam contoh ini kita melihat bahawa UNION-terdapat 3 segmen dalam permintaan kami. Sehubungan itu, satu UNION sepadan dengan Append-nod, dan kepada yang lain - Recursive Union.

Data baca-tulis

Segala-galanya dibentangkan, kini kita tahu bahagian permintaan yang mana sepadan dengan bahagian rancangan yang mana. Dan dalam kepingan ini kita boleh mencari objek yang "boleh dibaca" dengan mudah dan semula jadi.

Dari sudut pandangan pertanyaan, kami tidak tahu sama ada ia jadual atau CTE, tetapi ia ditetapkan oleh nod yang sama RangeVar. Dan dari segi "kebolehbacaan", ini juga merupakan set nod yang agak terhad:

• Seq Scan on [tbl]
• Bitmap Heap Scan on [tbl]
• Index [Only] Scan [Backward] using [idx] on [tbl]
• CTE Scan on [cte]
• Insert/Update/Delete on [tbl]

Kami tahu struktur rancangan dan pertanyaan, kami tahu surat-menyurat blok, kami tahu nama objek - kami membuat perbandingan satu dengan satu.


sekali lagi tugasan "dengan asterisk". Kami menerima permintaan itu, melaksanakannya, kami tidak mempunyai sebarang alias - kami hanya membacanya dua kali daripada CTE yang sama.


Kami melihat rancangan - apa masalahnya? Mengapa kita mempunyai alias? Kami tidak memesannya. Di manakah dia mendapat "nombor nombor" sedemikian?

PostgreSQL menambahkannya sendiri. Anda hanya perlu memahaminya hanya alias begitu bagi kami, untuk tujuan perbandingan dengan pelan, ia tidak masuk akal, ia hanya ditambah di sini. Jangan kita hiraukan dia.

Yang kedua tugasan "dengan asterisk": jika kita membaca dari jadual partitioned, maka kita akan mendapat nod Append atau Merge Append, yang akan terdiri daripada sebilangan besar "kanak-kanak", dan setiap daripadanya akan entah bagaimana Scan'om dari bahagian jadual: Seq Scan, Bitmap Heap Scan atau Index Scan. Tetapi, dalam apa jua keadaan, "kanak-kanak" ini tidak akan menjadi pertanyaan yang rumit - ini adalah bagaimana nod ini boleh dibezakan daripada Append pada UNION.


Kami juga memahami simpulan sedemikian, kumpulkannya "dalam satu longgokan" dan katakan: "semua yang anda baca dari megatable ada di sini dan di bawah pokok".

Data "Mudah" menerima nod

Values Scan sepadan dalam rancangan VALUES dalam permintaan itu.

Result adalah permintaan tanpa FROM seperti SELECT 1. Atau apabila anda mempunyai ungkapan yang sengaja palsu WHERE-block (kemudian atribut muncul One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan "peta" kepada SRF dengan nama yang sama.

Tetapi dengan pertanyaan bersarang semuanya lebih rumit - malangnya, mereka tidak selalu berubah menjadi InitPlan/SubPlan. Kadang-kadang mereka bertukar menjadi ... Join atau ... Anti Join, terutamanya apabila anda menulis sesuatu seperti WHERE NOT EXISTS .... Dan di sini tidak selalu mungkin untuk menggabungkannya - dalam teks rancangan tidak ada pengendali yang sepadan dengan nod rancangan.

sekali lagi tugasan "dengan asterisk": beberapa VALUES dalam permintaan itu. Dalam kes ini dan dalam pelan anda akan mendapat beberapa nod Values Scan.


Akhiran "bernombor" akan membantu membezakannya dari satu sama lain - ia ditambah tepat dalam susunan yang sepadan dijumpai VALUES-sekat sepanjang permintaan dari atas ke bawah.

Pemprosesan data

Nampaknya segala-galanya dalam permintaan kami telah diselesaikan - yang tinggal hanyalah Limit.


Tetapi di sini semuanya mudah - nod seperti Limit, Sort, Aggregate, WindowAgg, Unique "peta" satu-satu kepada pengendali yang sepadan dalam permintaan, jika mereka ada. Tiada "bintang" atau kesukaran di sini.

JOIN

Kesukaran timbul apabila kita ingin bergabung JOIN antara mereka sendiri. Ini tidak selalu mungkin, tetapi mungkin.


Dari sudut pandangan penghurai pertanyaan, kami mempunyai nod JoinExpr, yang mempunyai dua orang anak - kiri dan kanan. Oleh itu, ini adalah "di atas" JOIN anda dan apa yang ditulis "di bawah" dalam permintaan.

Dan dari sudut pandangan rancangan, ini adalah dua keturunan beberapa * Loop/* Join-nod. Nested Loop, Hash Anti Join,... - sesuatu seperti itu.

Mari kita gunakan logik mudah: jika kita mempunyai jadual A dan B yang "menyertai" satu sama lain dalam pelan, maka dalam permintaan ia boleh didapati sama ada A-JOIN-BAtau B-JOIN-A. Mari cuba gabungkan cara ini, mari cuba gabungkan sebaliknya, dan seterusnya sehingga kita kehabisan pasangan sedemikian.

Mari kita ambil pokok sintaks kita, ambil rancangan kita, lihat mereka... tidak serupa!


Mari kita lukis semula dalam bentuk graf - oh, ia sudah kelihatan seperti sesuatu!


Mari kita ambil perhatian bahawa kami mempunyai nod yang mempunyai anak B dan C secara serentak - kami tidak peduli dalam susunan apa. Mari gabungkan mereka dan pusingkan gambar nod itu.


Jom tengok lagi. Kini kami mempunyai nod dengan kanak-kanak A dan berpasangan (B + C) - serasi dengan mereka juga.


Hebat! Ternyata kita berdua ini JOIN daripada permintaan dengan nod pelan berjaya digabungkan.

Malangnya, masalah ini tidak selalu diselesaikan.


Sebagai contoh, jika dalam permintaan A JOIN B JOIN C, dan dalam pelan itu, pertama sekali, nod "luar" A dan C telah disambungkan. Tetapi tiada pengendali sedemikian dalam permintaan itu, kami tidak mempunyai apa-apa untuk diserlahkan, tiada apa-apa untuk dilampirkan pembayang. Ia sama dengan "koma" semasa anda menulis A, B.

Tetapi, dalam kebanyakan kes, hampir semua nod boleh "diikat" dan anda boleh mendapatkan pemprofilan jenis ini di sebelah kiri tepat pada masanya - secara literal, seperti dalam Google Chrome apabila anda menganalisis kod JavaScript. Anda boleh melihat berapa lama setiap baris dan setiap pernyataan mengambil masa untuk "melaksanakan".


Dan untuk memudahkan anda menggunakan semua ini, kami telah membuat storan arkib, di mana anda boleh menyimpan dan kemudian mencari rancangan anda bersama-sama dengan permintaan yang berkaitan atau berkongsi pautan dengan seseorang.

Jika anda hanya perlu membawa pertanyaan yang tidak boleh dibaca ke dalam bentuk yang mencukupi, gunakan "penormal" kami.

Sumber: www.habr.com