परिचय

लेखों की यह श्रृंखला काफी हद तक PostgreSQL में अनुक्रमित से संबंधित है।

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

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

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

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

इंडेक्स

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

वर्तमान में, PostgreSQL 9.6 में छह अलग-अलग प्रकार के सूचकांक बनाए जाते हैं, और एक और सूचकांक एक एक्सटेंशन के रूप में उपलब्ध है - संस्करण 9.6 में महत्वपूर्ण परिवर्तन के लिए धन्यवाद। इसलिए निकट भविष्य में नए प्रकार के सूचकांक की उम्मीद करें।

सभी प्रकार के अनुक्रमित (जिसे एक्सेस विधि भी कहा जाता है) के बीच सभी अंतरों के बावजूद, उनमें से प्रत्येक अंततः तालिका पंक्तियों के साथ एक कुंजी (उदाहरण के लिए अनुक्रमित स्तंभ का मूल्य) को जोड़ता है जिसमें यह कुंजी होती है। प्रत्येक पंक्ति को TID (tuple id) द्वारा पहचाना जाता है, जिसमें फ़ाइल में ब्लॉक की संख्या और ब्लॉक के अंदर पंक्ति की स्थिति शामिल होती है। ज्ञात कुंजी या इसके बारे में कुछ जानकारी के साथ, हम उन पंक्तियों को जल्दी से पढ़ सकते हैं जिनमें संपूर्ण तालिका को स्कैन किए बिना हमारी रुचि की जानकारी हो सकती है।

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

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

तालिका पंक्तियों के संबंधित संस्करणों से डेटा पढ़ें।
पंक्ति के संस्करणों को TID द्वारा TID या किसी पूर्वनिर्मित बिटमैप का उपयोग करके बैच में लाएं।
मौजूदा लेन-देन के लिए पंक्ति संस्करणों की दृश्यता की जांच करें ताकि इसके अलगाव स्तर को ध्यान में रखा जा सके।

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

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

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

शेष सभी पहुंच विधि का कार्य है:

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

हम पहले सामान्य अनुक्रमण इंजन की क्षमताओं पर विचार करेंगे और फिर विभिन्न पहुंच विधियों पर विचार करेंगे।

अनुक्रमण इंजन

अनुक्रमण इंजन PostgreSQL को विभिन्न एक्सेस विधियों के साथ समान रूप से काम करने में सक्षम बनाता है, लेकिन उनकी विशेषताओं को ध्यान में रखता है।

मुख्य स्कैनिंग तकनीक

सूचकांक स्कैन

हम एक सूचकांक द्वारा प्रदान की गई TID के साथ अलग तरीके से काम कर सकते हैं। आइए एक उदाहरण पर विचार करें:

postgres=# create table t(a integer, b text, c boolean); postgres=# insert into t(a,b,c) select s.id, chr((32+random()*94)::integer), random() < 0.01 from generate_series(1,100000) as s(id) order by random(); postgres=# create index on t(a); postgres=# analyze t;

हमने तीन-फ़ील्ड तालिका बनाई। पहले फ़ील्ड में 1 से 100.000 तक संख्याएँ होती हैं, और इस क्षेत्र पर एक इंडेक्स (कोई फर्क नहीं पड़ता कि किस प्रकार) बनाया गया है। दूसरे क्षेत्र में गैर-मुद्रण योग्य को छोड़कर विभिन्न ASCII वर्ण हैं। अंत में, तीसरे क्षेत्र में एक तार्किक मान होता है जो लगभग 1% पंक्तियों के लिए सही होता है और बाकी के लिए गलत होता है। पंक्तियों को एक यादृच्छिक क्रम में तालिका में डाला जाता है।

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

 postgres=# explain (costs off) select * from t where a = 1;

  QUERY PLAN ------------------------------- Index Scan using t_a_idx on t Index Cond: (a = 1) (2 rows)

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

बिटमैप स्कैन

जब हम केवल कुछ मूल्यों से निपटते हैं तो इंडेक्स स्कैन ठीक काम करता है। हालाँकि, जैसे ही पुनर्प्राप्त पंक्तियों की संख्या बढ़ती है, यह एक ही तालिका पृष्ठ पर कई बार वापस आने की संभावना है। इसलिए, ऑप्टिमाइज़र बिटमैप स्कैन पर स्विच करता है।

 postgres=# explain (costs off) select * from t where a <= 100;

  QUERY PLAN ------------------------------------ Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) (4 rows)

पहुंच विधि पहले सभी TID को लौटाती है जो स्थिति से मेल खाती है (बिटमैप इंडेक्स स्कैन नोड), और पंक्ति संस्करणों का बिटमैप इन TID से बनाया गया है। पंक्ति संस्करण तब तालिका (बिटमैप हीप स्कैन) से पढ़े जाते हैं, प्रत्येक पृष्ठ केवल एक बार पढ़ा जा रहा है।

ध्यान दें कि दूसरे चरण में, स्थिति को फिर से जांचा जा सकता है (Recheck Cond)। पुनर्प्राप्त पंक्तियों की संख्या पंक्ति संस्करणों के बिटमैप के लिए पूरी तरह से रैम ("वर्क_म" पैरामीटर द्वारा सीमित) के लिए बड़ी हो सकती है। इस स्थिति में, बिटमैप केवल उन पृष्ठों के लिए बनाया जाता है जिनमें कम से कम एक मिलान पंक्ति संस्करण होता है। इस "हानिपूर्ण" बिटमैप को कम स्थान की आवश्यकता होती है, लेकिन एक पृष्ठ को पढ़ते समय, हमें वहां मौजूद प्रत्येक पंक्ति के लिए शर्तों को फिर से जाँचना होगा। ध्यान दें कि पुनर्प्राप्त पंक्तियों की एक छोटी संख्या के लिए और इसलिए "सटीक" बिटमैप (जैसे हमारे उदाहरण में), "रीचेक कॉन्ड" चरण को वैसे भी योजना में दर्शाया गया है, हालांकि वास्तव में प्रदर्शन नहीं किया गया है।

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

 postgres=# create index on t(b); postgres=# analyze t; postgres=# explain (costs off) select * from t where a <= 100 and b = 'a';

  QUERY PLAN -------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: ((a <= 100) AND (b = 'a'::text)) -> BitmapAnd -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) -> Bitmap Index Scan on t_b_idx Index Cond: (b = 'a'::text) (7 rows)

यहां BitmapAnd नोड बिटवाइज और "ऑपरेशन" द्वारा दो बिटमैप से जुड़ता है।

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

 postgres=# select attname, correlation from pg_stats where tablename = 't';

  attname | correlation ---------+------------- b | 0.533512 c | 0.942365 a | -0.00768816 (3 rows)

एक के करीब पूर्ण मान एक उच्च सहसंबंध (स्तंभ "सी" के रूप में) को इंगित करता है, जबकि शून्य के करीब मान, इसके विपरीत, एक अराजक वितरण (स्तंभ "ए") का संकेत देता है।

अनुक्रमिक स्कैन

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

 postgres=# explain (costs off) select * from t where a <= 40000;

  QUERY PLAN ------------------------ Seq Scan on t Filter: (a <= 40000) (2 rows)

बात यह है कि इंडेक्स बेहतर रूप से उच्चतर हालत चयनात्मकता का काम करते हैं, अर्थात, कम पंक्तियाँ इसका मिलान करती हैं। प्राप्त पंक्तियों की संख्या बढ़ने से सूचकांक पृष्ठों को पढ़ने की लागत बढ़ जाती है।

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

अनुक्रमणिका को कवर करना

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

 postgres=# vacuum t; postgres=# explain (costs off) select a from t where a < 100;

  QUERY PLAN ------------------------------------ Index Only Scan using t_a_idx on t Index Cond: (a < 100) (2 rows)

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

हालाँकि, यदि इंडेक्सिंग इंजन को हर बार दृश्यता के लिए तालिका में देखने की आवश्यकता होती है, तो यह स्कैनिंग विधि किसी नियमित इंडेक्स स्कैन से अलग नहीं होगी।

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

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

हम EXALAIN ANALYZE कमांड का उपयोग करके किसी तालिका में जबरन पहुंच की संख्या जान सकते हैं:

 postgres=# explain (analyze, costs off) select a from t where a < 100;

  QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using t_a_idx on t (actual time=0.025..0.036 rows=99 loops=1) Index Cond: (a < 100) Heap Fetches: 0 Planning time: 0.092 ms Execution time: 0.059 ms (5 rows)

इस मामले में, तालिका (हीप बुत: 0) तक पहुंचने की आवश्यकता नहीं थी, क्योंकि वैक्यूमिंग अभी किया गया है। सामान्य तौर पर, यह संख्या बेहतर शून्य के करीब है।

सभी अनुक्रमित पंक्ति पहचानकर्ताओं के साथ अनुक्रमित मूल्यों को संग्रहीत नहीं करते हैं। यदि एक्सेस विधि डेटा वापस नहीं कर सकती है, तो इसका उपयोग केवल-इंडेक्स स्कैन के लिए नहीं किया जा सकता है।

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

शून्य

NULLs संबंधपरक डेटाबेस में एक महत्वपूर्ण तरीका निभाता है, जो कि किसी भी अनजान या अज्ञात मूल्य का प्रतिनिधित्व करने के लिए एक सुविधाजनक तरीका है।

लेकिन एक विशेष मूल्य से निपटने के लिए विशेष है। एक नियमित बूलियन बीजगणित टर्नरी बन जाता है; यह स्पष्ट नहीं है कि NULL नियमित मानों से छोटा या बड़ा होना चाहिए (इसके लिए छंटाई के लिए विशेष निर्माण, NULLS FIRST और NULLS LAST की आवश्यकता है); यह स्पष्ट नहीं है कि कुल कार्यों को NULLs पर विचार करना चाहिए या नहीं; योजनाकार के लिए एक विशेष सांख्यिकीय की आवश्यकता है ...

सूचकांक समर्थन के दृष्टिकोण से, यह भी स्पष्ट नहीं है कि हमें इन मूल्यों को अनुक्रमित करने की आवश्यकता है या नहीं। यदि NULLs अनुक्रमित नहीं हैं, तो अनुक्रमणिका अधिक कॉम्पैक्ट हो सकती है। लेकिन अगर NULLs को अनुक्रमित किया जाता है, तो हम " अनुक्रमित-फ़ील्ड IS [NOT] NULL" जैसी स्थितियों के लिए सूचकांक का उपयोग कर पाएंगे और साथ ही एक कवरिंग इंडेक्स के रूप में, जब कोई भी स्थिति तालिका के लिए निर्दिष्ट न हो (क्योंकि इस मामले में, तब से अनुक्रमणिका को सभी तालिका पंक्तियों के डेटा को वापस करना चाहिए, जिसमें NULLs भी शामिल हैं)।

प्रत्येक एक्सेस विधि के लिए, डेवलपर्स NULLs इंडेक्स करने के लिए एक व्यक्तिगत निर्णय लेते हैं या नहीं। लेकिन एक नियम के रूप में, वे अनुक्रमित होते हैं।

कई क्षेत्रों पर सूचकांक

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

 postgres=# create index on t(a,b); postgres=# analyze t;

ऑप्टिमाइज़र सबसे अधिक संभावना इस सूचकांक को बिटमैप में शामिल होने के लिए पसंद करेंगे क्योंकि यहाँ से हमें आसानी से बिना किसी सहायक ऑपरेशन के आवश्यक TID मिलनी चाहिए:

 postgres=# explain (costs off) select * from t where a <= 100 and b = 'a';

  QUERY PLAN ------------------------------------------------ Index Scan using t_a_b_idx on t Index Cond: ((a <= 100) AND (b = 'a'::text)) (2 rows)

एक बहुउद्देशीय सूचकांक का उपयोग कुछ क्षेत्रों के लिए एक शर्त द्वारा डेटा पुनर्प्राप्ति को गति देने के लिए भी किया जा सकता है, पहली बार से शुरू:

 postgres=# explain (costs off) select * from t where a <= 100;

  QUERY PLAN -------------------------------------- Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_b_idx Index Cond: (a <= 100) (4 rows)

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

सभी पहुँच विधियाँ कई स्तंभों पर अनुक्रमणिका बनाने का समर्थन नहीं करती हैं।

भावों पर अनुक्रमण करता है

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

 postgres=# explain (costs off) select * from t where lower(b) = 'a';

  QUERY PLAN ------------------------------------------ Seq Scan on t Filter: (lower((b)::text) = 'a'::text) (2 rows)

इस विशिष्ट क्वेरी को फिर से लिखने में अधिक समय नहीं लगता है ताकि ऑपरेटर के बाईं ओर केवल फ़ील्ड नाम लिखा जाए। लेकिन अगर यह संभव नहीं है, तो भावों (कार्यात्मक अनुक्रमित) पर अनुक्रमित करने में मदद मिलेगी:

 postgres=# create index on t(lower(b)); postgres=# analyze t; postgres=# explain (costs off) select * from t where lower(b) = 'a';

  QUERY PLAN ---------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: (lower((b)::text) = 'a'::text) -> Bitmap Index Scan on t_lower_idx Index Cond: (lower((b)::text) = 'a'::text) (4 rows)

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

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

 postgres=# \dt

  Table "public.t" Column | Type | Modifiers --------+---------+----------- a | integer | b | text | c | boolean | Indexes: "t_a_b_idx" btree (a, b) "t_a_idx" btree (a) "t_b_idx" btree (b) "t_lower_idx" btree (lower(b))

 postgres=# select * from pg_stats where tablename = 't_lower_idx';

यह संभव है, यदि आवश्यक हो, तो हिस्टोग्राम बास्केट की संख्या को नियमित डेटा फ़ील्ड के लिए उसी तरह नियंत्रित करना (ध्यान दें कि स्तंभ का नाम अनुक्रमित अभिव्यक्ति के आधार पर भिन्न हो सकता है):

 postgres=# \d t_lower_idx

  Index "public.t_lower_idx" Column | Type | Definition --------+------+------------ lower | text | lower(b) btree, for table "public.t"

 postgres=# alter index t_lower_idx alter column "lower" set statistics 69;

PostgreSQL 11 ने ALTER INDEX ... SET STATISTICS कमांड में कॉलम नंबर निर्दिष्ट करके अनुक्रमित के लिए सांख्यिकी लक्ष्य को नियंत्रित करने के लिए एक क्लीनर तरीका पेश किया है। पैच को मेरे सहयोगी अलेक्जेंडर कोरोटकोव और एड्रियन नायरत ने विकसित किया था।

आंशिक सूचकांक

कभी-कभी एक आवश्यकता तालिका पंक्तियों के केवल भाग को अनुक्रमित करने के लिए उठती है। यह आमतौर पर एक अत्यधिक गैर-समान वितरण से संबंधित होता है: यह एक अनुक्रमणिका द्वारा एक अनन्त मान की खोज करने के लिए समझ में आता है, लेकिन तालिका के पूर्ण स्कैन द्वारा बार-बार मान खोजना आसान है।

हम निश्चित रूप से "सी" कॉलम पर एक नियमित सूचकांक का निर्माण कर सकते हैं, जो हमारी अपेक्षा के अनुरूप काम करेगा:

 postgres=# create index on t(c); postgres=# analyze t; postgres=# explain (costs off) select * from t where c;

  QUERY PLAN ------------------------------- Index Scan using t_c_idx on t Index Cond: (c = true) Filter: c (3 rows)

 postgres=# explain (costs off) select * from t where not c;

  QUERY PLAN ------------------- Seq Scan on t Filter: (NOT c) (2 rows)

और सूचकांक का आकार 276 पृष्ठ है:

 postgres=# select relpages from pg_class where relname='t_c_idx';

  relpages ---------- 276 (1 row)

लेकिन चूंकि "c" कॉलम में केवल 1% पंक्तियों के लिए सही का मान होता है, इसलिए 99% सूचकांक वास्तव में कभी उपयोग नहीं किया जाता है। इस स्थिति में, हम एक आंशिक सूचकांक बना सकते हैं:

 postgres=# create index on t(c) where c; postgres=# analyze t;

सूचकांक का आकार 5 पृष्ठों तक घटाया गया है:

 postgres=# select relpages from pg_class where relname='t_c_idx1';

  relpages ---------- 5 (1 row)

कभी-कभी आकार और प्रदर्शन में अंतर काफी महत्वपूर्ण हो सकता है।

छंटाई

यदि कोई पहुँच विधि पंक्ति पहचानकर्ताओं को कुछ विशेष क्रम में लौटाती है, तो यह क्वेरी करने के लिए अतिरिक्त विकल्पों के साथ अनुकूलक प्रदान करता है।

हम तालिका को स्कैन कर सकते हैं और फिर डेटा को सॉर्ट कर सकते हैं:

 postgres=# set enable_indexscan=off; postgres=# explain (costs off) select * from t order by a;

  QUERY PLAN --------------------- Sort Sort Key: a -> Seq Scan on t (3 rows)

लेकिन हम वांछित क्रम में आसानी से सूचकांक का उपयोग कर डेटा पढ़ सकते हैं:

 postgres=# set enable_indexscan=on; postgres=# explain (costs off) select * from t order by a;

  QUERY PLAN ------------------------------- Index Scan using t_a_idx on t (1 row)

सभी एक्सेस विधियों में से केवल "btree" सॉर्ट किए गए डेटा को वापस कर सकता है, इसलिए इस प्रकार के इंडेक्स पर विचार करने तक अधिक विस्तृत चर्चा बंद कर दें।

समवर्ती भवन

आमतौर पर एक इंडेक्स का निर्माण तालिका के लिए एक शेयर लॉक प्राप्त करता है। यह लॉक तालिका से डेटा पढ़ने की अनुमति देता है, लेकिन इंडेक्स का निर्माण करते समय किसी भी बदलाव को मना करता है।

हम यह सुनिश्चित कर सकते हैं यदि, "t" तालिका पर एक सूचकांक के निर्माण के दौरान, हम एक और सत्र में नीचे दिए गए प्रश्न को पूरा करते हैं:

 postgres=# select mode, granted from pg_locks where relation = 't'::regclass;

  mode | granted -----------+--------- ShareLock | t (1 row)

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

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

 postgres=# create index concurrently on t(a);

यह कमांड SHARE UPDATE EXCLUSIVE मोड में टेबल को लॉक करता है, जो रीडिंग और अपडेट दोनों की अनुमति देता है (केवल टेबल स्ट्रक्चर को बदलना मना है, साथ ही इस टेबल पर समवर्ती वैक्यूमिंग, विश्लेषण या अन्य इंडेक्स का निर्माण करना)।

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

दूसरा, सूचकांक के समवर्ती इमारत के साथ, एक गतिरोध हो सकता है या अद्वितीय बाधाओं का उल्लंघन हो सकता है। हालाँकि, इंडेक्स बनाया जाएगा, हालांकि नॉनऑपरेटिंग। इस तरह के सूचकांक को हटा दिया जाना चाहिए और फिर से बनाया जाना चाहिए। नॉनकॉर्पोरेटिंग इंडेक्स को psql \ d कमांड के आउटपुट में INVALID शब्द से चिह्नित किया जाता है, और नीचे दी गई क्वेरी उन की पूरी सूची लौटाती है:

 postgres=# select indexrelid::regclass index_name, indrelid::regclass table_name from pg_index where not indisvalid;

  index_name | table_name ------------+------------ t_a_idx | t (1 row)

पर पढ़ें ।

PostgreSQL में अनुक्रमित - 1