हमने पहले ही PostgreSQL इंडेक्सिंग इंजन और एक्सेस विधियों के इंटरफ़ेस , साथ ही हैश इंडेक्स , एक्सेस विधियों में से एक पर चर्चा की है। अब हम बी-ट्री, सबसे पारंपरिक और व्यापक रूप से उपयोग किए जाने वाले सूचकांक पर विचार करेंगे। यह लेख बड़ा है, इसलिए धैर्य रखें।

BTREE

संरचना

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

हमेशा की तरह, बी-ट्री की इंडेक्स पंक्तियों को पृष्ठों में पैक किया जाता है। पत्ती पृष्ठों में, इन पंक्तियों में अनुक्रमित (चाबियाँ) और तालिका पंक्तियों (TID) के संदर्भ में डेटा होता है। आंतरिक पृष्ठों में, प्रत्येक पंक्ति सूचकांक के एक बाल पृष्ठ को संदर्भित करती है और इस पृष्ठ में न्यूनतम मान समाहित करती है।

बी-पेड़ों में कुछ महत्वपूर्ण लक्षण हैं:

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

नीचे पूर्णांक कुंजियों के साथ एक क्षेत्र पर सूचकांक का एक सरल उदाहरण है।



इंडेक्स का पहला पेज एक मेटापेज है, जो इंडेक्स रूट का संदर्भ देता है। आंतरिक नोड्स रूट के नीचे स्थित होते हैं, और लीफ पेज बॉटलमॉस्ट पंक्ति में होते हैं। डाउन ऐरो लीफ नोड्स से टेबल रो (TID) के संदर्भों का प्रतिनिधित्व करता है।

समानता से खोजें

आइए " अनुक्रमित-क्षेत्र = अभिव्यक्ति " स्थिति में एक पेड़ में एक मूल्य की खोज पर विचार करें। कहो, हम 49 की कुंजी में रुचि रखते हैं।



खोज रूट नोड के साथ शुरू होती है, और हमें यह निर्धारित करने की आवश्यकता है कि कौन से बच्चे को उतरना है। रूट नोड (4, 32, 64) में कुंजियों के बारे में पता होने के कारण, हम इसलिए चाइल्ड नोड्स में मूल्य श्रेणियों का पता लगाते हैं। 32 to 49 <64 के बाद से, हमें दूसरे बच्चे के नोड में उतरना होगा। अगला, एक ही प्रक्रिया पुनरावर्ती रूप से दोहराई जाती है जब तक हम एक पत्ती नोड तक नहीं पहुंचते हैं जहां से आवश्यक TID प्राप्त किया जा सकता है।

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

(एक और जटिलता यह है कि खोज के दौरान अन्य प्रक्रियाएं डेटा को बदल सकती हैं: पेड़ को फिर से बनाया जा सकता है, पृष्ठों को दो में विभाजित किया जा सकता है, आदि सभी एल्गोरिदम इन समवर्ती संचालनों के लिए एक दूसरे के साथ हस्तक्षेप नहीं करने और अतिरिक्त ताले पैदा नहीं करने के लिए इंजीनियर हैं। जहाँ भी संभव हो। लेकिन हम इस पर विस्तार करने से बचेंगे।)

असमानता से खोजें

जब स्थिति " अनुक्रमित-फ़ील्ड ( अभिव्यक्ति " (या " अनुक्रमित-फ़ील्ड " अभिव्यक्ति ") द्वारा खोज की जाती है, तो हम पहली बार सूचकांक में समता शर्त" अनुक्रमित-फ़ील्ड = अभिव्यक्ति "द्वारा एक मान (यदि कोई हो) पाते हैं और फिर चलते हैं अंत करने के लिए उचित दिशा में पत्ती पृष्ठों।

आंकड़ा ≤ 35 के लिए इस प्रक्रिया को दिखाता है:



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

रेंज द्वारा खोजें

जब " अभिव्यक्ति 1 ed अनुक्रमित-क्षेत्र " अभिव्यक्ति 2 "की खोज करते हैं, तो हम स्थिति" अनुक्रमित-क्षेत्र = अभिव्यक्ति 1 "द्वारा एक मूल्य पाते हैं, और फिर पत्ती के पन्नों से गुजरते रहते हैं, जबकि स्थिति" अनुक्रमित-क्षेत्र ≤ अभिव्यक्ति 2 "से मिलती है; या इसके विपरीत: दूसरी अभिव्यक्ति के साथ शुरू करें और एक विपरीत दिशा में चलें जब तक हम पहली अभिव्यक्ति तक नहीं पहुंचते।

आंकड़ा 23 23 n this 64 की स्थिति के लिए इस प्रक्रिया को दिखाता है:

उदाहरण

आइए एक उदाहरण देखें कि क्वेरी योजनाएं कैसी दिखती हैं। हमेशा की तरह, हम डेमो डेटाबेस का उपयोग करते हैं, और इस बार हम विमान तालिका पर विचार करेंगे। इसमें नौ पंक्तियों के रूप में कुछ शामिल हैं, और योजनाकार सूचकांक का उपयोग नहीं करने का चयन करेगा क्योंकि पूरी तालिका एक पृष्ठ फिट होती है। लेकिन यह तालिका हमारे लिए एक दिलचस्प उद्देश्य के लिए दिलचस्प है।

demo=# select * from aircrafts; 

  aircraft_code | model | range ---------------+---------------------+------- 773 | Boeing 777-300 | 11100 763 | Boeing 767-300 | 7900 SU9 | Sukhoi SuperJet-100 | 3000 320 | Airbus A320-200 | 5700 321 | Airbus A321-200 | 5600 319 | Airbus A319-100 | 6700 733 | Boeing 737-300 | 4200 CN1 | Cessna 208 Caravan | 1200 CR2 | Bombardier CRJ-200 | 2700 (9 rows) 

 demo=# create index on aircrafts(range); demo=# set enable_seqscan = off; 

(या स्पष्ट रूप से, "बीट्री (रेंज) का उपयोग करके विमान पर सूचकांक बनाएं", लेकिन यह एक बी-ट्री है जो डिफ़ॉल्ट रूप से बनाया गया है।)

समानता से खोजें:

 demo=# explain(costs off) select * from aircrafts where range = 3000; 

  QUERY PLAN --------------------------------------------------- Index Scan using aircrafts_range_idx on aircrafts Index Cond: (range = 3000) (2 rows) 

असमानता द्वारा खोजें:

 demo=# explain(costs off) select * from aircrafts where range < 3000; 

  QUERY PLAN --------------------------------------------------- Index Scan using aircrafts_range_idx on aircrafts Index Cond: (range < 3000) (2 rows) 

और सीमा से:

 demo=# explain(costs off) select * from aircrafts where range between 3000 and 5000; 

  QUERY PLAN ----------------------------------------------------- Index Scan using aircrafts_range_idx on aircrafts Index Cond: ((range >= 3000) AND (range <= 5000)) (2 rows) 

छंटाई

आइए एक बार फिर इस बात पर जोर दें कि किसी भी तरह के स्कैन (इंडेक्स, इंडेक्स-ओनली या बिटमैप) के साथ, "btree" एक्सेस मेथड ने डेटा ऑर्डर किया है, जिसे हम उपरोक्त आंकड़ों में स्पष्ट रूप से देख सकते हैं।

इसलिए, यदि किसी तालिका में सॉर्ट स्थिति पर एक इंडेक्स है, तो ऑप्टिमाइज़र दोनों विकल्पों पर विचार करेगा: तालिका का इंडेक्स स्कैन, जो आसानी से सॉर्ट किए गए डेटा, और परिणाम के बाद की छँटाई के साथ तालिका का अनुक्रमिक स्कैन करता है।

क्रमबद्ध करें

एक इंडेक्स बनाते समय हम स्पष्ट रूप से सॉर्ट क्रम को निर्दिष्ट कर सकते हैं। उदाहरण के लिए, हम विशेष रूप से इस तरह से उड़ान श्रेणियों द्वारा एक सूचकांक बना सकते हैं:

 demo=# create index on aircrafts(range desc); 

इस मामले में, बाईं ओर के पेड़ में बड़े मूल्य दिखाई देंगे, जबकि छोटे मूल्य दाईं ओर दिखाई देंगे। यदि हम दोनों दिशाओं में अनुक्रमित मूल्यों से चल सकते हैं तो इसकी आवश्यकता क्यों हो सकती है?

उद्देश्य मल्टी-कॉलम इंडेक्स है। आइए एक छोटे से, मध्य, और लंबी दूरी के शिल्प में एक पारंपरिक विभाजन के साथ विमान मॉडल दिखाने के लिए एक दृश्य बनाएं:

 demo=# create view aircrafts_v as select model, case when range < 4000 then 1 when range < 10000 then 2 else 3 end as class from aircrafts; demo=# select * from aircrafts_v; 

  model | class ---------------------+------- Boeing 777-300 | 3 Boeing 767-300 | 2 Sukhoi SuperJet-100 | 1 Airbus A320-200 | 2 Airbus A321-200 | 2 Airbus A319-100 | 2 Boeing 737-300 | 2 Cessna 208 Caravan | 1 Bombardier CRJ-200 | 1 (9 rows) 

और आइए एक इंडेक्स बनाएं (अभिव्यक्ति का उपयोग करके):

 demo=# create index on aircrafts( (case when range < 4000 then 1 when range < 10000 then 2 else 3 end), model); 

अब हम आरोही क्रम में दोनों स्तंभों द्वारा छांटे गए डेटा को प्राप्त करने के लिए इस सूचकांक का उपयोग कर सकते हैं:

 demo=# select class, model from aircrafts_v order by class, model; 

  class | model -------+--------------------- 1 | Bombardier CRJ-200 1 | Cessna 208 Caravan 1 | Sukhoi SuperJet-100 2 | Airbus A319-100 2 | Airbus A320-200 2 | Airbus A321-200 2 | Boeing 737-300 2 | Boeing 767-300 3 | Boeing 777-300 (9 rows) 

 demo=# explain(costs off) select class, model from aircrafts_v order by class, model; 

  QUERY PLAN -------------------------------------------------------- Index Scan using aircrafts_case_model_idx on aircrafts (1 row) 

इसी प्रकार, हम डेटा को अवरोही क्रम में क्रमबद्ध करने के लिए क्वेरी कर सकते हैं:

 demo=# select class, model from aircrafts_v order by class desc, model desc; 

  class | model -------+--------------------- 3 | Boeing 777-300 2 | Boeing 767-300 2 | Boeing 737-300 2 | Airbus A321-200 2 | Airbus A320-200 2 | Airbus A319-100 1 | Sukhoi SuperJet-100 1 | Cessna 208 Caravan 1 | Bombardier CRJ-200 (9 rows) 

 demo=# explain(costs off) select class, model from aircrafts_v order by class desc, model desc; 

  QUERY PLAN ----------------------------------------------------------------- Index Scan BACKWARD using aircrafts_case_model_idx on aircrafts (1 row) 

हालाँकि, हम इस अनुक्रमणिका का उपयोग अवरोही क्रम में एक कॉलम द्वारा और दूसरे कॉलम में आरोही क्रम में डेटा प्राप्त करने के लिए नहीं कर सकते हैं। इसके लिए अलग से छंटाई की आवश्यकता होगी:

 demo=# explain(costs off) select class, model from aircrafts_v order by class ASC, model DESC; 

  QUERY PLAN ------------------------------------------------- Sort Sort Key: (CASE ... END), aircrafts.model DESC -> Seq Scan on aircrafts (3 rows) 

(ध्यान दें कि, अंतिम उपाय के रूप में, प्लानर ने पहले की गई "enable_seqscan = off" सेटिंग की परवाह किए बिना अनुक्रमिक स्कैन को चुना। ऐसा इसलिए है क्योंकि वास्तव में यह सेटिंग टेबल स्कैनिंग के लिए मना नहीं करती है, लेकिन केवल इसकी खगोलिय लागत को निर्धारित करती है - कृपया इस योजना को देखें। "लागत पर"

इस क्वेरी को इंडेक्स का उपयोग करने के लिए, उत्तरार्द्ध को आवश्यक सॉर्ट दिशा के साथ बनाया जाना चाहिए:

 demo=# create index aircrafts_case_asc_model_desc_idx on aircrafts( (case when range < 4000 then 1 when range < 10000 then 2 else 3 end) ASC, model DESC); demo=# explain(costs off) select class, model from aircrafts_v order by class ASC, model DESC; 

  QUERY PLAN ----------------------------------------------------------------- Index Scan using aircrafts_case_asc_model_desc_idx on aircrafts (1 row) 

स्तंभों का क्रम

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

हम उस इंडेक्स का प्रतिनिधित्व कर सकते हैं जिसे हमने रेंज अंतराल और मॉडल पर एक प्रतीकात्मक तरीके से बनाया है:



वास्तव में इस तरह के एक छोटे सूचकांक के लिए एक रूट पेज फिट होगा। आकृति में, यह स्पष्टता के लिए कई पृष्ठों के बीच जानबूझकर वितरित किया जाता है।

इस चार्ट से यह स्पष्ट है कि "वर्ग = 3" (केवल प्रथम क्षेत्र द्वारा खोज) या "वर्ग = 3 और मॉडल = 'बोइंग 777-300' (जैसे दोनों क्षेत्रों द्वारा खोज) की भविष्यवाणी कुशलतापूर्वक काम करेगी।

हालाँकि, विधेय "मॉडल = 'बोइंग 777-300' द्वारा खोजा जाना कम कुशल होगा: जड़ से शुरू करके, हम यह निर्धारित नहीं कर सकते हैं कि किस बच्चे के नोड को उतरना है, इसलिए, हमें उन सभी के लिए उतरना होगा। इसका मतलब यह नहीं है कि इस तरह के सूचकांक का कभी उपयोग नहीं किया जा सकता है - इसकी दक्षता जारी है। उदाहरण के लिए, यदि हमारे पास प्रत्येक श्रेणी में तीन श्रेणी के विमान और एक महान कई मॉडल हैं, तो हमें लगभग एक तिहाई सूचकांक के माध्यम से देखना होगा और यह पूर्ण टेबल स्कैन की तुलना में अधिक कुशल हो सकता है ... या नहीं।

हालाँकि, अगर हम इस तरह एक इंडेक्स बनाते हैं:

 demo=# create index on aircrafts( model, (case when range < 4000 then 1 when range < 10000 then 2 else 3 end)); 

खेतों का क्रम बदलेगा:



इस सूचकांक के साथ, विधेय "मॉडल = 'बोइंग 777-300' की खोज कुशलतापूर्वक काम करेगी, लेकिन विधेय" वर्ग = 3 "द्वारा नहीं खोजा जाएगा।

NULLs

"बीटीआरई" एक्सेस पद्धति NULLs को अनुक्रमित करती है और IS NULL और IS NOT NULL की शर्तों के अनुसार खोज का समर्थन करती है।

आइए उड़ानों की तालिका पर विचार करें, जहां NULL होते हैं:

 demo=# create index on flights(actual_arrival); demo=# explain(costs off) select * from flights where actual_arrival is null; 

  QUERY PLAN ------------------------------------------------------- Bitmap Heap Scan on flights Recheck Cond: (actual_arrival IS NULL) -> Bitmap Index Scan on flights_actual_arrival_idx Index Cond: (actual_arrival IS NULL) (4 rows) 

NULLs एक या दूसरे पत्ते के छोर पर स्थित होते हैं जो इस बात पर निर्भर करता है कि सूचकांक कैसे बनाया गया था (NULLS FIRST या NULLS LAST)। यह महत्वपूर्ण है अगर किसी क्वेरी में छँटाई शामिल है: इंडेक्स का उपयोग तब किया जा सकता है जब सेलेक्ट कमांड अपने ORDER BY क्लॉज़ में NULL के समान क्रम को निर्दिष्ट करता है क्योंकि बिल्ट इंडेक्स (NULLS FIRST या NULLS LAST) के लिए निर्दिष्ट आदेश।

निम्नलिखित उदाहरण में, ये आदेश समान हैं, इसलिए, हम सूचकांक का उपयोग कर सकते हैं:

 demo=# explain(costs off) select * from flights order by actual_arrival NULLS LAST; 

  QUERY PLAN -------------------------------------------------------- Index Scan using flights_actual_arrival_idx on flights (1 row) 

और यहाँ ये आदेश अलग हैं, और ऑप्टिमाइज़र बाद में छँटाई के साथ अनुक्रमिक स्कैन चुनता है:

 demo=# explain(costs off) select * from flights order by actual_arrival NULLS FIRST; 

  QUERY PLAN ---------------------------------------- Sort Sort Key: actual_arrival NULLS FIRST -> Seq Scan on flights (3 rows) 

सूचकांक का उपयोग करने के लिए, इसे शुरुआत में NULL के साथ बनाया जाना चाहिए:

 demo=# create index flights_nulls_first_idx on flights(actual_arrival NULLS FIRST); demo=# explain(costs off) select * from flights order by actual_arrival NULLS FIRST; 

  QUERY PLAN ----------------------------------------------------- Index Scan using flights_nulls_first_idx on flights (1 row) 

इस तरह के मुद्दे निश्चित रूप से NULLs के क्रमबद्ध नहीं होने के कारण होते हैं, अर्थात, NULL और किसी अन्य मूल्य के लिए तुलना का परिणाम अपरिभाषित है:

 demo=# \pset null NULL demo=# select null < 42; 

  ?column? ---------- NULL (1 row) 

यह बी-ट्री की अवधारणा के लिए काउंटर चलाता है और सामान्य पैटर्न में फिट नहीं होता है। NULLs, हालांकि, डेटाबेस में इतनी महत्वपूर्ण भूमिका निभाते हैं कि हमें हमेशा उनके लिए अपवाद बनाना पड़ता है।

चूंकि NULLs को अनुक्रमित किया जा सकता है, इसलिए तालिका पर लगाए गए किसी भी स्थिति के बिना भी सूचकांक का उपयोग करना संभव है (चूंकि सूचकांक में सभी तालिका पंक्तियों की जानकारी सुनिश्चित है)। यदि क्वेरी को डेटा ऑर्डर करने की आवश्यकता होती है और सूचकांक आवश्यक आदेश को सुनिश्चित करता है, तो यह समझ में आता है। इस मामले में, योजनाकार अलग-अलग छंटाई पर बचाने के लिए सूचकांक पहुंच का चयन कर सकता है।

गुण

आइए "btree" एक्सेस पद्धति के गुणों को देखें (प्रश्न पहले ही प्रदान किए जा चुके हैं )।

  amname | name | pg_indexam_has_property --------+---------------+------------------------- btree | can_order | t btree | can_unique | t btree | can_multi_col | t btree | can_exclude | t 

जैसा कि हमने देखा है, बी-ट्री डेटा ऑर्डर कर सकता है और विशिष्टता का समर्थन करता है - और यह हमें इन जैसे गुणों के साथ प्रदान करने का एकमात्र तरीका है। बहुरंगी अनुक्रमणिका की भी अनुमति है, लेकिन अन्य अभिगम विधियाँ (हालाँकि उनमें से सभी नहीं) भी ऐसे अनुक्रमणिकाओं का समर्थन कर सकती हैं। हम अगली बार EXCLUDE बाधा के समर्थन पर चर्चा करेंगे, और बिना कारण के।

  name | pg_index_has_property ---------------+----------------------- clusterable | t index_scan | t bitmap_scan | t backward_scan | t 

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

  name | pg_index_column_has_property --------------------+------------------------------ asc | t desc | f nulls_first | f nulls_last | t orderable | t distance_orderable | f returnable | t search_array | t search_nulls | t 

इस परत के पहले चार गुण बताते हैं कि कैसे एक निश्चित विशिष्ट स्तंभ के मूल्यों का आदेश दिया जाता है। इस उदाहरण में, मानों को आरोही क्रम ("asc") में क्रमबद्ध किया जाता है और NULL को अंतिम ("nulls_last") प्रदान किया जाता है। लेकिन जैसा कि हमने पहले ही देखा है, अन्य संयोजन संभव हैं।

"Search_array" गुण इंडेक्स द्वारा इस तरह अभिव्यक्ति के समर्थन को इंगित करता है:

 demo=# explain(costs off) select * from aircrafts where aircraft_code in ('733','763','773'); 

  QUERY PLAN ----------------------------------------------------------------- Index Scan using aircrafts_pkey on aircrafts Index Cond: (aircraft_code = ANY ('{733,763,773}'::bpchar[])) (2 rows) 

"रिटर्नेबल" संपत्ति इंडेक्स-ओनली स्कैन के समर्थन को इंगित करती है, जो कि इंडेक्स स्टोर इंडेक्स वैल्यू की पंक्तियों के बाद से स्वयं (हैश इंडेक्स के विपरीत, उदाहरण के लिए) उचित है। यहां बी-ट्री पर आधारित इंडेक्स को कवर करने के बारे में कुछ शब्द कहना समझ में आता है।

अतिरिक्त पंक्तियों के साथ अद्वितीय अनुक्रमित

जैसा कि हमने पहले चर्चा की, एक कवरिंग इंडेक्स वह है जो क्वेरी के लिए आवश्यक सभी मानों को संग्रहीत करता है, तालिका तक पहुंच आवश्यक (लगभग) नहीं है। एक विशिष्ट सूचकांक विशेष रूप से कवर किया जा सकता है।

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

हमारी कंपनी अनास्तासिया लुबेनीकोवा लुबेनिकोवाव में "बीटीआरआई" पद्धति में सुधार हुआ, ताकि अतिरिक्त, गैर-अद्वितीय, स्तंभों को एक अद्वितीय सूचकांक में शामिल किया जा सके। हमें उम्मीद है, इस पैच को समुदाय द्वारा PostgreSQL का हिस्सा बनने के लिए अपनाया जाएगा, लेकिन यह संस्करण 10 में जल्दी नहीं होगा। इस बिंदु पर, पैच प्रो मानक 9.5+ में उपलब्ध है, और यही दिखता है की तरह।

वास्तव में यह पैच PostgreSQL 11 के लिए प्रतिबद्ध था।

आइए बुकिंग टेबल पर विचार करें:

 demo=# \d bookings 

  Table "bookings.bookings" Column | Type | Modifiers --------------+--------------------------+----------- book_ref | character(6) | not null book_date | timestamp with time zone | not null total_amount | numeric(10,2) | not null Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) 

इस तालिका में, प्राथमिक कुंजी (book_ref, बुकिंग कोड) एक नियमित "btree" सूचकांक द्वारा प्रदान की जाती है। आइए एक अतिरिक्त कॉलम के साथ एक नया अनूठा सूचकांक बनाएँ:

 demo=# create unique index bookings_pkey2 on bookings(book_ref) INCLUDE (book_date); 

अब हम मौजूदा सूचकांक को एक नए (लेन-देन में, सभी परिवर्तनों को एक साथ लागू करने के लिए) से प्रतिस्थापित करते हैं:

 demo=# begin; demo=# alter table bookings drop constraint bookings_pkey cascade; demo=# alter table bookings add primary key using index bookings_pkey2; demo=# alter table tickets add foreign key (book_ref) references bookings (book_ref); demo=# commit; 

यह वही है जो हमें मिलता है:

 demo=# \d bookings 

  Table "bookings.bookings" Column | Type | Modifiers --------------+--------------------------+----------- book_ref | character(6) | not null book_date | timestamp with time zone | not null total_amount | numeric(10,2) | not null Indexes: "bookings_pkey2" PRIMARY KEY, btree (book_ref) INCLUDE (book_date) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) 

अब एक और एक ही सूचकांक अद्वितीय के रूप में काम करता है और इस क्वेरी के लिए एक कवरिंग इंडेक्स के रूप में कार्य करता है, उदाहरण के लिए:

 demo=# explain(costs off) select book_ref, book_date from bookings where book_ref = '059FC4'; 

  QUERY PLAN -------------------------------------------------- Index Only Scan using bookings_pkey2 on bookings Index Cond: (book_ref = '059FC4'::bpchar) (2 rows) 

सूचकांक का निर्माण

यह सर्वविदित है, फिर भी कोई कम महत्वपूर्ण नहीं है, कि बड़े आकार की तालिका के लिए, बिना अनुक्रमित के वहां डेटा लोड करना और बाद में आवश्यक अनुक्रमित बनाना बेहतर है। यह न केवल तेज है, बल्कि सबसे अधिक संभावना है कि सूचकांक का आकार छोटा होगा।

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

इस प्रक्रिया की गति उपलब्ध रैम के आकार पर निर्भर करती है, जो "मेंटेनेंस_वर्क_म" पैरामीटर द्वारा सीमित है। तो, पैरामीटर मान बढ़ाने से प्रक्रिया में तेजी आ सकती है। अद्वितीय अनुक्रमितों के लिए, आकार की मेमोरी "work_mem" को "मेन्टेनेंस_वर्क_म" के अलावा आवंटित किया जाता है।

तुलना शब्दार्थ

पिछली बार हमने उल्लेख किया है कि PostgreSQL को यह जानने की जरूरत है कि किस हैश को विभिन्न प्रकार के मूल्यों के लिए कॉल करना है और यह एसोसिएशन "हैश" एक्सेस पद्धति में संग्रहीत है। इसी तरह, सिस्टम को यह पता लगाना होगा कि मूल्यों को कैसे ऑर्डर करना है। इसके लिए सॉर्टिंग, ग्रुपिंग (कभी-कभी), मर्ज जॉइन वगैरह की जरूरत होती है। PostgreSQL ऑपरेटर नामों (जैसे कि,>, <, =) के लिए स्वयं को बाध्य नहीं करता है क्योंकि उपयोगकर्ता अपने स्वयं के डेटा प्रकार को परिभाषित कर सकते हैं और संबंधित ऑपरेटरों को अलग-अलग नाम दे सकते हैं। "Btree" एक्सेस विधि द्वारा उपयोग किया जाने वाला एक ऑपरेटर परिवार इसके बजाय ऑपरेटर नामों को परिभाषित करता है।

उदाहरण के लिए, इन तुलना संचालकों का उपयोग "bool_ops" ऑपरेटर परिवार में किया जाता है:

 postgres=# select amop.amopopr::regoperator as opfamily_operator, amop.amopstrategy from pg_am am, pg_opfamily opf, pg_amop amop where opf.opfmethod = am.oid and amop.amopfamily = opf.oid and am.amname = 'btree' and opf.opfname = 'bool_ops' order by amopstrategy; 

  opfamily_operator | amopstrategy ---------------------+-------------- <(boolean,boolean) | 1 <=(boolean,boolean) | 2 =(boolean,boolean) | 3 >=(boolean,boolean) | 4 >(boolean,boolean) | 5 (5 rows) 

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

• 1 - कम
• 2 - कम या बराबर
• 3 - बराबर
• 4 - अधिक या बराबर
• 5 - अधिक से अधिक

कुछ ऑपरेटर परिवारों में एक रणनीति को लागू करने वाले कई ऑपरेटर हो सकते हैं। उदाहरण के लिए, "integer_ops" ऑपरेटर परिवार में रणनीति 1 के लिए निम्नलिखित ऑपरेटर शामिल हैं:

 postgres=# select amop.amopopr::regoperator as opfamily_operator from pg_am am, pg_opfamily opf, pg_amop amop where opf.opfmethod = am.oid and amop.amopfamily = opf.oid and am.amname = 'btree' and opf.opfname = 'integer_ops' and amop.amopstrategy = 1 order by opfamily_operator; 

  opfamily_operator ---------------------- <(integer,bigint) <(smallint,smallint) <(integer,integer) <(bigint,bigint) <(bigint,integer) <(smallint,integer) <(integer,smallint) <(smallint,bigint) <(bigint,smallint) (9 rows) 

इसके लिए धन्यवाद, एक ऑपरेटर परिवार में निहित विभिन्न प्रकारों के मूल्यों की तुलना करते हुए, आशावादी प्रकार के कलाकारों से बच सकता है।

नए डेटा प्रकार के लिए अनुक्रमणिका समर्थन

दस्तावेज़ीकरण जटिल संख्याओं के लिए एक नए डेटा प्रकार के निर्माण का एक उदाहरण प्रदान करता है और एक ऑपरेटर वर्ग को इस प्रकार के मूल्यों को क्रमबद्ध करने के लिए। यह उदाहरण सी भाषा का उपयोग करता है, जो गति के महत्वपूर्ण होने पर बिल्कुल उचित है। लेकिन केवल तुलनात्मक शब्दार्थों को समझने और बेहतर तरीके से समझने के लिए एक ही प्रयोग के लिए शुद्ध एसक्यूएल का उपयोग करने से हमें कुछ भी बाधा नहीं है।

चलो दो क्षेत्रों के साथ एक नया मिश्रित प्रकार बनाते हैं: वास्तविक और काल्पनिक भाग।

 postgres=# create type complex as (re float, im float); 

हम नए प्रकार के क्षेत्र के साथ एक तालिका बना सकते हैं और तालिका में कुछ मान जोड़ सकते हैं:

 postgres=# create table numbers(x complex); postgres=# insert into numbers values ((0.0, 10.0)), ((1.0, 3.0)), ((1.0, 1.0)); 

अब एक प्रश्न उठता है: यदि गणितीय अर्थों में उनके लिए कोई आदेश संबंध परिभाषित नहीं किया गया है तो जटिल संख्याओं का क्रम कैसे करें?

जैसा कि यह पता चला है, तुलना ऑपरेटर हमारे लिए पहले से ही परिभाषित हैं:

 postgres=# select * from numbers order by x; 

  x -------- (0,10) (1,1) (1,3) (3 rows) 

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

 postgres=# create function modulus(a complex) returns float as $$ select sqrt(a.re*a.re + a.im*a.im); $$ immutable language sql; 

अब हम इस सहायक फ़ंक्शन का उपयोग करते हुए सभी पाँच तुलनात्मक ऑपरेटरों के लिए व्यवस्थित रूप से परिभाषित करेंगे:

 postgres=# create function complex_lt(a complex, b complex) returns boolean as $$ select modulus(a) < modulus(b); $$ immutable language sql; postgres=# create function complex_le(a complex, b complex) returns boolean as $$ select modulus(a) <= modulus(b); $$ immutable language sql; postgres=# create function complex_eq(a complex, b complex) returns boolean as $$ select modulus(a) = modulus(b); $$ immutable language sql; postgres=# create function complex_ge(a complex, b complex) returns boolean as $$ select modulus(a) >= modulus(b); $$ immutable language sql; postgres=# create function complex_gt(a complex, b complex) returns boolean as $$ select modulus(a) > modulus(b); $$ immutable language sql; 

और हम संबंधित ऑपरेटर बनाएंगे। यह समझाने के लिए कि उन्हें ">", "<", और इतने पर बुलाया जाने की आवश्यकता नहीं है, चलो उन्हें "अजीब" नाम दें।

 postgres=# create operator #<#(leftarg=complex, rightarg=complex, procedure=complex_lt); postgres=# create operator #<=#(leftarg=complex, rightarg=complex, procedure=complex_le); postgres=# create operator #=#(leftarg=complex, rightarg=complex, procedure=complex_eq); postgres=# create operator #>=#(leftarg=complex, rightarg=complex, procedure=complex_ge); postgres=# create operator #>#(leftarg=complex, rightarg=complex, procedure=complex_gt); 

इस बिंदु पर, हम संख्याओं की तुलना कर सकते हैं:

 postgres=# select (1.0,1.0)::complex #<# (1.0,3.0)::complex; 

  ?column? ---------- t (1 row) 

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

 postgres=# create function complex_cmp(a complex, b complex) returns integer as $$ select case when modulus(a) < modulus(b) then -1 when modulus(a) > modulus(b) then 1 else 0 end; $$ language sql; 

अब हम एक ऑपरेटर वर्ग बनाने के लिए तैयार हैं (और उसी नाम के ऑपरेटर परिवार को स्वचालित रूप से बनाया जाएगा):

 postgres=# create operator class complex_ops default for type complex using btree as operator 1 #<#, operator 2 #<=#, operator 3 #=#, operator 4 #>=#, operator 5 #>#, function 1 complex_cmp(complex,complex); 

अब छंटनी काम वांछित के रूप में:

 postgres=# select * from numbers order by x; 

  x -------- (1,1) (1,3) (0,10) (3 rows) 

और यह निश्चित रूप से "बीटीआरई" सूचकांक द्वारा समर्थित होगा।

चित्र को पूरा करने के लिए, आप इस क्वेरी का उपयोग करके समर्थन कार्य प्राप्त कर सकते हैं:

 postgres=# select amp.amprocnum, amp.amproc, amp.amproclefttype::regtype, amp.amprocrighttype::regtype from pg_opfamily opf, pg_am am, pg_amproc amp where opf.opfname = 'complex_ops' and opf.opfmethod = am.oid and am.amname = 'btree' and amp.amprocfamily = opf.oid; 

  amprocnum | amproc | amproclefttype | amprocrighttype -----------+-------------+----------------+----------------- 1 | complex_cmp | complex | complex (1 row) 

internals

हम "पेजिंस्पेक्ट" एक्सटेंशन का उपयोग करके बी-ट्री की आंतरिक संरचना का पता लगा सकते हैं।

 demo=# create extension pageinspect; 

सूचकांक रूपक:

 demo=# select * from bt_metap('ticket_flights_pkey'); 

  magic | version | root | level | fastroot | fastlevel --------+---------+------+-------+----------+----------- 340322 | 2 | 164 | 2 | 164 | 2 (1 row) 

यहां सबसे दिलचस्प है सूचकांक स्तर: एक तालिका के लिए दो स्तंभों पर सूचकांक जिसमें एक लाख पंक्तियाँ होती हैं जिनकी आवश्यकता 2 स्तरों के समान होती है (जड़ सहित नहीं)।

ब्लॉक 164 (रूट) पर सांख्यिकीय जानकारी:

 demo=# select type, live_items, dead_items, avg_item_size, page_size, free_size from bt_page_stats('ticket_flights_pkey',164); 

  type | live_items | dead_items | avg_item_size | page_size | free_size ------+------------+------------+---------------+-----------+----------- r | 33 | 0 | 31 | 8192 | 6984 (1 row) 

और ब्लॉक में डेटा ("डेटा" फ़ील्ड, जिसे यहां स्क्रीन की चौड़ाई तक बलिदान किया गया है, में बाइनरी प्रतिनिधित्व में अनुक्रमण कुंजी का मूल्य शामिल है):

 demo=# select itemoffset, ctid, itemlen, left(data,56) as data from bt_page_items('ticket_flights_pkey',164) limit 5; 

  itemoffset | ctid | itemlen | data ------------+---------+---------+---------------------------------------------------------- 1 | (3,1) | 8 | 2 | (163,1) | 32 | 1d 30 30 30 35 34 33 32 33 30 35 37 37 31 00 00 ff 5f 00 3 | (323,1) | 32 | 1d 30 30 30 35 34 33 32 34 32 33 36 36 32 00 00 4f 78 00 4 | (482,1) | 32 | 1d 30 30 30 35 34 33 32 35 33 30 38 39 33 00 00 4d 1e 00 5 | (641,1) | 32 | 1d 30 30 30 35 34 33 32 36 35 35 37 38 35 00 00 2b 09 00 (5 rows) 

पहला तत्व तकनीकों से संबंधित है और ब्लॉक में सभी तत्वों की ऊपरी सीमा को निर्दिष्ट करता है (एक कार्यान्वयन विवरण जिस पर हमने चर्चा नहीं की), जबकि डेटा खुद दूसरे तत्व से शुरू होता है। यह स्पष्ट है कि बाईं ओर का बच्चा नोड ब्लॉक 163 है, इसके बाद ब्लॉक 323, और इसी तरह। बदले में, उन्हें समान कार्यों का उपयोग करके पता लगाया जा सकता है।

अब, एक अच्छी परंपरा का पालन करते हुए, यह प्रलेखन, README और स्रोत कोड को पढ़ने के लिए समझ में आता है।

फिर भी एक और संभावित रूप से उपयोगी विस्तार " amcheck " है, जिसे PostgreSQL 10 में शामिल किया जाएगा, और निचले संस्करणों के लिए आप इसे जीथब से प्राप्त कर सकते हैं। यह विस्तार बी-पेड़ों में डेटा की तार्किक स्थिरता की जांच करता है और हमें अग्रिम में दोषों का पता लगाने में सक्षम बनाता है।

यह सच है, "amcheck" संस्करण 10 से शुरू होने वाले PostgreSQL का एक हिस्सा है।

पर पढ़ें ।