Banyak yang sudah menggunakan - layanan visualisasi paket PostgreSQL kami mungkin tidak menyadari salah satu kekuatan supernya - mengubah bagian log server yang sulit dibaca...
... menjadi kueri yang dirancang dengan indah dengan petunjuk kontekstual untuk node rencana yang sesuai:
Dalam transkrip bagian kedua miliknya Saya akan memberitahu Anda bagaimana kami berhasil melakukan ini.
Transkrip bagian pertama, yang didedikasikan untuk masalah kinerja kueri umum dan solusinya, dapat ditemukan di artikel .
Pertama, mari kita mulai mewarnai - dan kita tidak akan lagi mewarnai denahnya, kita sudah mewarnainya, kita sudah memilikinya dengan indah dan mudah dimengerti, tetapi sebuah permintaan.
Tampaknya bagi kami bahwa dengan "lembar" yang tidak diformat, permintaan yang ditarik dari log terlihat sangat jelek dan karenanya merepotkan.
Terutama ketika pengembang “merekatkan” isi permintaan ke dalam kode (ini, tentu saja, merupakan antipola, tetapi itu terjadi) dalam satu baris. Mengerikan!
Mari kita menggambar ini dengan lebih indah.
Dan jika kita dapat menggambarnya dengan indah, yaitu membongkar dan menyatukan kembali isi permintaan, maka kita dapat "melampirkan" petunjuk ke setiap objek permintaan ini - apa yang terjadi pada titik yang sesuai dalam rencana.
Pohon sintaksis kueri
Untuk melakukan ini, permintaan tersebut harus diurai terlebih dahulu.
Karena kita punya , lalu kami membuat modul untuk itu, Anda bisa . Faktanya, ini adalah “pengikatan” yang diperluas ke internal parser PostgreSQL itu sendiri. Artinya, tata bahasanya hanya dikompilasi biner dan pengikatannya dibuat dari NodeJS. Kami mengambil modul orang lain sebagai dasar - tidak ada rahasia besar di sini.
Kami memasukkan isi permintaan sebagai masukan ke fungsi kami - pada keluaran kami mendapatkan pohon sintaksis yang diurai dalam bentuk objek JSON.
Sekarang kita dapat menjalankan pohon ini dalam arah yang berlawanan dan menyusun permintaan dengan indentasi, pewarnaan, dan format yang kita inginkan. Tidak, ini tidak dapat disesuaikan, tetapi bagi kami tampaknya ini nyaman.
Memetakan kueri dan merencanakan node
Sekarang mari kita lihat bagaimana kita bisa menggabungkan rencana yang kita analisis di langkah pertama dan kueri yang kita analisis di langkah kedua.
Mari kita ambil contoh sederhana - kita memiliki kueri yang menghasilkan CTE dan membacanya dua kali. Dia membuat rencana seperti itu.
CTE
Kalau diperhatikan baik-baik, sampai dengan versi 12 (atau dimulai dari itu dengan kata kunci MATERIALIZED) formasi .
Ini berarti jika kita melihat generasi CTE di suatu tempat dalam permintaan dan sebuah node di suatu tempat dalam rencana CTE, maka node-node tersebut pasti “bertarung” satu sama lain, kita bisa langsung menggabungkannya.
Masalah dengan tanda bintang: CTE dapat disarangkan.
Ada yang bersarang sangat buruk, dan bahkan ada yang memiliki nama yang sama. Misalnya, Anda bisa di dalam CTE A membuat CTE X, dan pada tingkat yang sama di dalam CTE B melakukannya lagi CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Saat membandingkan, Anda harus memahami hal ini. Memahami hal ini "dengan mata Anda" - bahkan melihat rencananya, bahkan melihat isi permintaan - sangatlah sulit. Jika pembuatan CTE Anda rumit, bertingkat, dan permintaannya banyak, maka hal itu sepenuhnya tidak disadari.
PERSATUAN
Jika kita memiliki kata kunci dalam kueri UNION [ALL] (operator yang menggabungkan dua sampel), maka dalam rencana itu sesuai dengan salah satu node Append, atau beberapa Recursive Union.
Yang ada "di atas" di atas UNION - ini adalah keturunan pertama dari simpul kita, yang berada "di bawah" - yang kedua. Jika melalui UNION kita punya beberapa blok yang “direkatkan” sekaligus Append-hanya akan ada satu node, tetapi tidak akan ada dua, melainkan banyak anak - sesuai urutannya, masing-masing:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Masalah dengan tanda bintang: di dalam pembuatan pengambilan sampel rekursif (WITH RECURSIVE) juga bisa lebih dari satu UNION. Tetapi hanya blok terakhir setelah blok terakhir yang selalu bersifat rekursif UNION. Segala sesuatu di atas adalah satu, tetapi berbeda UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Anda juga harus bisa “menonjol” contoh-contoh seperti itu. Dalam contoh ini kita melihatnya UNION-ada 3 segmen dalam permintaan kami. Oleh karena itu, satu UNION соответствует Append-simpul, dan ke simpul lainnya - Recursive Union.
Data baca-tulis
Semuanya sudah diatur, sekarang kita tahu bagian permintaan mana yang sesuai dengan bagian rencana mana. Dan dalam potongan-potongan ini kita dapat dengan mudah dan alami menemukan objek-objek yang “dapat dibaca”.
Dari sudut pandang kueri, kita tidak tahu apakah itu tabel atau CTE, tetapi keduanya ditentukan oleh node yang sama RangeVar. Dan dalam hal “keterbacaan”, ini juga merupakan kumpulan node yang cukup terbatas:
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 mengetahui struktur rencana dan kueri, kami mengetahui korespondensi blok, kami mengetahui nama objek - kami membuat perbandingan satu lawan satu.
Lagi tugas "dengan tanda bintang". Kami menerima permintaan tersebut, menjalankannya, kami tidak memiliki alias apa pun - kami hanya membacanya dua kali dari CTE yang sama.
Kami melihat rencananya - apa masalahnya? Mengapa kami memiliki alias? Kami tidak memesannya. Dari mana dia mendapatkan “nomor nomor” seperti itu?
PostgreSQL menambahkannya sendiri. Anda hanya perlu memahaminya hanya alias seperti itu bagi kami, untuk keperluan perbandingan dengan rencana, itu tidak masuk akal, hanya ditambahkan di sini. Mari kita tidak memperhatikan dia.
Kedua tugas "dengan tanda bintang": jika kita membaca dari tabel yang dipartisi, maka kita akan mendapatkan sebuah node Append или Merge Append, yang akan terdiri dari sejumlah besar “anak”, dan masing-masing akan menjadi satu kesatuan Scan'om dari bagian tabel: Seq Scan, Bitmap Heap Scan или Index Scan. Namun, bagaimanapun juga, "anak-anak" ini tidak akan menjadi kueri yang rumit - dari sinilah node-node ini dapat dibedakan Append di UNION.
Kami juga memahami simpul seperti itu, mengumpulkannya “dalam satu tumpukan” dan berkata: “semua yang Anda baca dari megatable ada di sini dan di bawah".
Node penerima data "sederhana".
Values Scan sesuai dengan rencana VALUES dalam permintaan.
Result adalah permintaan tanpa FROM suka SELECT 1. Atau ketika Anda sengaja membuat ekspresi palsu WHERE-block (kemudian atribut muncul One-Time Filter):
EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1Result (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 “memetakan” ke SRF dengan nama yang sama.
Namun dengan kueri bersarang, segalanya menjadi lebih rumit - sayangnya, kueri tersebut tidak selalu berubah menjadi InitPlan/SubPlan. Terkadang mereka berubah menjadi ... Join или ... Anti Join, terutama ketika Anda menulis sesuatu seperti WHERE NOT EXISTS .... Dan di sini tidak selalu mungkin untuk menggabungkannya - dalam teks rencana tidak ada operator yang sesuai dengan node rencana.
Lagi tugas "dengan tanda bintang": beberapa VALUES dalam permintaan. Dalam hal ini dan dalam rencana Anda akan mendapatkan beberapa node Values Scan.
Sufiks “bernomor” akan membantu membedakannya satu sama lain - sufiks tersebut ditambahkan persis sesuai urutan ditemukannya sufiks yang sesuai VALUES-memblokir sepanjang permintaan dari atas ke bawah.
Pengolahan data
Sepertinya semua permintaan kami telah beres - yang tersisa hanyalah Limit.
Tapi di sini semuanya sederhana - node seperti Limit, Sort, Aggregate, WindowAgg, Unique “petakan” satu-ke-satu ke operator terkait dalam permintaan, jika mereka ada. Tidak ada “bintang” atau kesulitan di sini.
BERGABUNG
Kesulitan muncul ketika kita ingin menggabungkan JOIN antara mereka sendiri. Hal ini tidak selalu memungkinkan, namun mungkin saja terjadi.
Dari sudut pandang parser kueri, kami memiliki sebuah node JoinExpr, yang memiliki dua anak - kiri dan kanan. Oleh karena itu, inilah yang “di atas” GABUNG Anda dan apa yang tertulis “di bawah” dalam permintaan.
Dan dari sudut pandang rencana, ini adalah dua keturunan dari beberapa * Loop/* Join-simpul. Nested Loop, Hash Anti Join,... - sesuatu seperti itu.
Mari kita gunakan logika sederhana: jika kita memiliki tabel A dan B yang “bergabung” satu sama lain dalam rencana, maka dalam permintaan tabel tersebut dapat ditempatkan juga A-JOIN-BAtau B-JOIN-A. Coba kita gabungkan dengan cara ini, coba gabungkan sebaliknya, dan seterusnya hingga kita kehabisan pasangan tersebut.
Mari kita ambil pohon sintaksis kita, ambil rencana kita, lihatlah... tidak serupa!
Mari kita gambar ulang dalam bentuk grafik - oh, sudah terlihat seperti sesuatu!
Perhatikan bahwa kita memiliki node yang secara bersamaan memiliki anak B dan C - kita tidak peduli urutannya. Mari gabungkan keduanya dan balikkan gambar simpulnya.
Mari kita lihat lagi. Sekarang kami memiliki node dengan anak A dan pasangan (B + C) - yang juga kompatibel dengannya.
Besar! Ternyata kita berdua ini JOIN dari permintaan dengan node rencana berhasil digabungkan.
Sayangnya, masalah ini tidak selalu terpecahkan.
Misalnya saja jika dalam sebuah permintaan A JOIN B JOIN C, dan dalam rencana, pertama-tama, node "luar" A dan C terhubung.Tetapi tidak ada operator seperti itu dalam permintaan, kami tidak memiliki apa pun untuk disorot, tidak ada yang perlu dilampirkan petunjuk. Sama halnya dengan "koma" saat Anda menulis A, B.
Namun, dalam kebanyakan kasus, hampir semua node dapat "dilepaskan" dan Anda bisa mendapatkan pembuatan profil semacam ini tepat waktu - secara harfiah, seperti di Google Chrome saat Anda menganalisis kode JavaScript. Anda dapat melihat berapa lama waktu yang dibutuhkan setiap baris dan setiap pernyataan untuk “dieksekusi”.
Dan untuk memudahkan Anda menggunakan semua ini, kami telah membuat penyimpanan , tempat Anda dapat menyimpan dan nanti menemukan rencana Anda beserta permintaan terkait atau membagikan tautan dengan seseorang.
Jika Anda hanya perlu membawa kueri yang tidak dapat dibaca ke dalam bentuk yang memadai, gunakan .
Sumber: www.habr.com