... ke dalam kueri yang dirancang dengan baik dengan petunjuk kontekstual untuk node rencana yang sesuai:
Dalam transkrip bagian kedua dari ceramah saya di PGConf. Rusia 2020, saya akan memberi tahu Anda bagaimana kami berhasil melakukan ini.
Transkrip bagian pertama, yang membahas masalah kinerja kueri yang khas dan solusinya, dapat ditemukan di artikel "Resep untuk kueri SQL yang sakit" .
Pertama, kami akan melukis - dan kami tidak akan lagi mengecat denah, kami telah melukisnya, kami sudah memilikinya dengan indah dan dapat dimengerti, tetapi sebuah permintaan.
Bagi kami, kueri yang ditarik dari log dengan "sheet" yang tidak diformat terlihat sangat jelek dan karenanya tidak nyaman.
Terutama ketika pengembang dalam kode "merekatkan" badan permintaan (ini, tentu saja, anti-pola, tetapi itu terjadi) dalam satu baris. Kengerian!
Mari menggambarnya dengan lebih indah.
Dan jika kita dapat menggambarnya dengan indah, yaitu, membongkar dan memasang kembali badan permintaan, kemudian kita dapat melampirkan petunjuk ke setiap objek permintaan ini - apa yang terjadi pada titik yang sesuai dalam rencana.
Pohon kueri sintaks
Untuk melakukan ini, permintaan harus diurai terlebih dahulu.
Karena inti sistem kami berjalan di NodeJS , kami membuat modul untuknya, Anda dapat menemukannya di GitHub . Nyatanya, ini diperpanjang "binding" ke internal parser PostgreSQL itu sendiri. Artinya, tata bahasanya hanya dikompilasi dalam biner dan binding dibuat dari sisi NodeJS. Kami mengambil modul orang lain sebagai dasar - tidak ada rahasia besar di sini.
Kami memberi makan tubuh permintaan ke input fungsi kami - pada output kami mendapatkan pohon sintaks yang diuraikan dalam bentuk objek JSON.
Sekarang Anda dapat melewati pohon ini ke arah yang berlawanan dan mengumpulkan permintaan dengan indentasi, pewarnaan, pemformatan yang kita inginkan. Tidak, ini tidak dapat dikonfigurasi, tetapi menurut kami ini akan memudahkan.
Memetakan Query dan Plan Nodes
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 - kami memiliki permintaan yang menghasilkan CTE dan membacanya dua kali. Dia menghasilkan rencana seperti itu.
CTE
Jika dilihat dengan seksama, sebelum versi ke-12 (atau dimulai dengan kata kunci
MATERIALIZED), pembentukan CTE merupakan penghalang mutlak bagi para perencana .
Ini berarti bahwa jika kita melihat generasi CTE di suatu tempat di request dan di suatu tempat di dalam suatu node
CTE, maka node ini pasti "bertarung" satu sama lain, kita dapat segera menggabungkannya.
Masalah asterisk : CTE bisa disarangkan.
Ada yang bertingkat sangat buruk, dan bahkan nama yang sama. Misalnya, Anda dapat
CTE Amelakukannya di dalam CTE X, dan CTE Bmelakukannya lagi di tingkat yang sama di dalam CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Anda harus memahami ini saat membandingkan. Sangat sulit untuk memahami ini dengan "mata" - bahkan melihat rencananya, bahkan melihat isi permintaan. Jika pembuatan CTE Anda kompleks, bersarang, permintaan besar - maka itu sama sekali tidak disadari.
PERSATUAN
Jika kami memiliki kata kunci dalam kueri kami
UNION [ALL](operator untuk menggabungkan dua pilihan), maka salah satu node Appendatau seseorang sesuai dengannya dalam rencana Recursive Union.
Apa yang "di atas" di atas
UNIONadalah anak pertama dari simpul kita, yang "di bawah" adalah yang kedua. Jika UNIONbeberapa blok "dilem" melalui kita sekaligus, maka Appendhanya akan ada satu -node, tetapi tidak akan memiliki dua anak, tetapi banyak - sesuai urutannya, masing-masing:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Masalah "dengan tanda bintang" : di dalam pembuatan pilihan rekursif (
WITH RECURSIVE) bisa juga ada lebih dari satu UNION. Tetapi hanya blok terakhir setelah yang terakhir selalu 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 "menempelkan" contoh seperti itu. Dalam contoh ini, kami melihat
UNIONada 3 segmen dalam permintaan kami. Karenanya, satu UNION berkorespondensi dengan Append-node, dan yang lainnya berkorespondensi dengan Recursive Union.
Data baca-tulis
Itu saja, kami sebarkan, sekarang kami tahu bagian mana dari permintaan yang sesuai dengan bagian mana dari rencana itu. Dan dalam potongan ini kita dapat dengan mudah dan alami menemukan objek yang "dapat dibaca".
Dari sudut pandang kueri, kami tidak tahu apakah ini adalah tabel atau CTE, tetapi dilambangkan dengan simpul yang sama
RangeVar. Dan dalam istilah "dapat dibaca" - 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]
Kita tahu struktur rencana dan kuerinya, kita tahu korespondensi bloknya, kita tahu nama-nama objeknya - kita membuat perbandingan yang jelas.
Sekali lagi, masalah tanda bintang . Kami mengambil permintaan, menjalankannya, kami tidak memiliki alias - kami hanya membacanya dua kali dari satu CTE.
Kami melihat rencananya - apa masalahnya? Mengapa alias kami keluar? Kami tidak memesannya. Dari mana dia berasal dari "plat nomor" seperti itu?
PostgreSQL menambahkannya sendiri. Anda hanya perlu memahami bahwa alias semacam itu tidak masuk akal bagi kami untuk tujuan perbandingan dengan rencana, itu hanya ditambahkan di sini. Jangan perhatikan dia. Tugas
kedua adalah "dengan tanda bintang" : jika kita membaca dari tabel yang dipartisi, maka kita akan mendapatkan node
AppendatauMerge Append, Yang akan terdiri dari sejumlah besar "anak-anak", dan masing-masing yang entah bagaimana Scan'th dari bagian meja: Seq Scan, Bitmap Heap Scanatau Index Scan. Namun, dalam kasus apa pun, "turunan" ini bukanlah kueri kompleks - begitulah cara node ini dapat dibedakan dari Appendkapan UNION.
Kami juga memahami simpul seperti itu, kami mengumpulkannya "dalam satu tumpukan" dan berkata: " semua yang Anda baca dari megatable ada di sini dan di bawah pohon ."
Node "sederhana" untuk menerima data
Values Scandalam rencana pertandingan yang VALUESdiminta.
Result- ini adalah permintaan tanpa FROMsuka SELECT 1. Atau ketika Anda memiliki ekspresi yang salah di 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"Petakan" ke SRF dengan nama yang sama.
Tetapi dengan kueri bersarang, semuanya menjadi lebih rumit - sayangnya, kueri tersebut tidak selalu berubah menjadi
InitPlan/ SubPlan. Terkadang mereka berubah menjadi ... Joinatau ... Anti Join, terutama saat Anda menulis sesuatu seperti WHERE NOT EXISTS .... Dan tidak selalu mungkin untuk menggabungkan di sana - tidak ada operator yang sesuai dengan node rencana dalam teks rencana.
Sekali lagi, tugas dengan tanda bintang : beberapa
VALUESdalam permintaan. Dalam kasus ini dan dalam rencana, Anda akan menerima beberapa node Values Scan.
Sufiks "bernomor" akan membantu membedakannya dari satu sama lain - ditambahkan tepat dalam urutan menemukan
VALUES-block yang sesuai di sepanjang permintaan dari atas ke bawah.
Pengolahan data
Tampaknya semua dalam permintaan kami telah diselesaikan - hanya ada satu yang tersisa
Limit.
Tapi semuanya sederhana - seperti node
Limit, Sort, Aggregate, WindowAgg, Unique"mapyatsya" satu-ke-satu ke yang sesuai pernyataan dalam permintaan, jika mereka ada. Tidak ada "bintang" dan tidak ada kesulitan.
IKUTI
Kesulitan muncul saat kita ingin menggabungkan satu
JOINsama lain. Ini tidak selalu dilakukan, tetapi Anda bisa.
Dari sudut pandang parser permintaan, kami memiliki node
JoinExpryang memiliki tepat dua anak - kiri dan kanan. Ini, masing-masing, adalah apa yang "di atas" GABUNG Anda dan apa yang "di bawah" itu dalam permintaan tertulis.
Dan dari sudut pandang rencana, ini adalah dua turunan dari some
* Loop/ * Join-node. Nested Loop, Hash Anti Join... - itu sesuatu.
Mari kita gunakan logika sederhana: jika kita memiliki pelat A dan B, yang "bergabung" satu sama lain dalam rencana, maka dalam permintaan pelat-pelat itu dapat ditempatkan
A-JOIN-Batau B-JOIN-A. Mari kita coba gabungkan dengan cara ini, coba gabungkan sebaliknya, dan seterusnya sampai pasangan tersebut habis.
Ambil pohon sintaks kami, ambil garis besar kami, lihat mereka ... tidak terlihat seperti itu!
Mari kita gambar ulang dalam bentuk grafik - oh, ini sudah menjadi seperti sesuatu!
Mari kita perhatikan bahwa kita memiliki node yang memiliki turunan B dan C pada saat yang sama - kita tidak peduli dengan urutan yang mana. Mari kita gabungkan dan balikkan gambar node.
Mari kita lihat lagi. Sekarang kami memiliki node dengan anak A dan pasangan (B + C) - kompatibel dengan mereka juga.
Luar biasa! Ternyata kami telah
JOINberhasil menggabungkan keduanya dari kueri dengan node rencana.
Sayangnya, tugas ini tidak selalu bisa diselesaikan.
Misalnya, jika dalam kueri
A JOIN B JOIN C, tetapi dalam rencana, node "ekstrim" A dan C terhubung pertama-tama. Dan dalam kueri tidak ada operator seperti itu, kami tidak memiliki apa-apa untuk disorot, tidak ada yang mengikat petunjuk. Ini sama dengan "koma" saat Anda menulis A, B.
Namun, dalam banyak kasus, hampir semua node dapat "dilepaskan" dan Anda mendapatkan profil semacam ini di sebelah kiri tepat waktu - secara harfiah, seperti di Google Chrome, saat Anda menganalisis kode JavaScript. Anda dapat melihat berapa lama setiap baris dan setiap pernyataan "dieksekusi".
Dan untuk membuatnya lebih nyaman bagi Anda untuk menggunakan semua ini, kami membuat penyimpanan arsip , tempat Anda dapat menyimpan dan kemudian menemukan paket Anda bersama dengan permintaan terkait atau berbagi tautan dengan seseorang.
Jika Anda hanya perlu memasukkan kueri yang tidak terbaca ke dalam formulir yang memadai, gunakan "normalizer" kami .
