👏🏻 ✊🏿 👊🏽 PostgreSQL में अनुक्रमित की सभी सुविधाओं का उपयोग करना 🤦🏿 🏇🏽 🕉️

Postgres world में, डेटाबेस रिपॉजिटरी को कुशलतापूर्वक (हीप, हीप) कहा जाता है। Postgres इसके लिए क्लस्टरिंग का समर्थन नहीं करता है, और MVCC आर्किटेक्चर आपको एक ही ट्यूपल के कई संस्करणों को जमा करने का कारण बनता है। इसलिए, अनुप्रयोगों का समर्थन करने के लिए प्रभावी अनुक्रमित बनाने और बनाए रखने में सक्षम होना बहुत महत्वपूर्ण है।

इंडेक्स के उपयोग के अनुकूलन और सुधार के लिए यहां कुछ सुझाव दिए गए हैं।

नोट: नीचे दिखाए गए क्वेरीज़ एक अनमॉडिफाइड पगिला नमूना डेटाबेस पर काम करते हैं।

कवरिंग इंडेक्स का उपयोग करना

आइए निष्क्रिय उपयोगकर्ताओं के लिए ईमेल पते पुनः प्राप्त करने के अनुरोध की समीक्षा करें। customer तालिका में एक active कॉलम है, और अनुरोध सरल है:

 pagila=# EXPLAIN SELECT email FROM customer WHERE active=0; QUERY PLAN ----------------------------------------------------------- Seq Scan on customer (cost=0.00..16.49 rows=15 width=32) Filter: (active = 0) (2 rows)

क्वेरी customer तालिका को स्कैन करने का पूरा अनुक्रम आमंत्रित करती है। active कॉलम के लिए एक इंडेक्स बनाएं:

 pagila=# CREATE INDEX idx_cust1 ON customer(active); CREATE INDEX pagila=# EXPLAIN SELECT email FROM customer WHERE active=0; QUERY PLAN ----------------------------------------------------------------------------- Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32) Index Cond: (active = 0) (2 rows)

इसने मदद की, बाद में स्कैन एक " index scan " में बदल गया। इसका मतलब यह है कि Postgres idx_cust1 इंडेक्स को स्कैन करेगा, और फिर अन्य कॉलम (इस मामले में, email कॉलम) के मानों को पढ़ने के लिए तालिका के ढेर को खोजना जारी रखेगा जिसे क्वेरी की आवश्यकता है।

PostgreSQL 11 ने इंडेक्स को कवर किया। वे आपको एक या एक से अधिक अतिरिक्त कॉलम को इंडेक्स में शामिल करने की अनुमति देते हैं - उनका मान इंडेक्स डेटा स्टोर में संग्रहीत किया जाता है।

यदि हमने इस सुविधा का उपयोग किया है और सूचकांक के अंदर ईमेल मूल्य जोड़ा है, तो Postgres को तालिका के ढेर में email मूल्य को देखने की आवश्यकता नहीं होगी। आइए देखें कि क्या यह काम करता है:

 pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email); CREATE INDEX pagila=# EXPLAIN SELECT email FROM customer WHERE active=0; QUERY PLAN ---------------------------------------------------------------------------------- Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32) Index Cond: (active = 0) (2 rows)

" Index Only Scan " हमें बताता है कि क्वेरी को अब केवल एक इंडेक्स की आवश्यकता है, जो टेबल हीप को पढ़ने के लिए सभी डिस्क I / O से बचने में मदद करता है।

आज, कवरिंग इंडेक्स केवल बी-पेड़ों के लिए उपलब्ध हैं। हालांकि, इस मामले में, एस्कॉर्ट प्रयास अधिक होंगे।

आंशिक सूचकांक का उपयोग करना

आंशिक अनुक्रमणिका केवल तालिका में पंक्तियों का एक सबसेट है। यह अनुक्रमित और तेजी से स्कैन के आकार को बचाता है।

मान लीजिए हमें अपने कैलिफोर्निया के ग्राहकों से ईमेल पते की एक सूची प्राप्त करने की आवश्यकता है। अनुरोध इस प्रकार होगा:

 SELECT c.email FROM customer c JOIN address a ON c.address_id = a.address_id WHERE a.district = 'California'; which has a query plan that involves scanning both the tables that are joined: pagila=# EXPLAIN SELECT c.email FROM customer c pagila-# JOIN address a ON c.address_id = a.address_id pagila-# WHERE a.district = 'California'; QUERY PLAN ---------------------------------------------------------------------- Hash Join (cost=15.65..32.22 rows=9 width=32) Hash Cond: (c.address_id = a.address_id) -> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34) -> Hash (cost=15.54..15.54 rows=9 width=4) -> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4) Filter: (district = 'California'::text) (6 rows)

सामान्य सूचकांक हमें क्या देगा:

 pagila=# CREATE INDEX idx_address1 ON address(district); CREATE INDEX pagila=# EXPLAIN SELECT c.email FROM customer c pagila-# JOIN address a ON c.address_id = a.address_id pagila-# WHERE a.district = 'California'; QUERY PLAN --------------------------------------------------------------------------------------- Hash Join (cost=12.98..29.55 rows=9 width=32) Hash Cond: (c.address_id = a.address_id) -> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34) -> Hash (cost=12.87..12.87 rows=9 width=4) -> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4) Recheck Cond: (district = 'California'::text) -> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0) Index Cond: (district = 'California'::text) (8 rows)

address स्कैन को idx_address1 इंडेक्स idx_address1 द्वारा बदल दिया गया था, और फिर address हीप को स्कैन किया गया था।

चूंकि यह लगातार क्वेरी है और इसे अनुकूलित करने की आवश्यकता है, हम एक आंशिक सूचकांक का उपयोग कर सकते हैं जो केवल उन पंक्तियों को उन पते के साथ अनुक्रमित करता है जिनमें क्षेत्र 'California' :

 pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California'; CREATE INDEX pagila=# EXPLAIN SELECT c.email FROM customer c pagila-# JOIN address a ON c.address_id = a.address_id pagila-# WHERE a.district = 'California'; QUERY PLAN ------------------------------------------------------------------------------------------------ Hash Join (cost=12.38..28.96 rows=9 width=32) Hash Cond: (c.address_id = a.address_id) -> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34) -> Hash (cost=12.27..12.27 rows=9 width=4) -> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4) (5 rows)

अब अनुरोध केवल idx_address2 पढ़ता है और address तालिका को स्पर्श नहीं करता है।

मल्टी-वैल्यू इंडेक्स का उपयोग करना

कुछ स्तंभ जिन्हें अनुक्रमित करने की आवश्यकता होती है, उनमें एक स्केलर डेटा प्रकार नहीं हो सकता है। jsonb , arrays और tsvector जैसे jsonb प्रकारों में एक या कई मान होते हैं। यदि आपको ऐसे स्तंभों को अनुक्रमित करने की आवश्यकता है, तो आपको आमतौर पर इन स्तंभों के सभी व्यक्तिगत मूल्यों की खोज करनी होगी।

आइए सभी फिल्मों के नाम खोजने की कोशिश करें जिनमें असफलता से कटौती होती है। film तालिका में एक पाठ स्तंभ है जिसे special_features कहा जाता है। यदि फिल्म में यह "विशेष गुण" है, तो स्तंभ में पाठ के Behind The Scenes एक तत्व के रूप में एक तत्व होता है। ऐसी सभी फिल्मों की खोज करने के लिए, हमें special_features किसी भी मूल्य के लिए "पर्दे के पीछे" के साथ सभी पंक्तियों का चयन करने की आवश्यकता है:

 SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

नियंत्रण ऑपरेटर @> यह देखने के लिए जाँच करता है कि क्या दाईं ओर बाईं ओर का उपसमूह है या नहीं।

अनुरोध योजना:

 pagila=# EXPLAIN SELECT title FROM film pagila-# WHERE special_features @> '{"Behind The Scenes"}'; QUERY PLAN ----------------------------------------------------------------- Seq Scan on film (cost=0.00..67.50 rows=5 width=15) Filter: (special_features @> '{"Behind The Scenes"}'::text[]) (2 rows)

जो 67 की लागत के साथ फुल हीप स्कैन का अनुरोध करता है।

आइए देखें कि क्या नियमित बी-ट्री इंडेक्स हमारी मदद करता है:

 pagila=# CREATE INDEX idx_film1 ON film(special_features); CREATE INDEX pagila=# EXPLAIN SELECT title FROM film pagila-# WHERE special_features @> '{"Behind The Scenes"}'; QUERY PLAN ----------------------------------------------------------------- Seq Scan on film (cost=0.00..67.50 rows=5 width=15) Filter: (special_features @> '{"Behind The Scenes"}'::text[]) (2 rows)

सूचकांक पर भी विचार नहीं किया गया। बी-ट्री इंडेक्स इंडेक्स मूल्यों में व्यक्तिगत तत्वों के अस्तित्व के बारे में पता नहीं है।

हमें GIN इंडेक्स चाहिए।

 pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features); CREATE INDEX pagila=# EXPLAIN SELECT title FROM film pagila-# WHERE special_features @> '{"Behind The Scenes"}'; QUERY PLAN --------------------------------------------------------------------------- Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15) Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[]) -> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0) Index Cond: (special_features @> '{"Behind The Scenes"}'::text[]) (4 rows)

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

डुप्लिकेट इंडेक्स से छुटकारा पाएं

सूचकांक समय के साथ जमा होते हैं, और कभी-कभी एक नए सूचकांक में पिछली परिभाषा के समान परिभाषा हो सकती है। इंडेक्स की मानव पठनीय SQL परिभाषाएँ प्राप्त करने के लिए, आप कैटलॉग दृश्य pg_indexes उपयोग कर सकते हैं। आप समान परिभाषाएँ भी आसानी से पा सकते हैं:

  SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn FROM pg_indexes GROUP BY defn HAVING count(*) > 1; And here's the result when run on the stock pagila database: pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn pagila-# FROM pg_indexes pagila-# GROUP BY defn pagila-# HAVING count(*) > 1; indexes | defn ------------------------------------------------------------------------+------------------------------------------------------------------ {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id (6 rows)

सुपरसेट सूचकांक

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

यदि आपको ऐसे अनुक्रमित की परिभाषा को स्वचालित करने की आवश्यकता है, तो आप pg_cex से pg_catalog तालिका से प्रारंभ कर सकते हैं।

अप्रयुक्त सूचकांक

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

यहां 'public' स्कीमा में सभी सूचकांकों के वर्तमान स्कैन काउंट प्राप्त करने का अनुरोध है:

 SELECT relname, indexrelname, idx_scan FROM pg_catalog.pg_stat_user_indexes WHERE schemaname = 'public'; with output like this: pagila=# SELECT relname, indexrelname, idx_scan pagila-# FROM pg_catalog.pg_stat_user_indexes pagila-# WHERE schemaname = 'public' pagila-# LIMIT 10; relname | indexrelname | idx_scan ---------------+--------------------+---------- customer | customer_pkey | 32093 actor | actor_pkey | 5462 address | address_pkey | 660 category | category_pkey | 1000 city | city_pkey | 609 country | country_pkey | 604 film_actor | film_actor_pkey | 0 film_category | film_category_pkey | 0 film | film_pkey | 11043 inventory | inventory_pkey | 16048 (10 rows)

कम तालों वाले इंडेक्स बनाएं

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

समानांतर इंडेक्स निर्माण सक्षम करें

PostgreSQL 11 में, बी-ट्री इंडेक्स बनाना प्रतिस्पर्धी है। निर्माण प्रक्रिया को गति देने के लिए, कई समानांतर श्रमिकों का उपयोग किया जा सकता है। हालाँकि, सुनिश्चित करें कि ये कॉन्फ़िगरेशन पैरामीटर सही तरीके से सेट हैं:

 SET max_parallel_workers = 32; SET max_parallel_maintenance_workers = 16;

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

पृष्ठभूमि सूचकांक निर्माण

आप क्रिएट इंडेक्स कमांड के CONCURRENTLY पैरामीटर का उपयोग करके पृष्ठभूमि में एक इंडेक्स बना सकते हैं:

 pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district); CREATE INDEX

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

Postgres अनुक्रमित बनाने के लिए कई लचीले विकल्प प्रदान करता है और किसी भी विशेष मामलों को हल करने के तरीके, साथ ही आपके आवेदन की विस्फोटक वृद्धि के मामले में डेटाबेस का प्रबंधन करने के तरीके प्रदान करता है। हम आशा करते हैं कि ये युक्तियां आपके प्रश्नों को शीघ्रता से और आपके डेटाबेस को पैमाना बनाने में मदद करेंगी।

PostgreSQL में अनुक्रमित की सभी सुविधाओं का उपयोग करना