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

जिन

"जिन? .. जिन है, ऐसा लगता है, ऐसी अमेरिकी शराब? ..?"
"मैं एक ड्रिंक नहीं हूँ, ओह, जिज्ञासु लड़का!" फिर से वह बूढ़ा आदमी भड़क गया, फिर से उसने खुद को महसूस किया और फिर से खुद को संभाल लिया। "मैं एक पेय नहीं हूं, लेकिन एक शक्तिशाली और अभद्र आत्मा हूं, और दुनिया में ऐसा कोई जादू नहीं है जो मैं नहीं कर पाऊंगा।"
- लज़ार लागिन, "ओल्ड खोटैबच।"

जिन सामान्यीकृत उल्टे सूचकांक के लिए खड़ा है और इसे एक जिन्न के रूप में माना जाना चाहिए, न कि एक पेय के रूप में।
- README

सामान्य अवधारणा

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

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

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

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

तो, GIN इंडेक्स में B- ट्री ऑफ़ एलिमेंट्स होते हैं, और B-ट्री या TID की फ्लैट लिस्ट उस B- ट्री की लीफ पंक्तियों से जुड़ी होती है।

पहले चर्चा किए गए GiST और SP-GiST इंडेक्स की तरह, GIN कंपाउंड डेटा प्रकारों पर विभिन्न कार्यों का समर्थन करने के लिए इंटरफ़ेस के साथ एक एप्लिकेशन डेवलपर प्रदान करता है।

पूर्ण-पाठ खोज

GIN विधि के लिए मुख्य अनुप्रयोग क्षेत्र पूर्ण-पाठ खोज को गति दे रहा है, जो इस सूचकांक की अधिक विस्तृत चर्चा में एक उदाहरण के रूप में उपयोग किए जाने के लिए उचित है।

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

आइए GiST- संबंधित लेख से उदाहरण पर विचार करें:

postgres=# create table ts(doc text, doc_tsv tsvector); postgres=# insert into ts(doc) values ('Can a sheet slitter slit sheets?'), ('How many sheets could a sheet slitter slit?'), ('I slit a sheet, a sheet I slit.'), ('Upon a slitted sheet I sit.'), ('Whoever slit the sheets is a good sheet slitter.'), ('I am a sheet slitter.'), ('I slit sheets.'), ('I am the sleekest sheet slitter that ever slit sheets.'), ('She slits the sheet she sits on.'); postgres=# update ts set doc_tsv = to_tsvector(doc); postgres=# create index on ts using gin(doc_tsv);

इस सूचकांक की एक संभावित संरचना चित्र में दिखाई गई है:

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

 postgres=# select ctid, left(doc,20), doc_tsv from ts;

  ctid | left | doc_tsv -------+----------------------+--------------------------------------------------------- (0,1) | Can a sheet slitter | 'sheet':3,6 'slit':5 'slitter':4 (0,2) | How many sheets coul | 'could':4 'mani':2 'sheet':3,6 'slit':8 'slitter':7 (0,3) | I slit a sheet, a sh | 'sheet':4,6 'slit':2,8 (1,1) | Upon a slitted sheet | 'sheet':4 'sit':6 'slit':3 'upon':1 (1,2) | Whoever slit the she | 'good':7 'sheet':4,8 'slit':2 'slitter':9 'whoever':1 (1,3) | I am a sheet slitter | 'sheet':4 'slitter':5 (2,1) | I slit sheets. | 'sheet':3 'slit':2 (2,2) | I am the sleekest sh | 'ever':8 'sheet':5,10 'sleekest':4 'slit':9 'slitter':6 (2,3) | She slits the sheet | 'sheet':4 'sit':6 'slit':2 (9 rows)

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

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

 postgres=# select (unnest(doc_tsv)).lexeme, count(*) from ts group by 1 order by 2 desc;

  lexeme | count ----------+------- sheet | 9 slit | 8 slitter | 5 sit | 2 upon | 1 mani | 1 whoever | 1 sleekest | 1 good | 1 could | 1 ever | 1 (11 rows)

यह भी ध्यान दें कि एक नियमित बी-ट्री के विपरीत, GIN सूचकांक के पृष्ठ एक द्विदिश की बजाय एक अप्रत्यक्ष सूची से जुड़े होते हैं। यह पर्याप्त है क्योंकि एक पेड़ का निशान केवल एक ही तरह से किया जाता है।

एक क्वेरी का उदाहरण

हमारे उदाहरण के लिए निम्नलिखित क्वेरी कैसे की जाएगी?

 postgres=# explain(costs off) select doc from ts where doc_tsv @@ to_tsquery('many & slitter');

  QUERY PLAN --------------------------------------------------------------------- Bitmap Heap Scan on ts Recheck Cond: (doc_tsv @@ to_tsquery('many & slitter'::text)) -> Bitmap Index Scan on ts_doc_tsv_idx Index Cond: (doc_tsv @@ to_tsquery('many & slitter'::text)) (4 rows)

व्यक्तिगत lexemes (खोज कुंजी) पहले क्वेरी से निकाले जाते हैं: "मणि" और "स्लीटर"। यह एक विशेष एपीआई फ़ंक्शन द्वारा किया जाता है जो ऑपरेटर वर्ग द्वारा निर्धारित डेटा प्रकार और रणनीति को ध्यान में रखता है:

 postgres=# select amop.amopopr::regoperator, amop.amopstrategy from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop where opc.opcname = 'tsvector_ops' and opf.oid = opc.opcfamily and am.oid = opf.opfmethod and amop.amopfamily = opc.opcfamily and am.amname = 'gin' and amop.amoplefttype = opc.opcintype;

  amopopr | amopstrategy -----------------------+-------------- @@(tsvector,tsquery) | 1 matching search query @@@(tsvector,tsquery) | 2 synonym for @@ (for backward compatibility) (2 rows)

लेक्समेस के बी-ट्री में, हम अगले दोनों कुंजी पाते हैं और टीआईडी की तैयार सूची से गुजरते हैं। हमें मिलता है:

"मणि" के लिए - (0,2)।
"स्लीटर" के लिए - (0,1), (0,2), (1,2), (1,3), (2,2)।

अंत में, पाया गया प्रत्येक TID के लिए, एक API संगति फ़ंक्शन को कहा जाता है, जो यह निर्धारित करना चाहिए कि कौन सी पंक्तियाँ खोज क्वेरी से मेल खाती हैं। चूँकि हमारी क्वेरी में लेक्सम बूलियन द्वारा शामिल किए गए हैं "और", लौटी एकमात्र पंक्ति है (0,2):

  | | | consistency | | | function TID | mani | slitter | slit & slitter -------+------+---------+---------------- (0,1) | f | T | f (0,2) | T | T | T (1,2) | f | T | f (1,3) | f | T | f (2,2) | f | T | f

और परिणाम है:

 postgres=# select doc from ts where doc_tsv @@ to_tsquery('many & slitter');

  doc --------------------------------------------- How many sheets could a sheet slitter slit? (1 row)

यदि हम इस दृष्टिकोण की तुलना पहले से ही GiST के लिए चर्चा की गई, पूर्ण-पाठ खोज के लिए GIN का लाभ स्पष्ट दिखाई देता है। लेकिन आंख से मिलने के अलावा इसमें और भी बहुत कुछ है।

एक धीमी अद्यतन की समस्या

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

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

GIN इंडेक्स में "फास्टअपडेट" स्टोरेज पैरामीटर है, जिसे हम इंडेक्स निर्माण के दौरान निर्दिष्ट कर सकते हैं और बाद में अपडेट कर सकते हैं:

 postgres=# create index on ts using gin(doc_tsv) with (fastupdate = true);

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

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

आंशिक मैच की खोज

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

 gin=# select doc from ts where doc_tsv @@ to_tsquery('slit:*');

  doc -------------------------------------------------------- Can a sheet slitter slit sheets? How many sheets could a sheet slitter slit? I slit a sheet, a sheet I slit. Upon a slitted sheet I sit. Whoever slit the sheets is a good sheet slitter. I am a sheet slitter. I slit sheets. I am the sleekest sheet slitter that ever slit sheets. She slits the sheet she sits on. (9 rows)

इस क्वेरी में ऐसे दस्तावेज़ मिलेंगे, जिनमें "स्लिट" से शुरू होने वाले लेक्सेम हैं। इस उदाहरण में, ऐसे लेक्सेम "स्लिट" और "स्लिटर" हैं।

अनुक्रमणिका के बिना भी, निश्चित रूप से एक क्वेरी काम करेगी, लेकिन GIN निम्नलिखित खोज को गति देने की भी अनुमति देता है:

 postgres=# explain (costs off) select doc from ts where doc_tsv @@ to_tsquery('slit:*');

  QUERY PLAN ------------------------------------------------------------- Bitmap Heap Scan on ts Recheck Cond: (doc_tsv @@ to_tsquery('slit:*'::text)) -> Bitmap Index Scan on ts_doc_tsv_idx Index Cond: (doc_tsv @@ to_tsquery('slit:*'::text)) (4 rows)

यहाँ खोज क्वेरी में निर्दिष्ट उपसर्ग वाले सभी lexemes को पेड़ में देखा जाता है और बूलियन "या" द्वारा जोड़ा जाता है।

बार-बार और अनियंत्रित lexemes

यह देखने के लिए कि लाइव डेटा पर इंडेक्सिंग कैसे काम करता है, चलो "pgsql-hackers" ईमेल का संग्रह लेते हैं, जिसका उपयोग हम पहले ही GiST पर चर्चा करते समय कर चुके हैं। संग्रह के इस संस्करण में भेजने की तारीख, विषय, लेखक और पाठ के साथ 356125 संदेश हैं।

 fts=# alter table mail_messages add column tsv tsvector; fts=# update mail_messages set tsv = to_tsvector(body_plain);

 NOTICE: word is too long to be indexed DETAIL: Words longer than 2047 characters are ignored. ... UPDATE 356125

 fts=# create index on mail_messages using gin(tsv);

आइए एक लेक्सेम पर विचार करें जो कई दस्तावेजों में होता है। "अनावश्यक" का उपयोग करने वाली क्वेरी इतने बड़े डेटा आकार पर काम करने में विफल हो जाएगी, और सही तकनीक "ts_stat" फ़ंक्शन का उपयोग करना है, जो लेक्सेम पर जानकारी प्रदान करता है, जहां वे हुए दस्तावेजों की संख्या, और घटनाओं की कुल संख्या।

 fts=# select word, ndoc from ts_stat('select tsv from mail_messages') order by ndoc desc limit 3;

  word | ndoc -------+-------- re | 322141 wrote | 231174 use | 176917 (3 rows)

चलो "लिखा" चुनें।

और हम कुछ शब्द लेंगे जो डेवलपर्स के ईमेल के लिए अनूठे हैं, कहते हैं, "टैटू":

 fts=# select word, ndoc from ts_stat('select tsv from mail_messages') where word = 'tattoo';

  word | ndoc --------+------ tattoo | 2 (1 row)

क्या ऐसे कोई दस्तावेज़ हैं जहाँ ये दोनों लेक्मे आते हैं? ऐसा प्रतीत होता है कि वहाँ हैं:

 fts=# select count(*) from mail_messages where tsv @@ to_tsquery('wrote & tattoo');

  count ------- 1 (1 row)

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

 fts=# \timing on fts=# select count(*) from mail_messages where tsv @@ to_tsquery('wrote & tattoo');

  count ------- 1 (1 row) Time: 0,959 ms

अकेले "लिखे" की खोज में अधिक समय लगता है:

 fts=# select count(*) from mail_messages where tsv @@ to_tsquery('wrote');

  count -------- 231174 (1 row) Time: 2875,543 ms (00:02,876)

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

क्वेरी परिणाम को सीमित करना

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

इसलिए, लिमट क्लॉज का उपयोग करते हुए सूचकांक स्कैन परिणाम की सीमा काफी कुशल नहीं है। ऑपरेशन की अनुमानित लागत पर ध्यान दें ("सीमा" नोड के "लागत" क्षेत्र):

 fts=# explain (costs off) select * from mail_messages where tsv @@ to_tsquery('wrote') limit 1;

  QUERY PLAN ----------------------------------------------------------------------------------------- Limit (cost=1283.61..1285.13 rows=1) -> Bitmap Heap Scan on mail_messages (cost=1283.61..209975.49 rows=137207) Recheck Cond: (tsv @@ to_tsquery('wrote'::text)) -> Bitmap Index Scan on mail_messages_tsv_idx (cost=0.00..1249.30 rows=137207) Index Cond: (tsv @@ to_tsquery('wrote'::text)) (5 rows)

लागत 1285.13 के रूप में अनुमानित है, जो कि पूरे बिटमैप 1249.30 (बिटमैप इंडेक्स स्कैन नोड के "लागत") के निर्माण की लागत से थोड़ा बड़ा है।

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

 fts=# set gin_fuzzy_search_limit = 1000; fts=# select count(*) from mail_messages where tsv @@ to_tsquery('wrote');

  count ------- 5746 (1 row)

 fts=# set gin_fuzzy_search_limit = 10000; fts=# select count(*) from mail_messages where tsv @@ to_tsquery('wrote');

  count ------- 14726 (1 row)

जैसा कि हम देख सकते हैं, क्वेरी द्वारा लौटी पंक्तियों की संख्या अलग-अलग पैरामीटर मानों के लिए भिन्न होती है (यदि इंडेक्स एक्सेस का उपयोग किया जाता है)। सीमा सख्त नहीं है: निर्दिष्ट से अधिक पंक्तियों को वापस किया जा सकता है, जो पैरामीटर नाम के "फजी" भाग को सही ठहराता है।

कॉम्पैक्ट प्रतिनिधित्व

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

आकार का अंदाजा लगाने के लिए, आइए संदेशों के पाठ से बी-वृक्ष का निर्माण करें। लेकिन एक निष्पक्ष तुलना निश्चित रूप से होने वाली नहीं है:

GIN अलग-अलग डेटा प्रकार ("tsvector" पर "टेक्स्ट" के बजाय बनाया गया है), जो छोटा है,
उसी समय, बी-ट्री के संदेशों के आकार को लगभग दो किलोबाइट तक छोटा करना पड़ता है।

फिर भी, हम जारी रखते हैं:

 fts=# create index mail_messages_btree on mail_messages(substring(body_plain for 2048));

हम GiST इंडेक्स भी बनाएंगे:

 fts=# create index mail_messages_gist on mail_messages using gist(tsv);

"वैक्यूम फुल" पर अनुक्रमित का आकार:

 fts=# select pg_size_pretty(pg_relation_size('mail_messages_tsv_idx')) as gin, pg_size_pretty(pg_relation_size('mail_messages_gist')) as gist, pg_size_pretty(pg_relation_size('mail_messages_btree')) as btree;

  gin | gist | btree --------+--------+-------- 179 MB | 125 MB | 546 MB (1 row)

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

GiST या GIN?

कई डेटा प्रकारों के लिए, ऑपरेटर कक्षाएं GiST और GIN दोनों के लिए उपलब्ध हैं, जो एक प्रश्न उठाता है कि किस इंडेक्स का उपयोग करना है। शायद, हम पहले से ही कुछ निष्कर्ष निकाल सकते हैं।

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

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

Arrays

GIN का उपयोग करने का एक अन्य उदाहरण सरणियों का अनुक्रमण है। इस मामले में, सरणी तत्व इंडेक्स में आते हैं, जो सरणियों पर कई कार्यों को गति देने की अनुमति देता है:

 postgres=# select amop.amopopr::regoperator, amop.amopstrategy from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop where opc.opcname = 'array_ops' and opf.oid = opc.opcfamily and am.oid = opf.opfmethod and amop.amopfamily = opc.opcfamily and am.amname = 'gin' and amop.amoplefttype = opc.opcintype;

  amopopr | amopstrategy -----------------------+-------------- &&(anyarray,anyarray) | 1 intersection @>(anyarray,anyarray) | 2 contains array <@(anyarray,anyarray) | 3 contained in array =(anyarray,anyarray) | 4 equality (4 rows)

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

 demo=# select departure_airport_name, arrival_airport_name, days_of_week from routes where flight_no = 'PG0049';

  departure_airport_name | arrival_airport_name | days_of_week ------------------------+----------------------+-------------- Vnukovo | Gelendzhik | {2,4,7} (1 row)

सूचकांक बनाने के लिए, आइए एक तालिका में दृश्य को "भौतिक" करें:

 demo=# create table routes_t as select * from routes; demo=# create index on routes_t using gin(days_of_week);

अब हम सभी उड़ानों को जानने के लिए सूचकांक का उपयोग कर सकते हैं जो मंगलवार, गुरुवार और रविवार को निकलती हैं:

 demo=# explain (costs off) select * from routes_t where days_of_week = ARRAY[2,4,7];

  QUERY PLAN ----------------------------------------------------------- Bitmap Heap Scan on routes_t Recheck Cond: (days_of_week = '{2,4,7}'::integer[]) -> Bitmap Index Scan on routes_t_days_of_week_idx Index Cond: (days_of_week = '{2,4,7}'::integer[]) (4 rows)

ऐसा प्रतीत होता है कि उनमें से छह हैं:

 demo=# select flight_no, departure_airport_name, arrival_airport_name, days_of_week from routes_t where days_of_week = ARRAY[2,4,7];

  flight_no | departure_airport_name | arrival_airport_name | days_of_week -----------+------------------------+----------------------+-------------- PG0005 | Domodedovo | Pskov | {2,4,7} PG0049 | Vnukovo | Gelendzhik | {2,4,7} PG0113 | Naryan-Mar | Domodedovo | {2,4,7} PG0249 | Domodedovo | Gelendzhik | {2,4,7} PG0449 | Stavropol | Vnukovo | {2,4,7} PG0540 | Barnaul | Vnukovo | {2,4,7} (6 rows)

यह क्वेरी कैसे निष्पादित की जाती है? ठीक उसी तरह जैसा कि ऊपर वर्णित है:

सरणी {2,4,7} से, जो यहां खोज क्वेरी की भूमिका निभाता है, तत्वों (खोज कीवर्ड) को निकाला जाता है। जाहिर है, ये "2", "4" और "7" के मूल्य हैं।
तत्वों के पेड़ में, निकाले गए कुंजी पाए जाते हैं, और उनमें से प्रत्येक के लिए TID की सूची का चयन किया जाता है।
पाए गए सभी TID में से, संगति फ़ंक्शन उन का चयन करता है जो क्वेरी से ऑपरेटर से मेल खाते हैं। = ऑपरेटर के लिए, केवल उन TID का मिलान होता है जो तीनों सूचियों में होते हैं (दूसरे शब्दों में, प्रारंभिक सरणी में सभी तत्व होने चाहिए)। लेकिन यह पर्याप्त नहीं है: यह सरणी के लिए किसी भी अन्य मूल्यों को शामिल नहीं करने के लिए भी आवश्यक है, और हम सूचकांक के साथ इस स्थिति की जांच नहीं कर सकते हैं। इसलिए, इस स्थिति में, पहुँच विधि अनुक्रमण इंजन को तालिका के साथ लौटे सभी TID को फिर से जाँचने के लिए कहती है।

दिलचस्प है, वहाँ रणनीतियाँ हैं (उदाहरण के लिए, "सरणी में निहित") जो कुछ भी जांच नहीं कर सकती है और तालिका के साथ पाए गए सभी TID को फिर से जाँचना होगा।

लेकिन क्या करें यदि हमें मंगलवार, गुरुवार और रविवार को मास्को से रवाना होने वाली उड़ानों को जानना है? सूचकांक अतिरिक्त स्थिति का समर्थन नहीं करेगा, जो "फ़िल्टर" कॉलम में मिलेगा।

 demo=# explain (costs off) select * from routes_t where days_of_week = ARRAY[2,4,7] and departure_city = 'Moscow';

  QUERY PLAN ----------------------------------------------------------- Bitmap Heap Scan on routes_t Recheck Cond: (days_of_week = '{2,4,7}'::integer[]) Filter: (departure_city = 'Moscow'::text) -> Bitmap Index Scan on routes_t_days_of_week_idx Index Cond: (days_of_week = '{2,4,7}'::integer[]) (5 rows)

यहां यह ठीक है (सूचकांक वैसे भी केवल छह पंक्तियों का चयन करता है), लेकिन ऐसे मामलों में जहां अतिरिक्त स्थिति चयनात्मक क्षमता को बढ़ाती है, ऐसे समर्थन के लिए वांछित है। हालाँकि, हम केवल इंडेक्स नहीं बना सकते हैं:

 demo=# create index on routes_t using gin(days_of_week,departure_city);

 ERROR: data type text has no default operator class for access method "gin" HINT: You must specify an operator class for the index or define a default operator class for the data type.

लेकिन " btree_gin " एक्सटेंशन मदद करेगा, जो GIN ऑपरेटर कक्षाएं जोड़ता है जो एक नियमित बी-ट्री के काम का अनुकरण करते हैं।

 demo=# create extension btree_gin; demo=# create index on routes_t using gin(days_of_week,departure_city); demo=# explain (costs off) select * from routes_t where days_of_week = ARRAY[2,4,7] and departure_city = 'Moscow';

  QUERY PLAN --------------------------------------------------------------------- Bitmap Heap Scan on routes_t Recheck Cond: ((days_of_week = '{2,4,7}'::integer[]) AND (departure_city = 'Moscow'::text)) -> Bitmap Index Scan on routes_t_days_of_week_departure_city_idx Index Cond: ((days_of_week = '{2,4,7}'::integer[]) AND (departure_city = 'Moscow'::text)) (4 rows)

JSONB

एक यौगिक डेटा प्रकार का एक और उदाहरण जिसमें अंतर्निहित GIN समर्थन है JSON। JSON मूल्यों के साथ काम करने के लिए, कई ऑपरेटरों और कार्यों को वर्तमान में परिभाषित किया गया है, जिनमें से कुछ को अनुक्रमित करके उपयोग किया जा सकता है:

 postgres=# select opc.opcname, amop.amopopr::regoperator, amop.amopstrategy as str from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop where opc.opcname in ('jsonb_ops','jsonb_path_ops') and opf.oid = opc.opcfamily and am.oid = opf.opfmethod and amop.amopfamily = opc.opcfamily and am.amname = 'gin' and amop.amoplefttype = opc.opcintype;

  opcname | amopopr | str ----------------+------------------+----- jsonb_ops | ?(jsonb,text) | 9 top-level key exists jsonb_ops | ?|(jsonb,text[]) | 10 some top-level key exists jsonb_ops | ?&(jsonb,text[]) | 11 all top-level keys exist jsonb_ops | @>(jsonb,jsonb) | 7 JSON value is at top level jsonb_path_ops | @>(jsonb,jsonb) | 7 (5 rows)

जैसा कि हम देख सकते हैं, दो ऑपरेटर कक्षाएं उपलब्ध हैं: "jsonb_ops" और "jsonb_path_ops"।

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

उदाहरण के लिए, आइए JSON के रूप में "मार्गों" से कुछ पंक्तियों का प्रतिनिधित्व करते हैं:

 demo=# create table routes_jsonb as select to_jsonb(t) route from ( select departure_airport_name, arrival_airport_name, days_of_week from routes order by flight_no limit 4 ) t; demo=# select ctid, jsonb_pretty(route) from routes_jsonb;

  ctid | jsonb_pretty -------+------------------------------------------------- (0,1) | { + | "days_of_week": [ + | 1 + | ], + | "arrival_airport_name": "Surgut", + | "departure_airport_name": "Ust-Ilimsk" + | } (0,2) | { + | "days_of_week": [ + | 2 + | ], + | "arrival_airport_name": "Ust-Ilimsk", + | "departure_airport_name": "Surgut" + | } (0,3) | { + | "days_of_week": [ + | 1, + | 4 + | ], + | "arrival_airport_name": "Sochi", + | "departure_airport_name": "Ivanovo-Yuzhnyi"+ | } (0,4) | { + | "days_of_week": [ + | 2, + | 5 + | ], + | "arrival_airport_name": "Ivanovo-Yuzhnyi", + | "departure_airport_name": "Sochi" + | } (4 rows)

 demo=# create index on routes_jsonb using gin(route);

सूचकांक निम्नानुसार लग सकता है:

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

 demo=# explain (costs off) select jsonb_pretty(route) from routes_jsonb where route @> '{"days_of_week": [5]}';

  QUERY PLAN --------------------------------------------------------------- Bitmap Heap Scan on routes_jsonb Recheck Cond: (route @> '{"days_of_week": [5]}'::jsonb) -> Bitmap Index Scan on routes_jsonb_route_idx Index Cond: (route @> '{"days_of_week": [5]}'::jsonb) (4 rows)

JSON दस्तावेज़ की जड़ से शुरू, @> ऑपरेटर यह बताता है कि क्या निर्दिष्ट मार्ग ( "days_of_week": [5] होता है। यहाँ क्वेरी एक पंक्ति लौटेगी:

 demo=# select jsonb_pretty(route) from routes_jsonb where route @> '{"days_of_week": [5]}';

  jsonb_pretty ------------------------------------------------ { + "days_of_week": [ + 2, + 5 + ], + "arrival_airport_name": "Ivanovo-Yuzhnyi",+ "departure_airport_name": "Sochi" + } (1 row)

क्वेरी निम्नानुसार की जाती है:

खोज क्वेरी में ( "days_of_week": [5] ) तत्व (खोज कुंजी) निकाले जाते हैं: "days_of_week" और "5"।
तत्वों की पेड़ में निकाली गई चाबियां पाई जाती हैं, और उनमें से प्रत्येक के लिए TIDs की सूची का चयन किया जाता है: "5" - (0.4) के लिए, और "दिनों_फोक" के लिए - (0,1), (0,2) ), (0.3), (0.4)।
पाए गए सभी TID में से, संगति फ़ंक्शन उन का चयन करता है जो क्वेरी से ऑपरेटर से मेल खाते हैं। @> ऑपरेटर के लिए, खोज क्वेरी से सभी तत्व नहीं होने वाले दस्तावेज़ सुनिश्चित नहीं होंगे, इसलिए केवल (0,4) बचा हुआ है। लेकिन हमें अभी भी तालिका के साथ छोड़े गए TID को फिर से जाँचने की आवश्यकता है क्योंकि यह इंडेक्स से स्पष्ट नहीं है कि JSON दस्तावेज़ में कौन से तत्व पाए जाते हैं।

अन्य ऑपरेटरों के अधिक विवरण खोजने के लिए, आप दस्तावेज़ पढ़ सकते हैं।

JSON से निपटने के लिए पारंपरिक संचालन के अलावा, "jsquery" विस्तार लंबे समय से उपलब्ध है, जो कि समृद्ध क्षमताओं (और निश्चित रूप से, GIN अनुक्रमित के समर्थन के साथ) के साथ एक क्वेरी भाषा को परिभाषित करता है। इसके अलावा, 2016 में, एक नया SQL मानक जारी किया गया था, जो अपने स्वयं के संचालन और क्वेरी भाषा "SQL / JSON पथ" को परिभाषित करता है। इस मानक का कार्यान्वयन पहले ही पूरा हो चुका है, और हमें विश्वास है कि यह PostgreSQL 11 में दिखाई देगा।

SQL / JSON पथ पैच अंततः PostgreSQL 12 के लिए प्रतिबद्ध था, जबकि अन्य टुकड़े अभी भी रास्ते में हैं। उम्मीद है कि हम PostgreSQL 13 में पूरी तरह से लागू सुविधा देखेंगे।

internals

हम " पेजिंस्पेक्ट " एक्सटेंशन का उपयोग करके GIN इंडेक्स के अंदर देख सकते हैं।

 fts=# create extension pageinspect;

मेटा पृष्ठ की जानकारी सामान्य आँकड़े दिखाती है:

 fts=# select * from gin_metapage_info(get_raw_page('mail_messages_tsv_idx',0));

 -[ RECORD 1 ]----+----------- pending_head | 4294967295 pending_tail | 4294967295 tail_free_size | 0 n_pending_pages | 0 n_pending_tuples | 0 n_total_pages | 22968 n_entry_pages | 13751 n_data_pages | 9216 n_entries | 1423598 version | 2

पृष्ठ संरचना एक विशेष क्षेत्र प्रदान करती है जहां पहुंच विधियां उनकी जानकारी संग्रहीत करती हैं; यह क्षेत्र निर्वात जैसे सामान्य कार्यक्रमों के लिए "अपारदर्शी" है। "Gin_page_opaque_info" फ़ंक्शन GIN के लिए यह डेटा दिखाता है। उदाहरण के लिए, हम सूचकांक पृष्ठों के सेट को जान सकते हैं:

 fts=# select flags, count(*) from generate_series(1,22967) as g(id), -- n_total_pages gin_page_opaque_info(get_raw_page('mail_messages_tsv_idx',g.id)) group by flags;

  flags | count ------------------------+------- {meta} | 1 meta page {} | 133 internal page of element B-tree {leaf} | 13618 leaf page of element B-tree {data} | 1497 internal page of TID B-tree {data,leaf,compressed} | 7719 leaf page of TID B-tree (5 rows)

"Gin_leafpage_items" फ़ंक्शन पृष्ठों पर संग्रहीत TID के बारे में जानकारी प्रदान करता है {डेटा, पत्ती, संपीडित}:

 fts=# select * from gin_leafpage_items(get_raw_page('mail_messages_tsv_idx',2672));

 -[ RECORD 1 ]--------------------------------------------------------------------- first_tid | (239,44) nbytes | 248 tids | {"(239,44)","(239,47)","(239,48)","(239,50)","(239,52)","(240,3)",... -[ RECORD 2 ]--------------------------------------------------------------------- first_tid | (247,40) nbytes | 248 tids | {"(247,40)","(247,41)","(247,44)","(247,45)","(247,46)","(248,2)",... ...

यहां ध्यान दें कि TID के वृक्ष के पृष्ठ छोड़ दें वास्तव में अलग-अलग बिंदुओं की बजाय तालिका पंक्तियों की छोटी संकुचित सूची होती है।

गुण

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

  amname | name | pg_indexam_has_property --------+---------------+------------------------- gin | can_order | f gin | can_unique | f gin | can_multi_col | t gin | can_exclude | f

दिलचस्प है, जीआईएन बहुरंगी अनुक्रमणिकाओं के निर्माण का समर्थन करता है। हालांकि, एक नियमित बी-पेड़ के विपरीत, यौगिक कुंजियों के बजाय, एक बहुरंगी सूचकांक अभी भी व्यक्तिगत तत्वों को संग्रहीत करेगा, और प्रत्येक तत्व के लिए कॉलम संख्या का संकेत दिया जाएगा।

निम्नलिखित सूचकांक-परत गुण उपलब्ध हैं:

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

ध्यान दें कि TID (इंडेक्स स्कैन) द्वारा परिणाम लौटाने का समर्थन नहीं किया गया है; केवल बिटमैप स्कैन संभव है।

बैकवर्ड स्कैन भी समर्थित नहीं है: यह सुविधा केवल इंडेक्स स्कैन के लिए आवश्यक है, लेकिन बिटमैप स्कैन के लिए नहीं।

और निम्नलिखित स्तंभ-परत गुण हैं:

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

यहां कुछ भी उपलब्ध नहीं है: कोई छँटाई नहीं है (जो स्पष्ट है), कवर के रूप में सूचकांक का कोई उपयोग नहीं है (चूंकि दस्तावेज़ स्वयं सूचकांक में संग्रहीत नहीं है), नल का कोई हेरफेर (क्योंकि यह यौगिक प्रकार के तत्वों के लिए कोई मतलब नहीं है) ।

अन्य डेटा प्रकार

कुछ और एक्सटेंशन उपलब्ध हैं जो कुछ डेटा प्रकारों के लिए GIN का समर्थन जोड़ते हैं।

" pg_trgm " हमें शब्दों की "समानता" निर्धारित करने में सक्षम बनाता है कि कितने समान तीन-अक्षर अनुक्रम (ट्रिगर्स) उपलब्ध हैं। दो ऑपरेटर कक्षाएं जोड़ी जाती हैं, "gist_trgm_ops" और "gin_trgm_ops", जो विभिन्न ऑपरेटरों का समर्थन करते हैं, जिसमें LIKE और नियमित अभिव्यक्ति के साधनों की तुलना शामिल है। टाइपोस को ठीक करने के लिए शब्द विकल्पों का सुझाव देने के लिए हम पूर्ण-पाठ खोज के साथ इस एक्सटेंशन का उपयोग कर सकते हैं।
" hstore " "key-value" स्टोरेज को लागू करता है। इस डेटा प्रकार के लिए, विभिन्न एक्सेस विधियों के लिए ऑपरेटर कक्षाएं GIN सहित उपलब्ध हैं। हालांकि, "जोंसब" डेटा प्रकार की शुरुआत के साथ, "हस्टोर" का उपयोग करने के लिए कोई विशेष कारण नहीं हैं।
" इंट्रैरे " पूर्णांक सरणियों की कार्यक्षमता का विस्तार करता है। इंडेक्स सपोर्ट में GiST, साथ ही GIN ("gin__int_ops" ऑपरेटर क्लास) शामिल है।

और इन दोनों एक्सटेंशनों का उल्लेख पहले ही किया जा चुका है:

" btree_gin " नियमित डेटा प्रकारों के लिए GIN समर्थन जोड़ता है, ताकि उन्हें बहु प्रकार के अनुक्रमणिका में कंपाउंड प्रकारों के साथ उपयोग किया जा सके।
" jsquery " JSON क्वेरी के लिए एक भाषा और इस भाषा के सूचकांक समर्थन के लिए एक ऑपरेटर वर्ग को परिभाषित करता है। यह एक्सटेंशन एक मानक PostgreSQL वितरण में शामिल नहीं है।

पर पढ़ें ।

PostgreSQL में सूचकांक - 7 (GIN)

जिन