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

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

पिछले भाग:

भाग 1
भाग २

आदेश द्वारा

DROP INDEX foo_c1_idx; EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1; 

जल्दी योजना
- सॉर्ट (लागत = 117993.01..120493.04 पंक्तियाँ = 1000010 चौड़ाई = 37) (वास्तविक समय = 571.591..651.524 पंक्तियाँ = 1000010 लूप = 1)
क्रमबद्ध कुंजी: c1
सॉर्ट विधि: बाहरी मर्ज डिस्क: 45952kB
-> फू (लागत = 0.00..18334.10 पंक्तियों = 1000010 चौड़ाई = 37) पर सीक स्कैन (वास्तविक समय = 0.007..62.041 पंक्तियाँ = 1000010 लूप = 1)
कुल रनटाइम: 690.984 एमएस
(5 पंक्तियाँ)

सबसे पहले, Seq Scan टेबल foo उत्पादन किया foo है। फिर सॉर्ट Sort । EXPLAIN कमांड के आउटपुट में, साइन -> कार्यों का एक पदानुक्रम ( नोड ) इंगित करता है। पहले की गई क्रिया की जाती है, उतनी ही अधिक प्रदर्शित की जाती है।
Sort Key - सॉर्टिंग स्थिति।
Sort Method: external merge Disk - सॉर्ट करते समय, 45952kB के डिस्क वॉल्यूम पर एक अस्थायी फ़ाइल 45952kB ।

मैं उन लोगों से पूछता हूं जो external merge और external sort बीच के अंतर को समझाने के लिए विषय को समझते हैं ।

BUFFERS विकल्प के साथ जांचें:

 EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo ORDER BY c1; 

जल्दी योजना
- क्रमबद्ध (लागत = 117993.01..120493.04 पंक्तियाँ = 1000010 चौड़ाई = 37) (वास्तविक समय = 568.412..652.308 पंक्तियाँ = 1000010 लूप = 1)
क्रमबद्ध कुंजी: c1
सॉर्ट विधि: बाहरी मर्ज डिस्क: 45952kB
बफ़र्स: साझा हिट = 8334, अस्थायी पढ़ा = 5745 लिखा = 5745
-> foo पर Seq स्कैन (लागत = 0.00..18334.10 पंक्तियाँ = 1000010 चौड़ाई = 37) (वास्तविक समय = 0.010..68.203 पंक्तियाँ = 1000010 लूप = 1)
बफ़र्स: साझा हिट = 8334
कुल रनटाइम: 698.032 एमएस
(7 पंक्तियाँ)

दरअसल, temp read=5745 written=5745 - 5745 8Kb = 45960Kb के ब्लॉक अस्थायी फ़ाइल में लिखे गए और पढ़े गए। कैश में 8334 ब्लॉक के साथ संचालन किया गया था।

मेमोरी सिस्टम की तुलना में फाइल सिस्टम ऑपरेशन धीमा है।
आइए work_mem द्वारा उपयोग की जाने वाली मेमोरी की मात्रा बढ़ाने की कोशिश करें:

 SET work_mem TO '200MB'; EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1; 

जल्दी योजना
- क्रमबद्ध (लागत = 117993.01..120493.04 पंक्तियाँ = 1000010 चौड़ाई = 37) (वास्तविक समय = 265.301..296.777 पंक्तियाँ = 1000010 लूप = 1)
क्रमबद्ध कुंजी: c1
सॉर्ट विधि: quicksort मेमोरी: 102702kB
-> फू पर स्कैन (लागत = 0.00..18334.10 पंक्तियाँ = 1000010 चौड़ाई = 37) (वास्तविक समय = 0.006..57.836 पंक्तियाँ = 1000010 लूप = 1)
कुल रनटाइम: 328.746 एमएस
(5 पंक्तियाँ)

Sort Method: quicksort Memory: 102702kB - पूरी छंटनी रैम में की जाती है।

ज़िप कोड:

 CREATE INDEX ON foo(c1); EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1; 

जल्दी योजना
- foo_c1_idx का foo (लागत = 0.42..34327.57 पंक्तियों = 1000010 चौड़ाई = 37) का उपयोग करके सूचकांक स्कैन (वास्तविक समय = 0.023..126.076 पंक्तियों = 1000010 छोरों = 1)
कुल रनटाइम: 153.452 एमएस
(2 पंक्तियाँ)

क्रियाओं में से, केवल Index Scan ही रहता है, जिसने क्वेरी की गति को महत्वपूर्ण रूप से प्रभावित किया है।

आप LIMIT

पहले बनाए गए इंडेक्स को डिलीट करें।

 DROP INDEX foo_c2_idx1; EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo WHERE c2 LIKE 'ab%'; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..20834.12 पंक्तियों = 100 चौड़ाई = 37) (वास्तविक समय = 0.033..94.757 पंक्तियों = 3824 छोरों = 1)
फ़िल्टर: (c2 ~~ 'ab%' :: टेक्स्ट)
फ़िल्टर द्वारा निकाली गई पंक्तियाँ: 996186
बफ़र्स: साझा हिट = 8334
कुल रनटाइम: 94.924 एमएस
(5 पंक्तियाँ)

Seq Scan और Filter का उपयोग करने की उम्मीद है।

 EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo WHERE c2 LIKE 'ab%' LIMIT 10; 

जल्दी योजना
- सीमा (लागत = 0.00..2083.41 पंक्तियों = 10 चौड़ाई = 37) (वास्तविक समय = 0.037..0.607 पंक्तियों = 10 छोरों = 1)
बफ़र्स: साझा हिट = 26
-> फू (लागत = 0.00..20834.12 पंक्तियों = 100 चौड़ाई = 37) पर Seq स्कैन (वास्तविक समय = 0.031..0.599 पंक्तियों = 10 छोरों = 1)
फ़िल्टर: (c2 ~~ 'ab%' :: टेक्स्ट)
फ़िल्टर द्वारा निकाली गई पंक्तियाँ: 3053
बफ़र्स: साझा हिट = 26
कुल रनटाइम: 0.628 एमएस
(7 पंक्तियाँ)

Seq Scan तालिका पंक्तियों Seq Scan और Filter की स्थिति से तुलना करता है। जैसे ही 10 रिकॉर्ड संतुष्ट होते हैं कि स्थिति को संतुष्ट करते हैं, स्कैन समाप्त हो जाएगा। हमारे मामले में, परिणाम की 10 पंक्तियाँ प्राप्त करने के लिए, हमें पूरी तालिका नहीं पढ़नी थी, लेकिन केवल 3063 रिकॉर्ड थे, जिनमें से 3053 को खारिज कर दिया गया था ( Rows Removed by Filter )।
यही बात Index Scan साथ भी होती है।

शामिल हों

एक नई तालिका बनाएं, इसके लिए आंकड़े एकत्र करें।

 CREATE TABLE bar (c1 integer, c2 boolean); INSERT INTO bar SELECT i, i%2=1 FROM generate_series(1, 500000) AS i; ANALYZE bar; 

दो टेबल क्वेरी

 EXPLAIN (ANALYZE) SELECT * FROM foo JOIN bar ON foo.c1=bar.c1; 

जल्दी योजना
- हैश ज्वाइन (लागत = 13463.00..49297.22 पंक्तियाँ = 500000 चौड़ाई = 42) (वास्तविक समय = 87.441..907.555 पंक्तियाँ = 500010 छोर = 1)
हैश कांड: (foo.c1 = bar.c1)
-> फू (लागत = 0.00..18334.10 पंक्तियों = 1000010 चौड़ाई = 37) पर Seq स्कैन (वास्तविक समय = 0.008..67.951 पंक्तियों = 1000010 छोरों = 1)
-> हैश (लागत = 7213.00..7213.00 पंक्तियाँ = 500000 चौड़ाई = 5) (वास्तविक समय = 87.352..87.352 पंक्तियाँ = 500000 लूप = 1)
बाल्टी: 65536 बैच: 1 मेमोरी उपयोग: 18067kB
-> बार पर स्कैन स्कैन (लागत = 0.00..7213.00 पंक्तियाँ = 500000 चौड़ाई = 5) (वास्तविक समय = 0.007..33.233 पंक्तियाँ = 500000 लूप = 1)
कुल रनटाइम: 920.967 एमएस
(7 पंक्तियाँ)

सबसे पहले, bar (तालिका) स्कैन की जाती है ( Seq Scan )। इसकी प्रत्येक पंक्ति के लिए, एक हैश की गणना की जाती है।
फिर, Seq Scan टेबल foo स्कैन किया जाता है, और इस तालिका की प्रत्येक पंक्ति के लिए, एक हैश की गणना की जाती है, जिसकी तुलना Hash Cond की स्थिति द्वारा bar टेबल के हैश के साथ ( Hash Join ) से की जाती है। यदि कोई मिलान पाया जाता है, तो परिणामी स्ट्रिंग प्रदर्शित होती है, अन्यथा स्ट्रिंग को छोड़ दिया जाएगा।
मेज की bar हैश की मेजबानी करने के लिए स्मृति में 18067kB का उपयोग किया।

एक सूचकांक जोड़ें

 CREATE INDEX ON bar(c1); EXPLAIN (ANALYZE) SELECT * FROM foo JOIN bar ON foo.c1=bar.c1; 

जल्दी योजना
- मर्ज जॉइन (लागत = 1.69..39879.71 पंक्तियाँ = 500000 चौड़ाई = 42) (वास्तविक समय = 0.037..263.357 पंक्तियाँ = 500010 लूप = 1)
मर्ज कंड: (foo.c1 = bar.c1)
-> सूचकांक स्कैन foo_c1_idx का उपयोग कर foo (लागत = 0.42..34327.57 पंक्तियों = 1000010 चौड़ाई = 37) (वास्तविक समय = 0.019..58.920 पंक्तियों = 500011 लूप = 1)
-> बार पर बार_c1_idx (लागत = 0.42..15212.42 पंक्तियों = 500000 चौड़ाई = 5) का उपयोग करके सूचकांक स्कैन (वास्तविक समय = 0.008..71.719 पंक्तियाँ = 500010 छोर = 1)
कुल रनटाइम: 283.549 एमएस
(5 पंक्तियाँ)

Hash अब उपयोग में नहीं है। दोनों तालिकाओं के सूचकांक पर Merge Join और Index Scan एक प्रभावशाली प्रदर्शन को बढ़ावा देते हैं।

बाईं ओर:

 EXPLAIN (ANALYZE) SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1; 

जल्दी योजना
- हैश लेफ्ट जॉइन (लागत = 13463.00..49297.22 पंक्तियाँ = 1000010 चौड़ाई = 42) (वास्तविक समय = 82.682..926.331 पंक्तियाँ = 1000010 लूप = 1)
हैश कांड: (foo.c1 = bar.c1)
-> फू (लागत = 0.00..18334.10 पंक्तियों = 1000010 चौड़ाई = 37) पर सीक स्कैन (वास्तविक समय = 0.004..68.763 पंक्तियाँ = 1000010 लूप = 1)
-> हैश (लागत = 7213.00..7213.00 पंक्तियाँ = 500000 चौड़ाई = 5) (वास्तविक समय = 82.625..82.625 पंक्तियाँ = 500000 लूप = 1)
बाल्टी: 65536 बैच: 1 मेमोरी उपयोग: 18067kB
-> बार पर स्कैन स्कैन (लागत = 0.00..7213.00 पंक्तियाँ = 500000 चौड़ाई = 5) (वास्तविक समय = 0.003..31.890 पंक्तियाँ = 500000 लूप = 1)
कुल रनटाइम: 950.625 एमएस
(7 पंक्तियाँ)

Seq Scan ?
आइए देखें कि अगर हम Seq Scan को प्रतिबंधित करते हैं तो परिणाम क्या होंगे।

 SET enable_seqscan TO off; EXPLAIN (ANALYZE) SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1; 

जल्दी योजना
- मर्ज लेफ्ट जॉइन (लागत = 0.85..58290.02 पंक्तियाँ = 1000010 चौड़ाई = 42) (वास्तविक समय = 0.024..353.819 पंक्तियाँ = 1000010 लूप = 1)
मर्ज कंड: (foo.c1 = bar.c1)
-> सूचकांक स्कैन foo_c1_idx का उपयोग foo (लागत = 0.42..34327.57 पंक्तियों = 1000010 चौड़ाई = 37) (वास्तविक समय = 0.011..112.095 पंक्तियों = 1000010 छोरों = 1) पर करें
-> बार पर बार_c1_idx (लागत = 0.42..15212.42 पंक्तियों = 500000 चौड़ाई = 5) का उपयोग करके सूचकांक स्कैन (वास्तविक समय = 0.008..63.125 पंक्तियाँ = 500010 छोर = 1)
कुल रनटाइम: 378.603 एमएस
(5 पंक्तियाँ)

प्लानर के अनुसार, हैश का उपयोग करने की तुलना में इंडेक्स का उपयोग करना अधिक महंगा है। यह आवंटित स्मृति की पर्याप्त मात्रा में संभव है। याद रखें हमने work_mem ?
लेकिन, यदि स्मृति कम आपूर्ति में है, तो अनुसूचक अलग तरह से व्यवहार करेगा:

 SET work_mem TO '15MB'; SET enable_seqscan TO ON; EXPLAIN (ANALYZE) SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1; 

जल्दी योजना
- मर्ज लेफ्ट जॉइन (लागत = 0.85..58290.02 पंक्तियाँ = 1000010 चौड़ाई = 42) (वास्तविक समय = 0.014..376.395 पंक्तियाँ = 1000010 लूप = 1)
मर्ज कंड: (foo.c1 = bar.c1)
-> सूचकांक स्कैन foo_c1_idx1 का उपयोग foo (लागत = 0.42..34327.57 पंक्तियों = 1000010 चौड़ाई = 37) (वास्तविक समय = 0.005..124.698 पंक्तियों = 1000010 छोरों = 1) पर करें
-> पट्टी पर बार_c1_idx (लागत = 0.42..15212.42 पंक्तियों = 500000 चौड़ाई = 5) का उपयोग करके सूचकांक स्कैन (वास्तविक समय = 0.006..66.813 पंक्तियाँ = 500010 छोर = 1)
कुल रनटाइम: 401.990 एमएस
(5 पंक्तियाँ)

और निषिद्ध Index Scan साथ EXPLAIN आउटपुट कैसा दिखेगा?

 SET work_mem TO '15MB'; SET enable_indexscan TO off; EXPLAIN (ANALYZE) SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1; 

जल्दी योजना
- हैश लेफ्ट जॉइन (लागत = 15417.00..63831.18 पंक्तियाँ = 1000010 चौड़ाई = 42) (वास्तविक समय = 93.440..712.056 पंक्तियाँ = 1000010 लूप = 1)
हैश कांड: (foo.c1 = bar.c1)
-> फू (लागत = 0.00..18334.10 पंक्तियों = 1000010 चौड़ाई = 37) पर Seq स्कैन (वास्तविक समय = 0.008..65.901 पंक्तियाँ = 1000010 लूप = 1)
-> हैश (लागत = 7213.00..7213.00 पंक्तियाँ = 500000 चौड़ाई = 5) (वास्तविक समय = 93.308..93.308 पंक्तियाँ = 500000 लूप = 1)
बाल्टी: 65536 बैच: 2 मेमोरी उपयोग: 9045kB
-> बार पर स्कैन स्कैन (लागत = 0.00..7213.00 पंक्तियाँ = 500000 चौड़ाई = 5) (वास्तविक समय = 0.007..33.718 पंक्तियाँ = 500000 लूप = 1)
कुल रनटाइम: 736.726 एमएस
(7 पंक्तियाँ)

cost स्पष्ट रूप से बढ़ गई है। Batches: 2 में कारण Batches: 2 । पूरा हैश मेमोरी में फिट नहीं था, इसे प्रत्येक के 9045kB के 2 पैकेट में विभाजित किया जाना था।

यहाँ मैं फिर से गुरु से मदद माँगता हूँ। बताएं कि क्यों Merge Left Join LEFT JOIN और पर्याप्त work_mem , Merge Left Join उपयोग करना Hash Left Join तुलना Merge Left Join अधिक महंगा है?

मैं आज यहीं रुकूंगा।

युपीडी।
PostgreSQL इंडेक्स के बारे में कई उपयोगी बातें ओलेग बार्टुनोव और अलेक्जेंडर कोरोटकोव द्वारा बताई गई थीं।

मैं यहाँ PostgreSQL Indexes के नवीनतम लेखों का लिंक लाऊंगा, भाग 2 , भाग 3 । बहुत कुछ स्पष्ट किया गया है।