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

धेरै जसो पहिले नै प्रयोग गर्दै हुनुहुन्छ व्याख्या.tensor.ru - हाम्रो PostgreSQL योजना भिजुअलाइजेशन सेवा यसको एक महाशक्ति बारे सचेत नहुन सक्छ - सर्भर लग को एक हार्ड-टु-पढ्ने टुक्रा बदल्दै...

... संगत योजना नोड्स को लागी प्रासंगिक संकेत संग एक सुन्दर डिजाइन क्वेरी मा:

उनको दोस्रो भागको यो ट्रान्सक्रिप्टमा PGConf.Russia 2020 मा रिपोर्ट म तपाईलाई बताउनेछु कि हामीले यो कसरी गर्न सफल भयौं।

पहिलो भागको ट्रान्सक्रिप्ट, सामान्य क्वेरी कार्यसम्पादन समस्याहरू र तिनीहरूका समाधानहरूमा समर्पित, लेखमा फेला पार्न सकिन्छ। "बिरामी SQL प्रश्नहरूको लागि व्यञ्जनहरू".

पहिले, रङ सुरु गरौं - र हामी अब योजनालाई रंग दिनेछैनौं, हामीले यसलाई पहिले नै रंगीन गरिसकेका छौं, हामीसँग पहिले नै यो सुन्दर र बुझ्न योग्य छ, तर अनुरोध।

यो हामीलाई लाग्थ्यो कि यस्तो अनफर्म्याट "पाना" संग लग बाट तानिएको अनुरोध धेरै कुरूप र त्यसैले असुविधाजनक देखिन्छ।


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

यसलाई अझ सुन्दर ढंगले चित्रण गरौं।


र यदि हामी यसलाई सुन्दर ढंगले कोर्न सक्छौं, त्यो हो, डिस्सेम्बल र अनुरोधको मुख्य भागलाई सँगै राख्न, त्यसपछि हामी यस अनुरोधको प्रत्येक वस्तुमा संकेत "संलग्न" गर्न सक्छौं - योजनाको सम्बन्धित बिन्दुमा के भयो।

क्वेरी सिन्ट्याक्स रूख

यो गर्नको लागि, अनुरोध पहिले पार्स गर्नुपर्छ।


किनभने हामीसँग छ प्रणालीको कोर NodeJS मा चल्छ, त्यसपछि हामीले यसको लागि मोड्युल बनायौं, तपाईं सक्नुहुन्छ GitHub मा फेला पार्नुहोस्। वास्तवमा, यी PostgreSQL पार्सरको आन्तरिकहरूमा विस्तारित "बाइन्डिङहरू" हुन्। त्यो हो, व्याकरण केवल बाइनरी कम्पाइल गरिएको छ र यसमा NodeJS बाट बाइन्डिङहरू बनाइन्छ। हामीले अन्य व्यक्तिको मोड्युललाई आधारको रूपमा लियौं - यहाँ कुनै ठूलो गोप्य छैन।

हामीले अनुरोधको मुख्य भागलाई हाम्रो प्रकार्यमा इनपुटको रूपमा फिड गर्छौं - आउटपुटमा हामीले JSON वस्तुको रूपमा पार्स गरिएको सिन्ट्याक्स रूख पाउँछौं।


अब हामी यस रूखको माध्यमबाट विपरीत दिशामा दौडन सक्छौं र हामीले चाहेको इन्डेन्ट, रङ र ढाँचाको साथ अनुरोध भेला गर्न सक्छौं। होइन, यो अनुकूलन योग्य छैन, तर यो सुविधाजनक हुनेछ कि हामीलाई लाग्थ्यो।

म्यापिङ क्वेरी र योजना नोडहरू

अब हामीले पहिलो चरणमा विश्लेषण गरेको योजना र दोस्रो चरणमा विश्लेषण गरेको प्रश्नलाई कसरी संयोजन गर्न सकिन्छ हेरौं।

एउटा साधारण उदाहरण लिऔं - हामीसँग एउटा प्रश्न छ जसले CTE उत्पन्न गर्छ र त्यसबाट दुई पटक पढ्छ। उसले यस्तो योजना बनाउँछ।

CTE

यदि तपाइँ यसलाई ध्यानपूर्वक हेर्नुहुन्छ भने, संस्करण 12 सम्म (वा कुञ्जी शब्दको साथ सुरू गर्दै MATERIALIZED) गठन CTE योजनाकार को लागी एक निरपेक्ष बाधा हो.


यसको मतलब यो हो कि यदि हामीले अनुरोधमा कतै 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तालिका-खण्डबाट ओम: Seq Scan, Bitmap Heap Scan वा Index Scan। तर, कुनै पनि अवस्थामा, यी "बच्चाहरू" जटिल प्रश्नहरू हुनेछैनन् - यसरी यी नोडहरूबाट छुट्याउन सकिन्छ। Append मा UNION.


हामी त्यस्ता गाँठहरू पनि बुझ्छौं, तिनीहरूलाई "एउटै थुप्रोमा" सङ्कलन गर्छौं र भन्छौं: "तपाईंले megatable बाट पढ्नुभएको सबै कुरा यहाँ र रूखको तल छ".

"सरल" डाटा प्राप्त गर्ने नोडहरू

Values Scan योजनामा ​​मेल खान्छ VALUES अनुरोध मा।

Result बिना अनुरोध छ FROM क्रमबद्ध गर्नुहोस् SELECT 1। वा जब तपाईंसँग जानाजानी गलत अभिव्यक्ति छ WHERE-block (त्यसपछि विशेषता देखिन्छ 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, जसको ठीक दुई बच्चाहरू छन् - बायाँ र दायाँ। यो, तदनुसार, तपाईको JOIN को "माथि" के छ र अनुरोधमा "तल" लेखिएको छ।

र योजना को दृष्टिकोण देखि, यी केहि को दुई सन्तान हो * Loop/* Join-नोड। Nested Loop, Hash Anti Join,... - त्यस्तै केही कुरा।

सरल तर्क प्रयोग गरौं: यदि हामीसँग योजनामा ​​एक अर्कामा "सामेल" हुने तालिकाहरू A र B छन् भने, अनुरोधमा तिनीहरू या त अवस्थित हुन सक्छन्। A-JOIN-B, वा B-JOIN-A। यसरी संयोजन गर्ने प्रयास गरौं, अर्को तरिकाले संयोजन गर्ने प्रयास गरौं, र त्यस्ता जोडीहरू नहटेसम्म।

हाम्रो सिन्ट्याक्स रूख लिनुहोस्, हाम्रो योजना लिनुहोस्, तिनीहरूलाई हेर्नुहोस् ... समान छैन!


यसलाई ग्राफको रूपमा पुन: कोरौं - ओह, यो पहिले नै केहि जस्तो देखिन्छ!


ध्यान दिनुहोस् कि हामीसँग नोडहरू छन् जुन एकै साथ बच्चाहरू B र C छन् - हामी कुन क्रममा वास्ता गर्दैनौं। तिनीहरूलाई संयोजन गरौं र नोडको चित्रलाई घुमाउनुहोस्।


फेरि हेरौं। अब हामीसँग बच्चाहरू A र जोडीहरू (B + C) सँग नोडहरू छन् - तिनीहरूसँग पनि उपयुक्त।


महान! हामी यी दुई हौं भन्ने थाहा भयो JOIN अनुरोधबाट योजना नोडहरू सफलतापूर्वक जोडिएका थिए।

अफसोस, यो समस्या सधैं समाधान हुँदैन।


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

तर, धेरै जसो केसहरूमा, लगभग सबै नोडहरू "अनटाइड" हुन सक्छन् र तपाईले यस प्रकारको प्रोफाइलिङ समयमै बाँयामा प्राप्त गर्न सक्नुहुन्छ - शाब्दिक रूपमा, गुगल क्रोममा जब तपाइँ जाभास्क्रिप्ट कोड विश्लेषण गर्नुहुन्छ। तपाईंले प्रत्येक लाइन र प्रत्येक कथनलाई "कार्यान्वयन" गर्न कति लामो समय लागेको देख्न सक्नुहुन्छ।


र तपाइँलाई यो सबै प्रयोग गर्न को लागी यो अधिक सुविधाजनक बनाउन को लागी, हामीले भण्डारण बनाएका छौं अभिलेख, जहाँ तपाईं सुरक्षित गर्न सक्नुहुन्छ र पछि सम्बन्धित अनुरोधहरू सहित आफ्नो योजनाहरू फेला पार्न सक्नुहुन्छ वा कसैसँग लिङ्क साझा गर्न सक्नुहुन्छ।

यदि तपाइँ भर्खरै पढ्न नसकिने प्रश्नलाई पर्याप्त फारममा ल्याउन आवश्यक छ भने, प्रयोग गर्नुहोस् हाम्रो "सामान्यकर्ता".

स्रोत: www.habr.com