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

अनुरोध में इतना समय क्यों लगा? अनुक्रमित का उपयोग क्यों नहीं किया जाता है?
शायद सभी ने PostgreSQL में EXPLAIN के बारे में सुना है। लेकिन ऐसे कई नहीं हैं जो समझते हैं कि इसका उपयोग कैसे करना है। खुद लंबे समय तक पाठ्यपुस्तक को समझने के लिए सुलभ नहीं पाया जा सकता (खराब खोज?)।
मुझे उम्मीद है कि यह लेख उन लोगों की मदद करेगा जो इस अद्भुत उपकरण से निपटना चाहते हैं।

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

इतना डरावना नरक नहीं

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

हमारे कार्य:

• EXPLAIN कमांड के आउटपुट को पढ़ना और समझना सीखें
• क्वेरी को निष्पादित करते समय PostgreSQL में क्या होता है, इसे समझें

पहला कदम

हम एक मिलियन-लाइन टेस्ट पैटर्न के साथ बिल्लियों पर प्रशिक्षण देंगे

CREATE TABLE foo (c1 integer, c2 text); INSERT INTO foo SELECT i, md5(random()::text) FROM generate_series(1, 1000000) AS i; 

आइए डेटा पढ़ने की कोशिश करते हैं

 EXPLAIN SELECT * FROM foo; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..18334.00 पंक्तियाँ = 1,000,000 चौड़ाई = 37)
(1 पंक्ति)

तालिका से डेटा पढ़ना कई तरीकों से किया जा सकता है। हमारे मामले में, EXPLAIN की रिपोर्ट है कि Seq Scan का उपयोग किया जा रहा है - अनुक्रमिक, ब्लॉक द्वारा ब्लॉक, foo तालिका से डेटा पढ़ना।
cost क्या है? यह समय नहीं है, लेकिन एक वैक्यूम अवधारणा में कुछ प्रकार का गोलाकार है, जिसे ऑपरेशन की लागत का आकलन करने के लिए डिज़ाइन किया गया है। 0.00 का पहला मान पहली पंक्ति प्राप्त करने की लागत है। दूसरी - 18334.00 - सभी लाइनों को प्राप्त करने की लागत।
rows Seq Scan ऑपरेशन करते समय दी गई पंक्तियों की अनुमानित संख्या है। यह मान शेड्यूलर द्वारा लौटाया जाता है। मेरे मामले में, यह तालिका में वास्तविक संख्या से मेल खाती है।
width - बाइट्स में एक पंक्ति का औसत आकार।
10 पंक्तियों को जोड़ने का प्रयास करते हैं।

 INSERT INTO foo SELECT i, md5(random()::text) FROM generate_series(1, 10) AS i; EXPLAIN SELECT * FROM foo; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..18334.00 पंक्तियाँ = 1,000,000 चौड़ाई = 37)
(1 पंक्ति)

rows का मूल्य नहीं बदला है। मेज पर आँकड़े पुराने हैं। आंकड़ों को अपडेट करने के लिए, ANALYZE कमांड को कॉल करें।

 ANALYZE foo; EXPLAIN SELECT * FROM foo; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..18334.10 पंक्तियाँ = 1000010 चौड़ाई = 37)
(1 पंक्ति)

अब rows की सही संख्या प्रदर्शित करता है।

ANALYZE करते समय क्या होता है?

• तालिका पंक्तियों की एक निश्चित संख्या यादृच्छिक पर चुनी जाती है।
• तालिका में प्रत्येक कॉलम के लिए आंकड़े एकत्र किए गए हैं:

ANALYZE कितनी लाइनें पढ़ेगा यह default_statistics_target पैरामीटर पर निर्भर करता है।

वास्तविक डेटा

EXPLAIN कमांड के आउटपुट में हमने जो कुछ भी ऊपर देखा वह सिर्फ शेड्यूलर की अपेक्षाएं हैं। आइए वास्तविक डेटा पर परिणामों के साथ उनकी तुलना करने का प्रयास करें। हम EXPLAIN (ANALYZE) उपयोग करते हैं।

 EXPLAIN (ANALYZE) SELECT * FROM foo; 

जल्दी योजना
- foo पर Seq स्कैन (लागत = 0.00..18334.10 पंक्तियाँ = 1000010 चौड़ाई = 37) (वास्तविक समय = 0.012..61.524 पंक्तियाँ = 1000010 लूप = 1)
कुल रनटाइम: 90.944 एमएस
(2 पंक्तियाँ)

इस तरह के अनुरोध को वास्तविक रूप से निष्पादित किया जाएगा। इसलिए यदि आप INSERT, DELETE या UPDATE के लिए EXPLAIN (ANALYZE) करते हैं, तो आपका डेटा बदल जाएगा। सावधान! ऐसे मामलों में, ROLLBACK कमांड का उपयोग करें।
कमांड जानकारी का आउटपुट जोड़ा गया है।
actual time - मिलीसेकंड में वास्तविक समय क्रमशः पहली पंक्ति और सभी पंक्तियों को प्राप्त करने के लिए बिताया गया।
rows - Seq Scan द्वारा प्राप्त पंक्तियों की वास्तविक संख्या।
loops - कितनी बार ऑपरेशन Seq Scan करना पड़ा।
Total runtime - अनुरोध का कुल रनटाइम।

अधिक ...

पहले भाग के लिए, शायद पर्याप्त है।
भाग २
भाग ३