PostgreSQL क्वेरी प्रोफाइलर: योजना आणि क्वेरी कशी जुळवायची

अनेक जे आधीच वापरत आहेत explain.tensor.ru - आमच्या PostgreSQL योजना व्हिज्युअलायझेशन सेवेला कदाचित त्याच्या एका महासत्तेबद्दल माहिती नसेल - सर्व्हर लॉगचा वाचायला कठीण भाग बदलणे...

... संबंधित योजना नोड्ससाठी संदर्भित सूचनांसह सुंदर डिझाइन केलेल्या क्वेरीमध्ये:

त्याच्या दुसऱ्या भागाच्या या उतार्‍यात PGConf.Russia 2020 येथे अहवाल आम्ही हे कसे केले ते मी तुम्हाला सांगेन.

पहिल्या भागाचा उतारा, सामान्य क्वेरी कार्यप्रदर्शन समस्या आणि त्यांचे निराकरण करण्यासाठी समर्पित, लेखात आढळू शकते "आजार असलेल्या SQL प्रश्नांसाठी पाककृती".

प्रथम, चला रंग देणे सुरू करूया - आणि आम्ही यापुढे योजनेला रंग देणार नाही, आम्ही ते आधीच रंगविले आहे, आमच्याकडे ते आधीपासूनच सुंदर आणि समजण्यासारखे आहे, परंतु एक विनंती.

आम्हाला असे वाटले की अशा स्वरूपित नसलेल्या "शीट" सह लॉगमधून खेचलेली विनंती खूप कुरूप दिसते आणि म्हणून गैरसोयीची आहे.


विशेषत: जेव्हा विकसक कोडमधील विनंतीच्या मुख्य भागाला एका ओळीत “गोंद” करतात (हे अर्थातच अँटीपॅटर्न आहे, परंतु असे घडते). भयानक!

चला हे आणखी सुंदर चित्र काढूया.


आणि जर आपण हे सुंदरपणे काढू शकतो, म्हणजे, विनंतीचा मुख्य भाग वेगळे करू शकतो आणि परत एकत्र ठेवू शकतो, तर आपण या विनंतीच्या प्रत्येक ऑब्जेक्टला एक इशारा "संलग्न" करू शकतो - योजनेतील संबंधित बिंदूवर काय घडले.

वाक्यरचना ट्री क्वेरी करा

हे करण्यासाठी, विनंती प्रथम विश्लेषित करणे आवश्यक आहे.


कारण आमच्याकडे आहे प्रणालीचा कोर NodeJS वर चालतो, मग आम्ही त्यासाठी एक मॉड्यूल बनवले, तुम्ही करू शकता GitHub वर शोधा. खरं तर, हे पोस्टग्रेएसक्यूएल पार्सरच्या अंतर्गत भागांसाठी विस्तारित "बाइंडिंग्ज" आहेत. म्हणजेच, व्याकरण फक्त बायनरी संकलित केले जाते आणि त्यास नोडजेएस वरून बाइंडिंग केले जाते. आम्ही इतर लोकांचे मॉड्यूल एक आधार म्हणून घेतले - येथे कोणतेही मोठे रहस्य नाही.

आम्ही विनंतीचा मुख्य भाग आमच्या फंक्शनमध्ये इनपुट म्हणून फीड करतो - आउटपुटवर आम्हाला JSON ऑब्जेक्टच्या रूपात एक पार्स केलेले वाक्यरचना ट्री मिळते.


आता आपण या झाडामधून उलट दिशेने धावू शकतो आणि आपल्याला हवे असलेले इंडेंट, रंग आणि स्वरूपनासह विनंती एकत्र करू शकतो. नाही, हे सानुकूल करण्यायोग्य नाही, परंतु आम्हाला असे वाटले की हे सोयीचे असेल.

मॅपिंग क्वेरी आणि योजना नोड्स

आता आपण पहिल्या चरणात विश्‍लेषित केलेली योजना आणि दुसऱ्या चरणात विश्‍लेषित केलेली क्वेरी यांची सांगड कशी घालता येईल ते पाहू.

एक साधे उदाहरण घेऊ - आमच्याकडे एक क्वेरी आहे जी CTE जनरेट करते आणि त्यातून दोनदा वाचते. तो अशी योजना तयार करतो.

टे दे

तुम्ही नीट पाहिल्यास, आवृत्ती १२ पर्यंत (किंवा त्यातून कीवर्डसह प्रारंभ करा MATERIALIZED) निर्मिती सीटीई हा नियोजकासाठी पूर्ण अडथळा आहे.


याचा अर्थ असा की आम्हाला विनंतीमध्ये कुठेतरी CTE जनरेशन आणि प्लॅनमध्ये कुठेतरी नोड दिसला CTE, मग हे नोड्स निश्चितपणे एकमेकांशी “लढा” करतात, आम्ही त्यांना त्वरित एकत्र करू शकतो.

तारकासह समस्या: CTEs नेस्टेड केले जाऊ शकते.

खूप खराब घरटे आहेत आणि अगदी त्याच नावाचे आहेत. उदाहरणार्थ, आपण आत करू शकता CTE A बनवा CTE X, आणि आत समान पातळीवर CTE B ते पुन्हा करा CTE X:

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

तुलना करताना, आपण हे समजून घेतले पाहिजे. हे “तुमच्या डोळ्यांनी” समजून घेणे - अगदी योजना पाहणे, अगदी विनंतीचे शरीर पाहणे - खूप कठीण आहे. जर तुमची CTE जनरेशन गुंतागुंतीची असेल, नेस्टेड असेल आणि विनंत्या मोठ्या असतील, तर ते पूर्णपणे बेशुद्ध आहे.

युनियन

आमच्याकडे क्वेरीमध्ये कीवर्ड असल्यास UNION [ALL] (दोन नमुने जोडण्याचा ऑपरेटर), नंतर योजनेत ते एकतर नोडशी संबंधित आहे Append, किंवा काही Recursive Union.


जे वर "वर" आहे UNION - हा आमच्या नोडचा पहिला वंशज आहे, जो "खाली" आहे - दुसरा. जर माध्यमातून UNION आमच्याकडे एकाच वेळी अनेक ब्लॉक्स "गोंदलेले" आहेत Append-अजूनही फक्त एकच नोड असेल, पण त्यात दोन नाही तर बरीच मुले असतील - क्रमशः ते जातात:

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

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

तारकासह समस्या: आवर्ती सॅम्पलिंग जनरेशनच्या आत (WITH RECURSIVE) देखील एकापेक्षा जास्त असू शकतात UNION. परंतु शेवटच्या नंतरचा फक्त शेवटचा ब्लॉक नेहमी पुनरावृत्ती करणारा असतो UNION. वरील सर्व काही एक आहे, परंतु भिन्न आहे UNION:

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

तुम्हाला अशी उदाहरणे "आऊट" करण्यात सक्षम असणे देखील आवश्यक आहे. या उदाहरणात आपण ते पाहतो UNION-आमच्या विनंतीमध्ये 3 विभाग होते. त्यानुसार, एक UNION च्याशी संबंधित आहे Append-नोड, आणि इतर - Recursive Union.

डेटा वाचा-लिहा

सर्व काही मांडले आहे, आता आम्हाला माहित आहे की विनंतीचा कोणता भाग योजनेच्या कोणत्या भागाशी संबंधित आहे. आणि या तुकड्यांमध्ये आपण सहजपणे आणि नैसर्गिकरित्या "वाचण्यायोग्य" वस्तू शोधू शकतो.

क्वेरीच्या दृष्टिकोनातून, आम्हाला माहित नाही की ते टेबल आहे की CTE, परंतु ते समान नोडद्वारे नियुक्त केले आहेत RangeVar. आणि "वाचनीयता" च्या दृष्टीने, हे नोड्सचा एक मर्यादित संच देखील आहे:

• 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]

आम्हाला योजना आणि क्वेरीची रचना माहित आहे, आम्हाला ब्लॉक्सचा पत्रव्यवहार माहित आहे, आम्हाला वस्तूंची नावे माहित आहेत - आम्ही एक-एक तुलना करतो.


पुन्हा "तारकासह" कार्य. आम्ही विनंती स्वीकारतो, ती कार्यान्वित करतो, आमच्याकडे कोणतेही उपनाव नाहीत - आम्ही ती एकाच CTE वरून फक्त दोनदा वाचतो.


आम्ही योजना पाहतो - काय समस्या आहे? आम्हाला उपनाव का झाला? आम्ही ते ऑर्डर केले नाही. त्याला असा "नंबर नंबर" कुठे मिळेल?

PostgreSQL ते स्वतः जोडते. आपण फक्त ते समजून घेणे आवश्यक आहे फक्त असे उपनाम आमच्यासाठी, योजनेशी तुलना करण्याच्या हेतूने, त्यास काही अर्थ नाही, ते येथे जोडले आहे. चला त्याच्याकडे लक्ष देऊ नका.

दुसरा "तारकासह" कार्य: जर आपण विभाजन केलेल्या टेबलवरून वाचत असाल, तर आपल्याला एक नोड मिळेल Append किंवा Merge Append, ज्यामध्ये मोठ्या संख्येने "मुले" असतील आणि त्यापैकी प्रत्येक कसा तरी असेल Scanटेबल-विभागातून 'om: Seq Scan, Bitmap Heap Scan किंवा Index Scan. परंतु, कोणत्याही परिस्थितीत, ही "मुले" जटिल प्रश्न नसतील - अशा प्रकारे या नोड्सपासून वेगळे केले जाऊ शकते. Append येथे UNION.


आम्हाला अशा गाठी देखील समजतात, त्या “एका ढिगाऱ्यात” गोळा करा आणि म्हणा: “तुम्ही megatable वरून वाचलेले सर्व काही येथे आणि झाडाच्या खाली आहे".

"साधे" डेटा प्राप्त करणारे नोड्स

Values Scan योजनेशी सुसंगत आहे VALUES विनंती मध्ये.

Result शिवाय विनंती आहे FROM जसे SELECT 1. किंवा जेव्हा तुमच्यामध्ये जाणीवपूर्वक खोटी अभिव्यक्ती असते WHERE-ब्लॉक (नंतर विशेषता दिसते 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 त्याच नावाच्या SRF चा “नकाशा”.

परंतु नेस्टेड क्वेरींसह सर्वकाही अधिक क्लिष्ट आहे - दुर्दैवाने, ते नेहमीच बदलत नाहीत InitPlan/SubPlan. कधीकधी ते मध्ये बदलतात ... Join किंवा ... Anti Join, विशेषतः जेव्हा तुम्ही असे काहीतरी लिहिता WHERE NOT EXISTS .... आणि येथे त्यांना एकत्र करणे नेहमीच शक्य नसते - योजनेच्या मजकुरात योजनेच्या नोड्सशी संबंधित कोणतेही ऑपरेटर नाहीत.

पुन्हा "तारकासह" कार्य: काही VALUES विनंती मध्ये. या प्रकरणात आणि योजनेमध्ये आपल्याला अनेक नोड्स मिळतील Values Scan.


"क्रमांकित" प्रत्यय त्यांना एकमेकांपासून वेगळे करण्यात मदत करतील - ते ज्या क्रमाने संबंधित आहेत त्या क्रमाने जोडले जातात. VALUES- वरपासून खालपर्यंत विनंतीसह अवरोधित करा.

डेटा प्रोसेसिंग

असे दिसते की आमच्या विनंतीतील सर्व काही सोडवले गेले आहे - फक्त बाकी आहे Limit.


परंतु येथे सर्वकाही सोपे आहे - जसे की नोड्स Limit, Sort, Aggregate, WindowAgg, Unique विनंतीमध्ये संबंधित ऑपरेटर्सना एक ते एक “नकाशा” द्या, जर ते तेथे असतील तर. येथे कोणतेही "तारे" किंवा अडचणी नाहीत.

सामील व्हा

जेव्हा आपल्याला एकत्र करायचे असते तेव्हा अडचणी येतात JOIN आपापसात. हे नेहमीच शक्य नाही, परंतु हे शक्य आहे.


क्वेरी पार्सरच्या दृष्टिकोनातून, आमच्याकडे एक नोड आहे JoinExpr, ज्याला अगदी दोन मुले आहेत - डावे आणि उजवे. हे, त्यानुसार, तुमच्या सामील होण्यासाठी "वर" काय आहे आणि विनंतीमध्ये "खाली" काय लिहिले आहे.

आणि योजनेच्या दृष्टिकोनातून, हे काहींचे दोन वंशज आहेत * Loop/* Join-नोड. Nested Loop, Hash Anti Join,... - तशा प्रकारे काहीतरी.

चला सोप्या तर्काचा वापर करूया: जर आमच्याकडे प्लॅनमध्‍ये एकमेकांना "सामील" होणार्‍या टेबल्स A आणि B असतील, तर विनंतीनुसार ते एकतर शोधू शकतात. A-JOIN-B, किंवा B-JOIN-A. चला अशा प्रकारे एकत्र करण्याचा प्रयत्न करूया, इतर मार्गाने एकत्र करण्याचा प्रयत्न करूया आणि अशा जोड्या संपेपर्यंत.

चला आमचा सिंटॅक्स ट्री घेऊ, आमचा प्लॅन घेऊ, त्यांच्याकडे पाहू... सारखे नाही!


चला ते आलेखांच्या रूपात पुन्हा काढू - अरे, ते आधीच काहीतरी दिसते आहे!


चला लक्षात घ्या की आमच्याकडे नोड्स आहेत ज्यात एकाच वेळी बी आणि सी मुले आहेत - आम्हाला कोणत्या क्रमाने काळजी नाही. चला त्यांना एकत्र करू आणि नोडचे चित्र उलट करू.


पुन्हा बघूया. आता आमच्याकडे मुलांसह नोड्स A आणि जोड्या (B + C) आहेत - त्यांच्याशी सुसंगत.


छान! हे आम्ही दोघे आहोत हे कळते JOIN विनंतीवरून प्लॅन नोड्स यशस्वीरित्या एकत्र केले गेले.

अरेरे, ही समस्या नेहमीच सोडवली जात नाही.


उदाहरणार्थ, विनंती असल्यास A JOIN B JOIN C, आणि योजनेमध्ये, सर्व प्रथम, "बाह्य" नोड्स A आणि C जोडलेले होते. परंतु विनंतीमध्ये असा कोणताही ऑपरेटर नाही, आमच्याकडे हायलाइट करण्यासाठी काहीही नाही, इशारा जोडण्यासाठी काहीही नाही. तुम्ही लिहिता तेव्हा "स्वल्पविराम" बरोबरच आहे A, B.

परंतु, बहुतेक प्रकरणांमध्ये, जवळजवळ सर्व नोड्स "उघडलेले" असू शकतात आणि आपण वेळेत डावीकडे अशा प्रकारचे प्रोफाइलिंग मिळवू शकता - अक्षरशः, जसे की Google Chrome मध्ये आपण JavaScript कोडचे विश्लेषण करता. प्रत्येक ओळ आणि प्रत्येक विधान “कार्यान्वीत” होण्यासाठी किती वेळ लागला ते तुम्ही पाहू शकता.


आणि हे सर्व वापरणे तुमच्यासाठी अधिक सोयीचे व्हावे यासाठी आम्ही स्टोरेज तयार केले आहे संग्रह, जिथे तुम्ही जतन करू शकता आणि नंतर संबंधित विनंत्यांसह तुमच्या योजना शोधू शकता किंवा एखाद्यासोबत लिंक शेअर करू शकता.

तुम्हाला न वाचता येणारी क्वेरी पुरेशा स्वरूपात आणायची असल्यास, वापरा आमचे "नॉर्मलायझर".

स्त्रोत: www.habr.com