😩 😛 🛢️ छिपाने और अनुकूलक के साथ की तलाश। गेम ओवर, यह CTE PostgreSQL 12 है 👩‍👦‍👦 🕙 👮

यह लेख PostgreSQL 12. में नए के बारे में कहानी का एक सिलसिला है। हमने पहले ही लेख में SQL / JSON (JSONPath पैच) का विश्लेषण किया था "वे फीचर फ्रीज 2019 पर जम गए थे। भाग I JSONPath" , अब CTE की बारी है।

CTE

CTE कॉमन टेबल एक्सप्रेशन है - कॉमन टेबल एक्सप्रेशन, इन्हें कंस्ट्रक्शन के साथ भी कहा जाता है। वास्तव में, यह अस्थायी तालिकाओं का निर्माण है, लेकिन केवल एक क्वेरी के लिए मौजूद है, और सत्र के लिए नहीं। उन्हें इस अनुरोध के अंदर पहुँचा जा सकता है। ऐसा अनुरोध अच्छी तरह से पढ़ा गया है, यह समझ में आता है, यदि आवश्यक हो तो इसे संशोधित करना आसान है। यह एक बहुत लोकप्रिय चीज है, और यह लंबे समय से PostgreSQL में है।

लेकिन सुविधाएं महंगी हो सकती हैं। समस्याओं के अंदर के बाद के रूप में अभिव्यक्ति के भौतिककरण से संबंधित हैं ... एएस () निर्माण। इसे एक आंतरिक अभिव्यक्ति भी कहा जाता है और बाकी की गणना शुरू करने से पहले इसकी गणना की जाती है; इसे शीर्ष-स्तरीय क्वेरी (कोई इनलाइनिंग) में एम्बेड नहीं किया जा सकता है। इस अभिव्यक्ति की योजना बाकी क्वेरी को ध्यान में नहीं रखती है। इस व्यवहार को अनुकूलन, या बाड़ लगाने के लिए एक बाधा कहा जाता है। इसके अलावा, भौतिककरण के लिए खुद को work_mem की आवश्यकता होती है। और अगर नमूना बड़ा है, तो समस्याएं शुरू होती हैं (उदाहरण के लिए, पीजीकोनफ 2019 में इवान फ्रोलकोव की एक रिपोर्ट है )।

ऑप्टिमाइज़र के साथ छुपाएं और खोज करें, जिसका हम नीचे विश्लेषण करेंगे, यह सामान्य रूप से बग नहीं है, लेकिन एक विशेषता है। बेशक, ऐसी स्थितियां हैं जब अभिव्यक्ति के एक हिस्से की प्रारंभिक गणना समाप्त हो जाती है, कहते हैं, पुनरावर्ती प्रश्नों में अनावश्यक दोहराया संचालन। दूसरी ओर, कई डेवलपर्स ने बाधा के बारे में सोचे बिना सीटीई का उपयोग किया, और परिणामस्वरूप, सीटीई के अनुरोधों को उप-क्षेत्रों के साथ समकक्ष (लेकिन अधिक परिष्कृत) अनुरोधों की तुलना में न केवल अधिक धीरे-धीरे निष्पादित किया गया, बल्कि परिमाण के आदेशों द्वारा धीमा कर दिया गया। पेशेवरों और विपक्षों के वजन के बाद, समुदाय ने एक निर्णायक कदम उठाया: इसने डिफ़ॉल्ट व्यवहार को बदल दिया।

हम ऐसी प्लेट पर सीटीई के काम का निरीक्षण करेंगे:

CREATE TABLE xytable AS SELECT x, x AS y FROM generate_series(1,10000000) AS x; CREATE INDEX ON xytable(x,y);

 Table "public.xytable" Column | Type | Collation | Nullable | Default --------------+---------+------------------+----------------+--------- x | integer | | | y | integer | | | Indexes: "xytable_x_y_idx" btree (x, y)

चलो एक साधारण अनुरोध के साथ शुरू करते हैं:

 SELECT * FROM xytable WHERE x=2 AND y>1; QUERY PLAN ----------------------------------------------------------------------------- Index Only Scan using xytable_x_y_idx on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.075 ms Execution Time: 0.035 ms (5 rows)

सब कुछ तुरन्त माना जाता है, केवल सूचकांक का उपयोग किया जाता है।

एक उपशम के साथ एक क्वेरी जो समान गणना करता है, लेकिन एक सिंटैक्स के साथ थोड़ा और अधिक जटिल:

 SELECT * FROM (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2; QUERY PLAN --------------------------------------------------------------------------------- Index Only Scan using xytable_x_y_idx on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.062 ms Execution Time: 0.029 ms (5 rows)

सब कुछ क्रम में है, बहुत तेज सूचकांक गणना।

और अब एक और तार्किक रूप से समकक्ष अनुरोध, लेकिन CTE के साथ:

 WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN ------------------------------------------ CTE Scan on yy (actual time=0.099..3672.842 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 CTE yy -> Seq Scan on cte (actual time=0.097..1355.367 rows=9999999 loops=1) Filter: (y > 1) Rows Removed by Filter: 1 Planning Time: 0.088 ms Execution Time: 3735.986 ms (9 rows)

इस तरह की देरी नग्न आंखों को पहले से ही दिखाई दे रही है। आपने कॉफी नहीं पी है, लेकिन मेल देखने के लिए पर्याप्त समय है (जब हमारे पास 11 वां संस्करण या उससे पहले का समय है)।

और यहाँ क्या हुआ: उपश्रेणियों के मामले में, ऑप्टिमाइज़र ने तुरंत महसूस किया कि शर्तों x = 2 और y> 1 को एक फिल्टर में जोड़ा जा सकता है और सूचकांक द्वारा खोजा जा सकता है। CTE के मामले में, ऑप्टिमाइज़र के पास कोई विकल्प नहीं है: उसे पहले अंदर की स्थिति से निपटना होगा ... AS निर्माण, परिणाम को भौतिक बनाना, और उसके बाद ही काम करना होगा।

और यहाँ बिंदु यह नहीं है कि भौतिकीकरण के लिए संसाधनों की आवश्यकता होगी: यदि शर्त y <3 है, तो लाखों रिकॉर्डों को भौतिक नहीं बनाना होगा, लेकिन केवल 2. एक साधारण क्वेरी के लिए राक्षसी समय अनुक्रमिक खोज पर खर्च किया जाता है, ऑप्टिमाइज़र खोज का उपयोग नहीं कर सकता है क्योंकि ताकि कंपोजिट इंडेक्स x पर बना हो, और उसके बाद ही y पर हो, और वह शर्त x = 2 के साथ क्वेरी के बारे में कुछ भी नहीं जानता जब तक कि वह आंतरिक CTE शर्त को पूरा नहीं करता। यह बाधा से परे है।

तो, PostgreSQL 12 से पहले, डिफ़ॉल्ट भौतिककरण था, अब इसकी अनुपस्थिति है। हम नए संस्करण के आधार पर उसी अनुरोध को लॉन्च करते हैं। बैरियर, जैसा कि यह था, आशावादी तुरंत पूरे अनुरोध को देखता है:

 WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;

 QUERY PLAN ------------------------------------------ Index Only Scan using xytable_x_y_idx1 on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.067 ms Execution Time: 0.029 ms (5 rows)

ऑप्टिमाइज़र ने तुरंत परिस्थितियों को इष्टतम क्रम में संयोजित करना सीखा - जैसा कि उपश्रेणियों के मामले में था।

लेकिन चूक चूक हैं, और अब स्थिति के पूर्ण स्वामित्व के लिए, संस्करण 12 में सीटीई का नियंत्रित, नियंत्रित भौतिककरण है:

 WITH cte_name AS [NOT] MATERIALIZED

आइए अमल करें:

 EXPLAIN ANALYZE WITH yy AS MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;

 QUERY PLAN --------------------------- CTE Scan on yy (cost=356423.68..581401.19 rows=49995 width=8) (actual time=661.038..3603.292 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 CTE yy -> Bitmap Heap Scan on cte (cost=187188.18..356423.68 rows=9999000 width=8) (actual time=661.032..2102.040 rows=9999999 loops=1) Recheck Cond: (y > 1) Heap Blocks: exact=44248 -> Bitmap Index Scan on xytable_x_y_idx1 (cost=0.00..184688.43 rows=9999000 width=0) (actual time=655.519..655.519 rows=9999999 loops=1) Index Cond: (y > 1) Planning Time: 0.086 ms Execution Time: 3612.840 ms (11 rows)

सब कुछ 11 के रूप में है और इसके पहले, आप क्वेरी परिणामों के स्टैंडबाय मोड में मेल देख सकते हैं। हम निषेचन को रोकते हैं, अवरोध को हटाते हैं:

 EXPLAIN ANALYZE WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN --------------------------- Index Only Scan using xytable_x_y_idx1 on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.070..0.072 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.182 ms Execution Time: 0.108 ms (5 rows)

फिर से, कोई राहत नहीं: यह तुरंत मायने रखता है।
की बारीकियां बताई। लेकिन महत्वपूर्ण बारीकियों।

यदि यह एक से अधिक बार एक्सेस किया जाता है तो CTE डिफ़ॉल्ट रूप से भौतिक हो जाता है।

पहली नज़र में, ऐसे मामलों में भौतिककरण एक उचित निर्णय है: एक ही चीज़ की गणना दो बार क्यों करें। व्यवहार में, यह अक्सर वह होता है जो हमने ऊपर देखा था। भौतिककरण से इनकार करने के लिए, यह स्पष्ट रूप से अनुकूलक का आदेश देना आवश्यक है: आवश्यक नहीं।

हम दोहरे अनुरोध के साथ बिना किसी अनुरोध के निष्पादित करते हैं:

 WITH yy AS ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);

 QUERY PLAN --------------------------------------------------------------------------- Result (actual time=3922.274..3922.275 rows=1 loops=1) CTE yy -> Seq Scan on xytable (actual time=0.023..1295.262 rows=9999999 loops=1) Filter: (y > 1) Rows Removed by Filter: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=3109.687..3109.687 rows=1 loops=1) -> CTE Scan on yy (actual time=0.027..3109.682 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 InitPlan 3 (returns $2) -> Aggregate (actual time=812.580..812.580 rows=1 loops=1) -> CTE Scan on yy yy_1 (actual time=0.016..812.575 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 Planning Time: 0.136 ms Execution Time: 3939.848 ms (17 rows)

और अब हम स्पष्ट रूप से भौतिककरण पर प्रतिबंध लिखेंगे:

 WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);

 QUERY PLAN --------------------------------------------------------------------------- Result (actual time=0.035..0.035 rows=1 loops=1) InitPlan 1 (returns $0) -> Aggregate (actual time=0.024..0.024 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable (actual time=0.019..0.020 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=0.006..0.006 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable cte_1 (actual time=0.004..0.005 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.253 ms Execution Time: 0.075 ms (13 rows)

राइटिंग सीटीई को हमेशा निष्पादित किया जाता है, और सीटीई को संदर्भित नहीं किया जाता है।

यह योजना से स्पष्ट है: not_executed इसमें नहीं है। यह पिछले संस्करणों के लिए सच है, लेकिन यह याद रखने योग्य है कि (नहीं) MATERIALIZED निर्माण संस्करण 12 में निष्पादन योग्य अभिव्यक्ति के लिए लागू है।

 EXPLAIN (COSTS OFF) WITH yy AS ( SELECT * FROM xytable WHERE y > 1), not_executed AS ( SELECT * FROM xytable), always_executed AS ( INSERT INTO xytable VALUES(2,2) RETURNING *) SELECT FROM yy WHERE x=2;

 QUERY PLAN ----------------------------- CTE Scan on yy Filter: (x = 2) CTE yy -> Seq Scan on cte Filter: (y > 1) CTE always_executed -> Insert on cte cte_1 -> Result (5 rows)

और एक और नियम:

हमेशा की तरह के साथ पुनरावर्ती प्रश्न।

यह हमेशा होता है, और डिफ़ॉल्ट रूप से नहीं। यदि हम ऑप्टिमाइज़र का आदेश देते हैं: जरूरी नहीं, तो कोई त्रुटि नहीं होगी, और भौतिककरण अभी भी होगा। यह एक समुदाय-सचेत निर्णय है।

हम उदाहरण के होमवर्क पर विचार करेंगे। आज के लिए बस इतना ही।

सीटीई में नए के लिए समर्पित समीक्षा का यह हिस्सा "एट्रोड्स में पोस्टग्रेज 12" रिपोर्ट से उदाहरणों और टुकड़ों का उपयोग करता है, जो ओलेग बार्टुनोव ने सेंट पीटर्सबर्ग में सेंट हाईलोड लोड ++ में इस साल 9 अप्रैल को पढ़ा था।

अगली श्रृंखला में - केएनएन ।

छिपाने और अनुकूलक के साथ की तलाश। गेम ओवर, यह CTE PostgreSQL 12 है

CTE

यदि यह एक से अधिक बार एक्सेस किया जाता है तो CTE डिफ़ॉल्ट रूप से भौतिक हो जाता है।

राइटिंग सीटीई को हमेशा निष्पादित किया जाता है, और सीटीई को संदर्भित नहीं किया जाता है।

हमेशा की तरह के साथ पुनरावर्ती प्रश्न।

More articles: