PostgreSQL ग्लाइडर संकेत

यह ज्ञात है कि एसक्यूएल एक घोषणात्मक भाषा है जो इंगित करती है कि "हम डेटाबेस से क्या चुनना चाहते हैं", और "कैसे" इसे करना है - DBMS खुद के लिए निर्णय लेता है। SQL क्वेरी को उसके निष्पादन (योजना) की एक विशिष्ट विधि के लिए चुनने का कार्य क्वेरी प्लानर द्वारा हल किया जाता है, जो लगभग किसी भी DBMS में होता है। लेकिन कभी-कभी वह सबसे अच्छी योजना का चयन नहीं करता है। कई वाणिज्यिक DBMS इस मामले के लिए "संकेत" प्रदान करते हैं, जो आपको मैन्युअल रूप से डेटाबेस को यह बताने की अनुमति देता है कि अनुरोध को कैसे पूरा किया जाए। ओपन सोर्स में, PostgreSQL में ऐसा कोई तंत्र नहीं था।

और फिर, आखिरकार, कुछ ऐसा हुआ जिसके बारे में कई सपने देखते थे और इंतजार करते-करते थक गए थे, जबकि अन्य डर गए थे। जापानी NTT डेवलपर्स ने PostgreSQL ग्लाइडर संकेत को लागू किया है। इसके अलावा, वे कर्नेल को बदलने के बिना ऐसा करने में कामयाब रहे, एक अलग मॉड्यूल pg_hint_plan के रूप में , PostgreSQL संस्करणों 9.1 और 9.2 का समर्थन करते हुए। मॉड्यूल संकेत को लागू करता है जो आपको स्कैनिंग विधियों को सेट करने और तालिकाओं में शामिल होने की अनुमति देता है, GUC मान सेट करता है। स्थापना और उपयोग के विवरण के लिए, बिल्ली में आपका स्वागत है।


साइट से आप 9.1 और 9.2 के संस्करणों के लिए स्रोत अभिलेखागार को अलग से डाउनलोड कर सकते हैं, हालांकि, बिल्कुल कुछ भी अलग नहीं है और दोनों संस्करणों के लिए समान रूप से एकत्र किया गया है। ओह ठीक है। मॉड्यूल की असेंबली और स्थापना समस्याओं का कारण नहीं बनती है: & & make को स्थापित करें। निर्माण के लिए, आपको अपने पसंदीदा वितरण से एक PostgreSQL देव पैकेज की आवश्यकता है। मॉड्यूल को लेने के लिए PostgreSQL के लिए, आपको किसी SQL को निष्पादित करने की आवश्यकता नहीं है, बस पोस्टग्रैसक्कल.कॉन्फ़ फ़ाइल में (साझा करने के लिए pg_hint_plan चर साझा करने के लिए pg_hint_plan जोड़ें (इसके बजाय, आप आवश्यक लोड का उपयोग करके प्रत्येक सत्र में मॉड्यूल लोड कर सकते हैं)। सर्वर को पुनरारंभ करने के बाद, तीन नए जीयूसी चर उपलब्ध हो जाएंगे: pg_hint_plan.enable_hint, pg_hint_plan.debug_print, pg_hint_plan.parse -messages। उनमें से पहला संकेत की उपलब्धता के लिए जिम्मेदार है (डिफ़ॉल्ट रूप से सक्षम), शेष दो लॉगिंग के लिए।

संकेतों को टिप्पणियों में अनुरोध के लिए संकेत दिया जाता है, / * और * / के साथ जारी किया जाता है। एक टिप्पणी को संकेत के रूप में व्याख्या करने के लिए, इसकी शुरुआत में एक + संकेत होना चाहिए, उदाहरण के लिए / * + SeSScan (t1) * /। संकेत निम्न प्रकार के होते हैं।

टेबल स्कैन विधि के लिए संकेत

• SeScan (तालिका नाम)
• TidScan (तालिका का नाम)
• IndexScan (तालिका का नाम [सूचकांक नाम])
• IndexOnlyScan (तालिका का नाम [सूचकांक नाम])
• बिटमैपस्कैन (तालिका का नाम [सूचकांक नाम])
• NoSeqScan (तालिका का नाम)
• NoTidScan (तालिका का नाम)
• NoIndexScan (तालिका का नाम)
• NoIndexOnlyScan (तालिका का नाम)
• NoBitmapScan (तालिका का नाम)

तालिका के नाम के रूप में, आप क्वेरी में तालिका का नाम और उसका उपनाम दोनों निर्दिष्ट कर सकते हैं।

तालिकाओं में शामिल होने के संकेत

• NestLoop (तालिका नामों की सूची)
• HashJoin (तालिका नामों की सूची)
• MergeJoin (तालिका नामों की सूची)
• NoNestLoop (तालिका नामों की सूची)
• NoHashJoin (तालिका नामों की सूची)
• NoMergeJoin (तालिका के नामों की सूची)

तालिका नामों की सूची एक स्थान के साथ इंगित की गई है। यह ऑर्डर करने के लिए संवेदनशील है, अर्थात, कनेक्शन उसी क्रम में किया जाता है जिस क्रम में तालिकाओं को इंगित किया जाता है।

दो संकेत भी अलग से दिए गए हैं:

• अग्रणी (तालिका नामों की सूची) - शामिल होने की एक विशिष्ट विधि निर्दिष्ट किए बिना तालिकाओं में शामिल होने का क्रम निर्धारित करता है
• सेट (GUC मान) - अनुरोध की अवधि के लिए GUC चर का मान सेट करता है। Vobshchem कुछ भी कोई नई कार्यक्षमता नहीं ले जाता है, GUC और इसलिए इसे स्थापित करना संभव था, बस संकेत आपको इसे अधिक संक्षेप में (और जल्दी से) करने की अनुमति देता है।

यह सभी कार्रवाई में यह प्रयास करने का समय है। आइए टेस्ट टेबल, इंडेक्स, आंकड़े इकट्ठा करें।

CREATE TABLE test1 AS (SELECT id, (random()*1000)::int AS id_2, random() AS value1, random() AS value2 FROM generate_series(1,1000000) id); CREATE TABLE test2 AS (SELECT id, random() AS value FROM generate_series(1,1000) id); CREATE INDEX test1_id_idx ON test1 (id); CREATE INDEX test1_id_2_idx ON test1 (id_2); CREATE INDEX test1_value1_idx ON test1 (value1); CREATE INDEX test1_value2_idx ON test1 (value2); CREATE INDEX test2_id_idx ON test2 (id); CREATE INDEX test2_value_idx ON test2 (value); VACUUM ANALYZE; 

मान लीजिए हमारे पास एक क्वेरी है जो डेटा को दो क्षेत्रों के मान से फ़िल्टर करती है।

 SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61; 

ग्लाइडर बिटमैप स्कैन का उपयोग करके प्रत्येक क्षेत्र के लिए सूचकांक स्कैन के परिणामों को संयोजित करने का निर्णय लेता है।

  QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on test1 (cost=319.82..514.76 rows=52 width=24) (actual time=9.575..9.736 rows=59 loops=1) Recheck Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision) AND (value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision)) -> BitmapAnd (cost=319.82..319.82 rows=52 width=0) (actual time=9.529..9.529 rows=0 loops=1) -> Bitmap Index Scan on test1_value1_idx (cost=0.00..113.54 rows=5318 width=0) (actual time=2.839..2.839 rows=5072 loops=1) Index Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision)) -> Bitmap Index Scan on test1_value2_idx (cost=0.00..206.00 rows=9764 width=0) (actual time=5.385..5.385 rows=10070 loops=1) Index Cond: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision)) Total runtime: 9.805 ms 

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

 /*+ IndexScan(test1) */ SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61; 

  QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Index Scan using test1_value1_idx on test1 (cost=0.00..15198.71 rows=52 width=24) (actual time=0.124..10.704 rows=59 loops=1) Index Cond: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision)) Filter: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision)) Total runtime: 10.776 ms 

और यहां तक ​​कि उसे एक अलग सूचकांक का उपयोग करें।

 /*+ IndexScan(test1 test1_value2_idx) */ SELECT * FROM test1 WHERE value1 BETWEEN 0.5 and 0.505 AND value2 BETWEEN 0.6 and 0.61; 

  QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Index Scan using test1_value2_idx on test1 (cost=0.00..22463.60 rows=52 width=24) (actual time=0.787..15.757 rows=59 loops=1) Index Cond: ((value2 >= 0.6::double precision) AND (value2 <= 0.61::double precision)) Filter: ((value1 >= 0.5::double precision) AND (value1 <= 0.505::double precision)) Total runtime: 15.816 ms (4 rows) 

एक उदाहरण अधिक जटिल है। एक तालिका के क्षेत्र से फ़िल्टरिंग के साथ दो तालिकाओं को मिलाते हुए, दूसरे और LIMIT के क्षेत्र द्वारा छँटाई।

 SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100; 

ग्लाइडर test1_value1_idx और नेस्टेड लूप का उपयोग करके सूचकांक सी योजना का चयन करता है।

  QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=4.33..2149.77 rows=100 width=36) (actual time=0.274..34.784 rows=100 loops=1) -> Nested Loop (cost=4.33..171467.82 rows=7992 width=36) (actual time=0.271..34.753 rows=100 loops=1) Join Filter: (t1.id_2 = t2.id) -> Index Scan using test1_value1_idx on test1 t1 (cost=0.00..51457.05 rows=1000000 width=24) (actual time=0.022..10.338 rows=11873 loops=1) -> Materialize (cost=4.33..10.80 rows=8 width=12) (actual time=0.000..0.001 rows=8 loops=11873) -> Bitmap Heap Scan on test2 t2 (cost=4.33..10.76 rows=8 width=12) (actual time=0.035..0.046 rows=8 loops=1) Recheck Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision)) -> Bitmap Index Scan on test2_value_idx (cost=0.00..4.33 rows=8 width=0) (actual time=0.026..0.026 rows=8 loops=1) Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision)) Total runtime: 34.870 ms 

मान लीजिए कि हम एक अलग प्रकार की टेबल जॉइन का उपयोग करना चाहते हैं: हैशओइन।

 /*+ HashJoin(t1 t2) */ EXPLAIN ANALYZE SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100; 

टेस्ट 2 द्वारा बिटमैप इंडेक्स स्कैन के अंदर और बाहर की सीमा को मिलाकर ग्लाइडर का पालन किया जाता है।

  QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=20516.23..20516.48 rows=100 width=36) (actual time=156.219..156.230 rows=100 loops=1) -> Sort (cost=20516.23..20536.21 rows=7992 width=36) (actual time=156.217..156.225 rows=100 loops=1) Sort Key: t1.value1 Sort Method: top-N heapsort Memory: 32kB -> Hash Join (cost=10.86..20210.78 rows=7992 width=36) (actual time=0.248..154.286 rows=7889 loops=1) Hash Cond: (t1.id_2 = t2.id) -> Seq Scan on test1 t1 (cost=0.00..16370.00 rows=1000000 width=24) (actual time=0.013..63.210 rows=1000000 loops=1) -> Hash (cost=10.76..10.76 rows=8 width=12) (actual time=0.066..0.066 rows=8 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Bitmap Heap Scan on test2 t2 (cost=4.33..10.76 rows=8 width=12) (actual time=0.044..0.057 rows=8 loops=1) Recheck Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision)) -> Bitmap Index Scan on test2_value_idx (cost=0.00..4.33 rows=8 width=0) (actual time=0.034..0.034 rows=8 loops=1) Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision)) Total runtime: 156.335 ms 

यदि, उदाहरण के लिए, आप कनेक्शन प्रकार MergeJoin और IndexScan को index test__value_idx द्वारा सेट करते हैं, तो ग्लाइडर फिर से आवश्यक छंटाई और सीमा जोड़ देगा।

 /*+ MergeJoin(t1 t2) IndexScan (t2 test2_value_idx) */ EXPLAIN ANALYZE SELECT * FROM test1 t1 JOIN test2 t2 ON t1.id_2 = t2.id WHERE t2.value BETWEEN 0.5 AND 0.51 ORDER BY t1.value1 LIMIT 100; 

  QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=54410.09..54410.34 rows=100 width=36) (actual time=446.031..446.041 rows=100 loops=1) -> Sort (cost=54410.09..54430.07 rows=7992 width=36) (actual time=446.029..446.032 rows=100 loops=1) Sort Key: t1.value1 Sort Method: top-N heapsort Memory: 32kB -> Merge Join (cost=71.79..54104.65 rows=7992 width=36) (actual time=12.501..444.501 rows=7889 loops=1) Merge Cond: (t1.id_2 = t2.id) -> Index Scan using test1_id_2_idx on test1 t1 (cost=0.00..51460.24 rows=1000000 width=24) (actual time=0.033..377.392 rows=900401 loops=1) -> Sort (cost=24.52..24.54 rows=8 width=12) (actual time=0.074..0.545 rows=6927 loops=1) Sort Key: t2.id Sort Method: quicksort Memory: 25kB -> Index Scan using test2_value_idx on test2 t2 (cost=0.00..24.40 rows=8 width=12) (actual time=0.026..0.047 rows=8 loops=1) Index Cond: ((value >= 0.5::double precision) AND (value <= 0.51::double precision)) Total runtime: 446.182 ms 

आप देख सकते हैं कि दिए गए सभी उदाहरणों में, केवल संकेत के उपयोग से स्थिति खराब हो गई है। इसके द्वारा, मैं संकेत देना चाहता था कि वास्तविक परियोजनाओं में संकेत का उपयोग करने से पहले दो बार सोचना उचित है। यहां तक ​​कि अगर आपके पास एक योजना है जो इस विशेष मामले में तेज है, तो अपने आप से निम्नलिखित प्रश्न पूछें:

• क्या आपने ग्लाइडर के पैरामीटर * _cost, effective_cache_size, geqo *, आदि को समायोजित किया था। उपलब्ध सर्वर संसाधनों के अनुसार?
• किस डेटा पर आपको एक योजना मिली जो तेजी से चलती है? क्या उत्पादन में समान डेटा वितरण है? क्या आप डेटा वितरण में परिवर्तन के संकेत को फिर से लिखने के लिए तैयार हैं?
• क्या योजना तब तेजी से क्रियान्वित हुई जब आपकी जरूरत की हर चीज कैश में थी? और उत्पादन पर इस अनुरोध के निष्पादन के समय, क्या सब कुछ कैश में भी होगा?

फिर भी, कम से कम दो स्थितियों में संकेत बहुत उपयोगी होते हैं:

• मैं ग्लाइडर / निष्कासन के काम को बेहतर ढंग से समझना चाहूंगा, "यदि होगा तो क्या होगा?" सवालों के जवाब पाएं।
• कभी-कभी ग्लाइडर अभी भी बहुत गलत है। उदाहरण के लिए, जब तालिका के क्षेत्रों के बीच एक मजबूत सहसंबंध होता है, जिसे वह ध्यान में नहीं ले सकता है। इस वजह से, हालत की चयनात्मकता का एक गलत मूल्यांकन प्राप्त होता है, और एक खराब योजना हो सकती है।

PS मॉड्यूल के लिए टिप के लिए धन्यवाद, ओलेग बार्टुनोव (उर्फ ज़ेन )!