🥇PostgreSQL क्वेरी प्रोफाइलर: योजना और क्वेरी का मिलान कैसे करें

कई लोग जो पहले से ही उपयोग कर रहे हैं व्याख्या.टेंसर.आरयू - हमारी PostgreSQL योजना विज़ुअलाइज़ेशन सेवा को इसकी महाशक्तियों में से एक के बारे में पता नहीं हो सकता है - सर्वर लॉग के एक कठिन-से-पढ़ने वाले टुकड़े को मोड़ना ...

... संबंधित योजना नोड्स के लिए प्रासंगिक संकेतों के साथ एक खूबसूरती से डिज़ाइन की गई क्वेरी में:

उनके दूसरे भाग की इस प्रतिलिपि में PGConf.Russia 2020 पर रिपोर्ट मैं आपको बताऊंगा कि हम यह कैसे करने में कामयाब रहे।

विशिष्ट क्वेरी प्रदर्शन समस्याओं और उनके समाधानों के लिए समर्पित पहले भाग की प्रतिलेख, लेख में पाया जा सकता है "खराब एसक्यूएल प्रश्नों के लिए व्यंजन विधि".

सबसे पहले, आइए रंग भरना शुरू करें - और हम अब योजना को रंग नहीं देंगे, हमने इसे पहले ही रंग दिया है, हमारे पास पहले से ही यह सुंदर और समझने योग्य है, लेकिन एक अनुरोध है।

हमें ऐसा लगा कि बिना स्वरूपित "शीट" के साथ लॉग से निकाला गया अनुरोध बहुत बदसूरत और इसलिए असुविधाजनक लग रहा है।

खासकर जब डेवलपर्स कोड में अनुरोध के मुख्य भाग को एक पंक्ति में "चिपका" देते हैं (यह, निश्चित रूप से, एक एंटीपैटर्न है, लेकिन ऐसा होता है)। भयंकर!

आइए इसे किसी तरह और अधिक खूबसूरती से बनाएं।

और यदि हम इसे खूबसूरती से चित्रित कर सकते हैं, यानी अनुरोध के मुख्य भाग को अलग कर सकते हैं और वापस एक साथ रख सकते हैं, तो हम इस अनुरोध के प्रत्येक ऑब्जेक्ट के लिए एक संकेत "संलग्न" कर सकते हैं - योजना में संबंधित बिंदु पर क्या हुआ।

क्वेरी सिंटैक्स ट्री

ऐसा करने के लिए, अनुरोध को पहले पार्स किया जाना चाहिए।

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

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

अब हम इस पेड़ के माध्यम से विपरीत दिशा में चल सकते हैं और इंडेंट, रंग और फ़ॉर्मेटिंग के साथ एक अनुरोध इकट्ठा कर सकते हैं जो हम चाहते हैं। नहीं, यह अनुकूलन योग्य नहीं है, लेकिन हमें ऐसा लगा कि यह सुविधाजनक होगा।

मैपिंग क्वेरी और योजना नोड्स

अब आइए देखें कि हम उस योजना को कैसे जोड़ सकते हैं जिसका हमने पहले चरण में विश्लेषण किया था और जिस क्वेरी का हमने दूसरे चरण में विश्लेषण किया था।

आइए एक सरल उदाहरण लें - हमारे पास एक क्वेरी है जो सीटीई उत्पन्न करती है और इसे दो बार पढ़ती है। वह ऐसी योजना बनाता है।

सीटीई

अगर आप इसे ध्यान से देखें तो संस्करण 12 तक (या कीवर्ड से शुरू करके)। MATERIALIZED) गठन सीटीई योजनाकार के लिए एक पूर्ण बाधा है.

इसका मतलब यह है कि अगर हम अनुरोध में कहीं सीटीई पीढ़ी और योजना में कहीं नोड देखते हैं CTE, तो ये नोड्स निश्चित रूप से एक दूसरे के साथ "लड़ाई" करते हैं, हम उन्हें तुरंत जोड़ सकते हैं।

तारांकन के साथ समस्या: सीटीई को नेस्ट किया जा सकता है।

बहुत ही खराब नेस्टेड हैं, और यहां तक कि एक ही नाम के भी हैं। उदाहरण के लिए, आप अंदर कर सकते हैं CTE A बनाना CTE X, और अंदर समान स्तर पर CTE B इसे फिर से करें CTE X:

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

तुलना करते समय आपको यह अवश्य समझना चाहिए। इसे "अपनी आँखों से" समझना - यहाँ तक कि योजना को देखना, यहाँ तक कि अनुरोध के मुख्य भाग को देखना - बहुत कठिन है। यदि आपकी सीटीई पीढ़ी जटिल, नेस्टेड है और अनुरोध बड़े हैं, तो यह पूरी तरह से अचेतन है।

संघ

यदि हमारे पास क्वेरी में कोई कीवर्ड है 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]

हम योजना और क्वेरी की संरचना जानते हैं, हम ब्लॉकों के पत्राचार को जानते हैं, हम वस्तुओं के नाम जानते हैं - हम एक-से-एक तुलना करते हैं।

फिर कार्य "तारांकन के साथ". हम अनुरोध लेते हैं, इसे निष्पादित करते हैं, हमारे पास कोई उपनाम नहीं है - हम इसे एक ही सीटीई से दो बार पढ़ते हैं।

हम योजना को देखते हैं - समस्या क्या है? हमारे पास उपनाम क्यों था? हमने इसका ऑर्डर नहीं दिया. उसे ऐसा "नंबर नंबर" कहां से मिलता है?

PostgreSQL इसे स्वयं जोड़ता है। आपको बस इसे समझने की जरूरत है ऐसा ही एक उपनाम हमारे लिए, योजना के साथ तुलना के उद्देश्य से, इसका कोई मतलब नहीं है, इसे बस यहां जोड़ा गया है। आइए उस पर ध्यान न दें.

दूसरा कार्य "तारांकन के साथ": यदि हम एक विभाजित तालिका से पढ़ रहे हैं, तो हमें एक नोड मिलेगा Append या Merge Append, जिसमें बड़ी संख्या में "बच्चे" शामिल होंगे, और जिनमें से प्रत्येक किसी न किसी तरह से होगा Scanॐ तालिका-खण्ड से: Seq Scan, Bitmap Heap Scan या Index Scan. लेकिन, किसी भी मामले में, ये "बच्चे" जटिल प्रश्न नहीं होंगे - इस तरह से इन नोड्स को अलग किया जा सकता है Append में UNION.

हम ऐसी गांठों को भी समझते हैं, उन्हें "एक ढेर में" इकट्ठा करते हैं और कहते हैं: "मेगाटेबल से आप जो कुछ भी पढ़ते हैं वह यहां और पेड़ के नीचे है".

"सरल" डेटा प्राप्त करने वाले नोड

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 एक ही नाम के एसआरएफ के लिए "मानचित्र"।

लेकिन नेस्टेड प्रश्नों के साथ सब कुछ अधिक जटिल है - दुर्भाग्य से, वे हमेशा इसमें परिवर्तित नहीं होते हैं InitPlan/SubPlan. कभी-कभी वे बदल जाते हैं ... Join या ... Anti Join, खासकर जब आप कुछ ऐसा लिखते हैं WHERE NOT EXISTS .... और यहां उन्हें संयोजित करना हमेशा संभव नहीं होता है - योजना के पाठ में योजना के नोड्स के अनुरूप कोई ऑपरेटर नहीं होते हैं।

फिर कार्य "तारांकन के साथ": कुछ VALUES अनुरोध में. इस मामले में और प्लान में आपको कई नोड मिलेंगे Values Scan.

"क्रमांकित" प्रत्यय उन्हें एक दूसरे से अलग करने में मदद करेंगे - वे ठीक उसी क्रम में जोड़े जाते हैं जिस क्रम में संबंधित प्रत्यय पाए जाते हैं VALUES-अनुरोध को ऊपर से नीचे तक ब्लॉक करता है।

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

ऐसा लगता है जैसे हमारे अनुरोध में सब कुछ सुलझा लिया गया है - जो कुछ बचा है Limit.

लेकिन यहां सब कुछ सरल है - जैसे नोड्स Limit, Sort, Aggregate, WindowAgg, Unique अनुरोध में संबंधित ऑपरेटरों को एक-से-एक "मैप" करें, यदि वे वहां हैं। यहां कोई "सितारे" या कठिनाइयां नहीं हैं।

शामिल हों

कठिनाइयाँ तब आती हैं जब हम गठबंधन करना चाहते हैं JOIN आपस में. यह हमेशा संभव नहीं है, लेकिन यह संभव है।

क्वेरी पार्सर के दृष्टिकोण से, हमारे पास एक नोड है JoinExpr, जिसके ठीक दो बच्चे हैं - बाएँ और दाएँ। तदनुसार, यह वही है जो आपका JOIN "ऊपर" है और जो अनुरोध में "नीचे" लिखा गया है।

और योजना की दृष्टि से ये कुछ के दो वंशज हैं * Loop/* Join-नोड. Nested Loop, Hash Anti Join,... - ऐसा कुछ।

आइए सरल तर्क का उपयोग करें: यदि हमारे पास टेबल ए और बी हैं जो योजना में एक दूसरे से "जुड़ते" हैं, तो अनुरोध में वे या तो स्थित हो सकते हैं A-JOIN-Bया B-JOIN-A. आइए इस तरह से संयोजन करने का प्रयास करें, आइए दूसरे तरीके से संयोजन करने का प्रयास करें, और इसी तरह जब तक हमारे पास ऐसे जोड़े खत्म नहीं हो जाते।

आइए अपना सिंटैक्स ट्री लें, अपनी योजना लें, उन्हें देखें... समान नहीं!

आइए इसे ग्राफ़ के रूप में फिर से बनाएं - ओह, यह पहले से ही कुछ ऐसा दिखता है!

आइए ध्यान दें कि हमारे पास ऐसे नोड हैं जिनमें एक साथ बच्चे बी और सी हैं - हमें इसकी परवाह नहीं है कि किस क्रम में। आइए उन्हें संयोजित करें और नोड की तस्वीर को पलट दें।

आइए फिर से देखें. अब हमारे पास बच्चों ए और जोड़े (बी + सी) के साथ नोड्स हैं - उनके साथ भी संगत।

महान! यह पता चला कि हम ये दो हैं JOIN योजना के साथ अनुरोध से नोड्स को सफलतापूर्वक संयोजित किया गया था।

अफ़सोस, यह समस्या हमेशा हल नहीं होती।

उदाहरण के लिए, यदि किसी अनुरोध में A JOIN B JOIN C, और योजना में, सबसे पहले, "बाहरी" नोड ए और सी जुड़े हुए थे। लेकिन अनुरोध में ऐसा कोई ऑपरेटर नहीं है, हमारे पास हाइलाइट करने के लिए कुछ भी नहीं है, संकेत संलग्न करने के लिए कुछ भी नहीं है। जब आप लिखते हैं तो "अल्पविराम" के साथ भी ऐसा ही होता है A, B.

लेकिन, ज्यादातर मामलों में, लगभग सभी नोड्स को "अनटाइड" किया जा सकता है और आप समय पर बाईं ओर इस तरह की प्रोफाइलिंग प्राप्त कर सकते हैं - शाब्दिक रूप से, Google Chrome की तरह जब आप जावास्क्रिप्ट कोड का विश्लेषण करते हैं। आप देख सकते हैं कि प्रत्येक पंक्ति और प्रत्येक कथन को "निष्पादित" होने में कितना समय लगा।

और आपके लिए इन सबका उपयोग करना अधिक सुविधाजनक बनाने के लिए, हमने भंडारण बनाया है संग्रह, जहां आप सहेज सकते हैं और बाद में संबंधित अनुरोधों के साथ अपनी योजनाएं ढूंढ सकते हैं या किसी के साथ लिंक साझा कर सकते हैं।

यदि आपको किसी अपठनीय प्रश्न को पर्याप्त रूप में लाना है, तो इसका उपयोग करें हमारा "सामान्यीकरणकर्ता".

स्रोत: www.habr.com

PostgreSQL क्वेरी प्रोफाइलर: योजना और क्वेरी का मिलान कैसे करें