क्वेरी ऑप्टिमाइज़ेशन। PostgreSQL में भाग मूल बातें (भाग 2)

मैं गिलियूम लेलार्ज द्वारा लिखित समझ का आधिकारिक संशोधन प्रकाशित करना जारी रखता हूं।
एक बार फिर, मैं ध्यान देता हूं कि कुछ जानकारी संक्षिप्तता के लिए छोड़ी गई है, इसलिए मैं अत्यधिक अनुशंसा करता हूं कि आप मूल पढ़ें।

पिछले भाग:

भाग 1

कैश

हमारे अनुरोध को पूरा करने के दौरान भौतिक स्तर पर क्या होता है? चलिए इसका पता लगाते हैं। मेरा सर्वर उबंटू 13.10 पर है। ओएस स्तर डिस्क कैश का उपयोग किया जाता है।
मैं PostgreSQL को रोक देता हूं, जबरन फाइल सिस्टम में बदलाव करता हूं, कैश साफ़ करता हूं, PostgreSQL शुरू करता हूं:

> sudo service postgresql-9.3 stop > sudo sync > sudo su - # echo 3 > /proc/sys/vm/drop_caches # exit > sudo service postgresql-9.3 start 

अब कैश साफ़ कर दिए गए हैं, क्वेरी को BUFFERS विकल्प के साथ निष्पादित करने का प्रयास करें

 EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..18334.10 पंक्तियाँ = 1000010 चौड़ाई = 37) (वास्तविक समय = 0.525..734.754 पंक्तियाँ = 1000010 लूप = 1)
बफ़र्स: साझा पढ़ा = 8334
कुल रनटाइम: 1253.177 एमएस
(3 पंक्तियाँ)

तालिका को भागों - ब्लॉकों में पढ़ा जाता है। कैश खाली है। तालिका डिस्क से पूरी तरह से पढ़ी जाती है। ऐसा करने के लिए, हमें 8334 ब्लॉक गिनने पड़े।
Buffers: shared read - डिस्क से पढ़े गए ब्लॉक की संख्या।

अंतिम अनुरोध दोहराएं

 EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..18334.10 पंक्तियाँ = 1000010 चौड़ाई = 37) (वास्तविक समय = 0.173..693.000 पंक्तियाँ = 1000010 लूप = 1)
बफ़र्स: साझा हिट = 32 पढ़ा = 8302
कुल रनटाइम: 1208.433 एमएस
(3 पंक्तियाँ)

Buffers: shared hit - पोस्टग्रेएसक्यूएल कैश से पढ़े गए ब्लॉक की संख्या।
यदि आप इस क्वेरी को कई बार दोहराते हैं, तो आप देखेंगे कि PostgreSQL को हर बार कैश से अधिक से अधिक डेटा कैसे मिलता है। हर क्वेरी के साथ, PostgreSQL अपने कैश को पॉप्युलेट करता है।
कैश से संचालन पढ़ें डिस्क से रीड ऑपरेशन की तुलना में तेज़ हैं। आप Total runtime के मान को ट्रैक करके इस प्रवृत्ति को नोटिस कर सकते हैं।
कैशे का आकार, postgresql.conf फाइल में shared_buffers स्थिरांक द्वारा निर्धारित किया जाता है।

कहां

अनुरोध में एक शर्त जोड़ें

 EXPLAIN SELECT * FROM foo WHERE c1 > 500; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..20834.12 पंक्तियों = 999522 चौड़ाई = 37)
फ़िल्टर: (c1> 500)
(2 पंक्तियाँ)

मेज पर कोई अनुक्रमणिका नहीं हैं। क्वेरी निष्पादित करते समय, प्रत्येक तालिका रिकॉर्ड क्रमिक रूप से ( Seq Scan ) पढ़ा जाता है। प्रत्येक रिकॉर्ड की तुलना c1 > 500 । यदि स्थिति संतुष्ट है, तो परिणाम में रिकॉर्ड दर्ज किया जाता है। अन्यथा, इसे छोड़ दिया जाता है। Filter मतलब बिल्कुल इस व्यवहार से है।
cost का cost , जो तार्किक है, बढ़ गया है।
परिणाम पंक्तियों की अपेक्षित संख्या - rows - में कमी आई है।
मूल बताता है कि cost ऐसा मूल्य क्यों लेती है, और यह भी कि पंक्तियों की अपेक्षित संख्या की गणना कैसे की जाती है।

यह सूचकांक बनाने का समय है।

 CREATE INDEX ON foo(c1); EXPLAIN SELECT * FROM foo WHERE c1 > 500; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..20834.12 पंक्तियों = 999519 चौड़ाई = 37)
फ़िल्टर: (c1> 500)
(2 पंक्तियाँ)

पंक्तियों की अपेक्षित संख्या बदल गई है। स्पष्ट करें। बाकी कोई नई बात नहीं है। सूचकांक के बारे में क्या?

 EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..20834.12 पंक्तियों = 999519 चौड़ाई = 37) (वास्तविक समय = 0.572..848.895 पंक्तियों = 999500 छोरों = 1)
फ़िल्टर: (c1> 500)
फ़िल्टर द्वारा निकाली गई पंक्तियाँ: 510
कुल रनटाइम: 1330.788 एमएस
(4 पंक्तियाँ)

एक मिलियन से अधिक केवल 510 लाइनों को फ़िल्टर किया जाता है। मुझे तालिका के 99.9% से अधिक की गणना करनी थी।

Seq स्कैन को अक्षम करके सूचकांक के उपयोग को मजबूर करें:

 SET enable_seqscan TO off; EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500; 

जल्दी योजना
- foo_c1_idx का foo (लागत = 0.42..34623.01 पंक्तियों = 999519 चौड़ाई = 37) का उपयोग करके सूचकांक स्कैन (वास्तविक समय = 0.178..1018.045 पंक्तियों = 999500 छोरों = 1)
सूचकांक कंडोम: (c1> 500)
कुल रनटाइम: 1434.429 एमएस
(3 पंक्तियाँ)

Filter बजाय Index Scan , Index Cond foo_c1_idx - इंडेक्स foo_c1_idx का उपयोग किया जाता है।
जब लगभग पूरी तालिका लाते हैं, तो सूचकांक का उपयोग करने से cost और क्वेरी निष्पादन समय बढ़ता है। योजनाकार मूर्ख नहीं है!

Seq Scan के उपयोग पर प्रतिबंध रद्द करना न भूलें:

 SET enable_seqscan TO on; 

अनुरोध बदलें:

 EXPLAIN SELECT * FROM foo WHERE c1 < 500; 

जल्दी योजना
- सूचकांक स्कैन foo_c1_idx का उपयोग कर foo (लागत = 0.42..25.78 पंक्तियों = 491% =)
सूचकांक कंडोम: (c1 <500)
(2 पंक्तियाँ)

फिर योजनाकार ने सूचकांक का उपयोग करने का निर्णय लिया।

हम शर्त को जटिल करते हैं। टेक्स्ट बॉक्स का उपयोग करें।

 EXPLAIN SELECT * FROM foo WHERE c1 < 500 AND c2 LIKE 'abcd%'; 

जल्दी योजना
- foo_c1_idx का foo (लागत = 0.42..27.00 पंक्तियों = 1 चौड़ाई = 37) का उपयोग करके सूचकांक स्कैन
सूचकांक कंडोम: (c1 <500)
फ़िल्टर: (c2 ~~ 'abcd%' :: पाठ)
(3 पंक्तियाँ)

जैसा कि आप देख सकते हैं, सूचकांक foo_c1_idx c1 < 500 लिए foo_c1_idx । c2 ~~ 'abcd%'::text , एक फिल्टर का उपयोग किया जाता है।
कृपया ध्यान दें कि परिणामों के आउटपुट में LIKE ऑपरेटर के POSIX प्रारूप का उपयोग किया जाता है।

यदि शर्त केवल पाठ फ़ील्ड है:

 EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c2 LIKE 'abcd%'; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..20834.12 पंक्तियों = 100 चौड़ाई = 37) (वास्तविक समय = 14.497..412.030 पंक्तियों = 10 छोरों = 1)
फ़िल्टर: (c2 ~~ 'abcd%' :: पाठ)
फ़िल्टर द्वारा निकाली गई पंक्तियाँ: 1,000,000
कुल रनटाइम: 412.120 एमएस
(4 पंक्तियाँ)

उम्मीद, Seq Scan ।

हम c2 पर एक इंडेक्स बनाते हैं:

 CREATE INDEX ON foo(c2); EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c2 LIKE 'abcd%'; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..20834.12 पंक्तियाँ = 100 चौड़ाई = 37) (वास्तविक समय = 20.992..424.946 पंक्तियाँ = 10 छोर = 1)
फ़िल्टर: (c2 ~~ 'abcd%' :: पाठ)
फ़िल्टर द्वारा निकाली गई पंक्तियाँ: 1,000,000
कुल रनटाइम: 425.039 एमएस
(4 पंक्तियाँ)

फिर से Seq Scan ? इंडेक्स का उपयोग नहीं किया जाता है क्योंकि मेरा डेटाबेस टेक्स्ट फ़ील्ड के लिए UTF-8 प्रारूप का उपयोग करता है।
ऐसे मामलों में एक इंडेक्स बनाते समय, आपको ऑपरेटर वर्ग text_pattern_ops उपयोग करना चाहिए:

 CREATE INDEX ON foo(c2 text_pattern_ops); EXPLAIN SELECT * FROM foo WHERE c2 LIKE 'abcd%'; 

जल्दी योजना
- बिटमैप ढेर स्कैन फू पर (लागत = 4.58..55.20 पंक्तियाँ = 100 चौड़ाई = 37)
फ़िल्टर: (c2 ~~ 'abcd%' :: पाठ)
-> बिटमैप इंडेक्स स्कैन foo_c2_idx1 (लागत = 0.00..4.55 पंक्तियों = 13 चौड़ाई = 0) पर
सूचकांक कंडोम: ((c2 ~> = ~ 'abcd' :: टेक्स्ट) और (c2 ~ <~ 'abce') टेक्स्ट)
(4 पंक्तियाँ)

हुर्रे! यह निकला!
Bitmap Index Scan - foo_c2_idx1 इंडेक्स का उपयोग उन रिकॉर्ड्स को निर्धारित करने के लिए किया जाता है जिनकी हमें आवश्यकता होती है, और उसके बाद PostgreSQL क्रॉल टेबल में ही हो जाता है: ( Bitmap Heap Scan ) - यह सुनिश्चित करने के लिए कि ये रिकॉर्ड वास्तव में मौजूद हैं। यह व्यवहार PostgreSQL संस्करण से संबंधित है।

यदि आप संपूर्ण पंक्ति का चयन नहीं करते हैं, लेकिन केवल वह क्षेत्र जिसके द्वारा सूचकांक बनाया गया है

 EXPLAIN SELECT c1 FROM foo WHERE c1 < 500; 

जल्दी योजना
- सूचकांक केवल foo_c1_idx का foo (लागत = 0.42..25.78 पंक्तियों = 491 चौड़ाई 4) का उपयोग करके स्कैन करें
सूचकांक कंडोम: (c1 <500)
(2 पंक्तियाँ)

Index Only Scan Index Scan तुलना में तेज़ है इस तथ्य के कारण कि आपको तालिका की पूरी पंक्ति पढ़ने की आवश्यकता नहीं है: width=4 ।

सारांश

• Seq स्कैन - पूरी तालिका पढ़ी जाती है।
• इंडेक्स स्कैन - WHERE की स्थिति के लिए इंडेक्स का उपयोग करता है, पंक्तियों का चयन करते समय तालिका को पढ़ता है।
• बिटमैप इंडेक्स स्कैन - पहले इंडेक्स स्कैन, फिर टेबल चयन नियंत्रण। बड़ी संख्या में लाइनों के लिए प्रभावी।
• इंडेक्स ओनली स्कैन सबसे तेज है। केवल इंडेक्स पढ़ा जाता है।

भाग ३