आप एक धीमे PostgreSQL क्वेरी का निदान और अनुकूलन (Optimize) कैसे करते हैं?

प्रॉम्प्ट और लागू संदर्भ

orders टेबल में 200 मिलियन पंक्तियाँ हैं और यह प्रति सेकंड 3,000 इंसर्ट या स्थिति अपडेट संभालती है। सभी ऑर्डर्स में से लगभग 2% pending हैं, हालांकि यह हिस्सा किरायेदारों (tenants) के बीच काफी भिन्न होता है। एक ऑपरेशन्स डैशबोर्ड प्रति सेकंड 40 बार निम्नलिखित क्वेरी चलाता है। यह पिछले 30 दिनों के भीतर एक टेनेंट के लिए 50 सबसे हालिया पेंडिंग ऑर्डर्स मांगता है:

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

टेबल में पहले से ही दो सिंगल-कॉलम B-tree इंडेक्स, orders(tenant_id) और orders(created_at) मौजूद हैं। एप्लिकेशन मॉनिटरिंग दर्शाती है कि क्वेरी का p95 120 ms से बढ़कर 2.8 सेकंड हो गया, जबकि PostgreSQL CPU, मेमोरी और कनेक्शन संख्या क्षमता से नीचे बनी हुई है। प्रोडक्शन-स्केल रेप्लिका पर कैप्चर किया गया एक प्लान 8,000 पंक्तियों का अनुमान लगाता है, लेकिन सॉर्टिंग से पहले 420,000 पंक्तियाँ उत्पन्न करता है, लगभग 120,000 शेयर्ड बफ़र्स को छूता है, और अंत में 50 पंक्तियाँ लौटाने के लिए टॉप-N सॉर्ट लागू करता है।

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

साक्षात्कारकर्ता क्या मूल्यांकन करता है

पहला संकेत SQL परिवर्तनों से पहले वर्कलोड की पुष्टि करना है। एक मजबूत उत्तर "सबसे धीमे एकल निष्पादन" को "सबसे बड़ी संचयी लागत" से अलग करता है। 1,000 कॉल्स प्रति सेकंड पर 80 ms का औसत लेने वाली क्वेरी कभी-कभार आने वाली पांच-सेकंड की क्वेरी से पहले ध्यान देने योग्य हो सकती है। pg_stat_statements कॉल्स, कुल निष्पादन समय और औसत निष्पादन समय प्रदान करता है। एप्लिकेशन मॉनिटरिंग या ट्रेसिंग को अभी भी p95 और टेनेंट-विशिष्ट पर्सेंटाइल प्रदान करने चाहिए।

दूसरा संकेत एक निष्पादन योजना को एक कारण-शृंखला के रूप में पढ़ना है। प्रासंगिक साक्ष्यों में 8,000-बनाम-420,000 कार्डिनैलिटी अंतर, स्कैन नोड्स द्वारा उत्सर्जित पंक्तियाँ, प्रत्येक नोड का loops, बफ़र गतिविधि, सॉर्ट विधि, और क्या कोई प्रेडिकेट इंडेक्स स्थिति में या पोस्ट-स्कैन फ़िल्टर में दिखाई देता है, शामिल हैं। Seq Scan देखना या यह देखना कि इंडेक्स का उपयोग किया गया था, यह स्थापित नहीं करता है कि कोई प्लान अच्छा है या नहीं।

तीसरा संकेत क्वेरी के आकार से कुंजी का क्रम (key order) प्राप्त करना है। tenant_id एक समानता (equality) प्रेडिकेट है। created_at एक रेंज प्रेडिकेट और अनुरोधित क्रम दोनों है। status='pending' एक निश्चित, दुर्लभ व्यावसायिक स्थिति है। एक उपयुक्त इंडेक्स को एक टेनेंट की पेंडिंग रेंज में प्रवेश करना चाहिए, टाइमस्टैम्प क्रम में पढ़ना चाहिए, और 50 पंक्तियाँ मिलते ही रुक जाना चाहिए।

अंत में, साक्षात्कारकर्ता सत्यापन और रोलआउट अनुशासन की तलाश करता है। एक इंडेक्स स्टोरेज और बिल्ड I/O की खपत करता है, जबकि इंसर्ट और स्थिति संक्रमणों (status transitions) में काम बढ़ाता है। एक संपूर्ण उत्तर प्रोडक्शन-स्केल डेटा, कोल्ड और वार्म कैश, विभिन्न टेनेंट आकारों, समवर्ती राइट्स और स्पष्ट रोलबैक सीमाओं पर योजनाओं की तुलना करता है। एक तेज़ स्थानीय निष्पादन पर्याप्त साक्ष्य नहीं है।

उत्तर देने से पहले स्पष्ट करने योग्य प्रश्न

• किस लेटेंसी माप में गिरावट आई है? प्रति-टेनेंट p95, वैश्विक p95, औसत लेटेंसी और कुल डेटाबेस समय विभिन्न प्राथमिकताओं को दर्शाते हैं। स्थापित करें कि गिरावट कब शुरू हुई और क्या यह डेटा वृद्धि, पैरामीटर वितरण, एक परिनियोजन (deployment), या सांख्यिकी परिवर्तनों के साथ संरेखित है।
• पेंडिंग-ऑर्डर दर और टेनेंट वितरण क्या हैं? एक आंशिक (partial) इंडेक्स आकर्षक होता है यदि pending पंक्तियों का 1% से 2% बना रहता है। यदि आधी टेबल पेंडिंग है तो इसका आकार लाभ सिकुड़ जाता है। एक औसत बहुत बड़े और छोटे किरायेदारों के बीच के अंतर को भी छुपाता है।
• क्या क्वेरी में हमेशा लिटरल status='pending' शामिल होता है? एक आंशिक इंडेक्स केवल तभी उपयोग करने योग्य होता है जब प्लानर यह साबित कर सके कि क्वेरी की स्थिति इंडेक्स प्रेडिकेट को लागू करती है। एक सामान्य स्थिति पैरामीटर उस प्रमाण को रोक सकता है।
• किन कॉलमों और कंसिस्टेंसी गारंटियों की आवश्यकता है? बड़े टेक्स्ट, JSON, या दस जॉइन की गई टेबल्स लौटाने से कवरिंग इंडेक्स जल्दी से बड़ा (bloat) हो जाएगा। पुष्टि करें कि इस सूची एंडपॉइंट को वास्तव में किन कॉलमों की आवश्यकता है।
• टेबल कितनी राइट-हैवी है, और किस प्रकार के रोलआउट की अनुमति है? प्रति सेकंड 3,000 राइट्स पर, इंडेक्स चौड़ाई और संक्रमण लागत को मापा जाना चाहिए। प्रोडक्शन को CREATE INDEX CONCURRENTLY की आवश्यकता हो सकती है, साथ ही एक लंबी बिल्ड विंडो, अतिरिक्त स्कैन और विफलता के बाद अमान्य इंडेक्स को साफ़ करने की प्रक्रिया की आवश्यकता हो सकती है।
• क्या वास्तविक प्लान प्रोडक्शन-स्केल रेप्लिका पर चल सकता है? EXPLAIN ANALYZE स्टेटमेंट को निष्पादित करता है। एक SELECT भी पर्याप्त लोड उत्पन्न कर सकता है, जबकि डेटा बदलने वाले स्टेटमेंट अपने साइड इफेक्ट्स निष्पादित करते हैं। पहले रेप्लिका, सीमित पैरामीटर्स या सादे EXPLAIN का उपयोग करें।

30-सेकंड उत्तर रूपरेखा

“मैं पहले एप्लिकेशन p95 को pg_stat_statements कॉल्स, कुल डेटाबेस समय और धीमे किरायेदारों के साथ सहसंबंधित करूंगा, जबकि लॉक वेट और बाहरी निर्भरताओं को खारिज करूंगा। फिर मैं प्रोडक्शन-स्केल रेप्लिका पर प्रतिनिधि मापदंडों के साथ EXPLAIN (ANALYZE, BUFFERS) चलाऊंगा और अनुमानित बनाम वास्तविक पंक्तियों, लूप्स, बफ़र्स और सॉर्ट नोड का निरीक्षण करूंगा। यहाँ, दो सिंगल-कॉलम इंडेक्स सॉर्टिंग से पहले अभी भी 420,000 उम्मीदवार उत्पन्न करते हैं। क्योंकि क्वेरी हमेशा एक दुर्लभ पेंडिंग स्थिति को लक्षित करती है, मैं (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending' पर एक आंशिक कवरिंग इंडेक्स का परीक्षण करूंगा, जो क्रम में पहली 50 पंक्तियों को पढ़ सकता है। यदि स्थिति को पैरामीटरयुक्त किया जाना चाहिए, तो मैं एक पूर्ण (tenant_id, status, created_at DESC) इंडेक्स की तुलना करूंगा। मैं रोलबैक सीमाओं के साथ समवर्ती रूप से निर्माण करने से पहले टेनेंट आकारों, कोल्ड और वार्म कैश, और समवर्ती राइट्स में p95, बफ़र कार्य, इंडेक्स आकार और राइट लेटेंसी को मान्य करूंगा।”

चरण-दर-चरण गहन विश्लेषण

चरण 1: वास्तविक वर्कलोड से प्राथमिकता तय करें

रूट, टेनेंट, पैरामीटर रेंज और एप्लिकेशन p95 को एक सामान्यीकृत डेटाबेस क्वेरी में मैप करें। यदि pg_stat_statements सक्षम है, तो संचयी संसाधन खपत से शुरुआत करें:

SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time लगातार कॉल्स के माध्यम से संचित डेटाबेस समय को खोजता है, mean_exec_time महंगे व्यक्तिगत निष्पादन को उजागर करता है, और calls मल्टीप्लायर दिखाता है। दृश्य p95 को उजागर नहीं करता है या यह स्पष्ट नहीं करता है कि कोई विशेष टेनेंट या पैरामीटर धीमा क्यों है, इसलिए एप्लिकेशन-साइड पर्सेंटाइल और पैरामीटर कोहोर्ट्स को बनाए रखें। यदि लेटेंसी मुख्य रूप से लॉक वेटिंग, कनेक्शन कतार, नेटवर्किंग, या डाउनस्ट्रीम कॉल है, तो केवल क्वेरी प्लान बदलने से एंड-टू-एंड लेटेंसी ठीक नहीं होगी।

चरण 2: वास्तविक निष्पादन साक्ष्य सुरक्षित रूप से एकत्र करें

पहले सादे EXPLAIN के साथ आकार का निरीक्षण करें। फिर, प्रोडक्शन-स्केल रेप्लिका या नियंत्रित वातावरण पर चलाएं:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

पर्याप्त वास्तविक समय वाले सबसे गहरे नोड्स से ऊपर की ओर पढ़ें। actual rows × loops को नोड के कुल कार्य के भाग के रूप में मानें। Buffers: shared read उन ब्लॉकों को रिकॉर्ड करता है जिन्हें स्टोरेज से पढ़ना पड़ा था; shared hit का अर्थ है कि ब्लॉक पहले से ही शेयर्ड बफ़र्स में थे, लेकिन वे हिट्स अभी भी CPU और मेमोरी बैंडविड्थ की खपत करते हैं। एक डिस्क-समर्थित सॉर्ट एक बाहरी सॉर्ट और अस्थायी ब्लॉक I/O की रिपोर्ट करता है, जो इसके इनपुट आकार और मेमोरी बजट की जांच को प्रेरित करता है।

8,000 पंक्तियों के अनुमान बनाम 420,000 वास्तविक पंक्तियों में 52.5 गुना त्रुटि है। सांख्यिकी पुरानी हो सकती है, या सिंगल-कॉलम सांख्यिकी tenant_id और status के बीच सहसंबंध का प्रतिनिधित्व करने में विफल हो सकती है। उचित रूप से स्कोप किया गया ANALYZE चलाएं और फिर से मापें। यदि स्थिर सहसंबंध योजना को भौतिक रूप से प्रभावित करता है, तो उन कॉलमों के लिए विस्तारित सांख्यिकी (extended statistics) का परीक्षण करें। विस्तारित सांख्यिकी संग्रहण और योजना लागत वहन करती है, इसलिए उन्हें केवल उन दृढ़ता से संबंधित कॉलमों के लिए बनाएं जो एक महत्वपूर्ण अनुमान में सुधार करते हैं।

चरण 3: क्वेरी आकार से इंडेक्स प्राप्त करें

प्लानर मौजूदा सिंगल-कॉलम इंडेक्स को BitmapAnd के साथ जोड़ सकता है, लेकिन एक बिटमैप परिणाम एक B-tree के क्रम को बनाए नहीं रखता है। यह अभी भी कई हीप पेजों पर जा सकता है और सॉर्ट कर सकता है। वैकल्पिक रूप से, प्लानर एक इंडेक्स चुन सकता है और बाद में दूसरे प्रेडिकेट को फ़िल्टर कर सकता है। दोनों इंडेक्स होने से केवल उम्मीदवार पथ बनते हैं; यह WHERE + ORDER BY + LIMIT के लिए उपयुक्त पथ तैयार नहीं करता है।

एक निश्चित और दुर्लभ पेंडिंग स्थिति के लिए, पहले एक आंशिक इंडेक्स की तुलना करें:

CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'pending';

CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);

पहला इंडेक्स केवल पेंडिंग ऑर्डर्स संग्रहीत करता है और इसलिए बताए गए वितरण के तहत छोटा होना चाहिए। टेनेंट समानता का पता लगाने के बाद, created_at 30-दिन की सीमा को बाधित करता है और अवरोही क्रम प्रदान करता है, जिससे स्कैन 50 पंक्तियों के बाद रुक सकता है। id और total_cents, INCLUDE में पेलोड कॉलम हैं; वे खोज या क्रमबद्ध करने में भाग नहीं लेते हैं और केवल इंडेक्स-ओनली रीड को संभव बनाते हैं।

पूर्ण मल्टी-कॉलम इंडेक्स एक पैरामीटरयुक्त स्थिति या कई स्थितियों के लिए उपयुक्त है जो समान क्वेरी पैटर्न का उपयोग करते हैं। अग्रणी B-tree समानता कॉलम समय सीमा और क्रमबद्धता से पहले सीमा को संकीर्ण करते हैं। "हमेशा सबसे चयनात्मक कॉलम को पहले रखें" बहुत अपरिष्कृत है; समानता, सीमा, क्रमबद्धता और वास्तविक क्वेरी टेम्प्लेट में पुन: उपयोग एक साथ कुंजी क्रम निर्धारित करते हैं।

चरण 4: आंशिक और कवरिंग इंडेक्स की सीमाओं को स्पष्ट करें

एक आंशिक इंडेक्स केवल तभी उपयोग करने योग्य होता है जब प्लानर यह साबित कर सके कि क्वेरी स्थिति में status='pending' शामिल है। status = $2 के रूप में लिखे गए एक सामान्य तैयार स्टेटमेंट (prepared statement) में, वह पैरामीटर हर संभावित मान के लिए प्रेडिकेट को लागू नहीं कर सकता है, इसलिए प्लानर आंशिक इंडेक्स को अनदेखा कर सकता है। एक समर्पित ऑपरेशन्स क्वेरी लिटरल को सुरक्षित रख सकती है, या डिज़ाइन पूर्ण मल्टी-कॉलम इंडेक्स का उपयोग कर सकता है। इंडेक्स परिभाषा से अनुमान लगाने के बजाय वास्तविक क्वेरी टेम्प्लेट के साथ निर्णय की पुष्टि करें।

INCLUDE प्रत्येक निष्पादन पर इंडेक्स ओनली स्कैन की गारंटी नहीं देता है। PostgreSQL को अभी भी MVCC दृश्यता सत्यापित करनी होगी। जब किसी हीप पेज में ऑल-विजिबल बिट की कमी होती है, तो स्कैन हीप पर जाता है। एक हॉट टेबल पर बार-बार इंसर्ट और स्थिति परिवर्तन इस तरह की विज़िट को अधिक संभावित बनाते हैं। यदि प्लान अभी भी कई Heap Fetches की रिपोर्ट करता है, तो पेलोड कॉलम के बिना एक संकीर्ण इंडेक्स की तुलना करें। विस्तृत इंडेक्स डिस्क और कैश के उपयोग को भी बढ़ाते हैं और प्रत्येक प्रभावित राइट में रखरखाव का काम जोड़ते हैं।

चरण 5: लाभ और लागत को मान्य करें

समान प्रतिनिधि मापदंडों का उपयोग करके पहले और बाद की योजनाओं की तुलना करें: बहुत बड़े, औसत और छोटे किरायेदार; कई पेंडिंग पंक्तियों वाले और लगभग बिना पंक्तियों वाले किरायेदार; कोल्ड कैश और वार्म कैश। लेटेंसी वितरण, वास्तविक पंक्तियाँ, बफ़र्स, सॉर्ट व्यवहार, अस्थायी I/O, Heap Fetches, और इंडेक्स आकार रिकॉर्ड करें। एकल बीता हुआ समय कैश और समवर्तीता के प्रति संवेदनशील होता है, जबकि प्लान कार्य बताता है कि परिणाम क्यों बदला।

फिर प्रोडक्शन अनुपात पर इंसर्ट और pending → paid ट्रांज़िशन का लोड-टेस्ट करें। एक पूर्ण ऑर्डर आंशिक इंडेक्स से एक प्रविष्टि हटाता है; पूर्ण इंडेक्स अपनी स्थिति कुंजी को अपडेट करता है। दोनों राइट कार्य थोपते हैं। स्वीकृति मानदंड के लिए लक्ष्य के भीतर रीड p95 और कोई भौतिक p99 रिग्रेशन नहीं, उम्मीदवार पंक्तियों और शेयर्ड-ब्लॉक कार्य में एक बड़ी कमी, और राइट p95, WAL वॉल्यूम, स्टोरेज और प्रतिकृति अंतराल (replication lag) बजट के भीतर होना आवश्यक हो सकता है।

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

उच्च-गुणवत्ता वाला नमूना उत्तर

“मैं पहले यह स्थापित करूंगा कि यह SQL प्राथमिकता का हकदार है। एप्लिकेशन मॉनिटरिंग मुझे p95 और धीमे किरायेदारों का डेटा देती है, जबकि pg_stat_statements कॉल्स, कुल निष्पादन समय और औसत निष्पादन समय देता है। यदि यह प्रति सेकंड 40 बार चलता है और संचयी डेटाबेस समय में उच्च स्थान रखता है, तो मैं वास्तविक क्वेरी टेम्प्लेट और प्रतिनिधि किरायेदारों को कैप्चर करूंगा, फिर प्रोडक्शन-स्केल रेप्लिका पर EXPLAIN (ANALYZE, BUFFERS) चलाऊंगा।

इस प्लान में केंद्रीय समस्या वाइड स्कैन है: प्लानर 8,000 पंक्तियों का अनुमान लगाता है, 420,000 पंक्तियाँ वास्तव में टॉप-N सॉर्ट तक पहुँचती हैं, और क्वेरी लगभग 120,000 बफ़र्स को छूती है। सिंगल-कॉलम इंडेक्स फ़िल्टर को जोड़ सकते हैं, लेकिन वे सीधे tenant_id + pending + created_at DESC के लिए एक व्यवस्थित सीमा नहीं बनाते हैं। मैं सांख्यिकी को रीफ्रेश करूंगा और फिर से मापूंगा। यदि टेनेंट और स्थिति सहसंबंध अनुमान त्रुटि का कारण बनते रहते हैं, तो मैं विस्तारित सांख्यिकी का परीक्षण करूंगा।

चूंकि ऑपरेशन्स क्वेरी हमेशा दुर्लभ पेंडिंग स्थिति का अनुरोध करती है, इसलिए मैं (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending' पर एक आंशिक इंडेक्स का परीक्षण करूंगा। यह अनुक्रमित जनसंख्या को सीमित करता है, एक टेनेंट की क्रमित समय सीमा में प्रवेश करता है, और 50 पंक्तियों के बाद रुक सकता है। यदि एप्लिकेशन स्थिति को पैरामीटरयुक्त करता है और कई मानों की क्वेरी करता है, तो मैं पूर्ण (tenant_id, status, created_at DESC) इंडेक्स की तुलना करूंगा। INCLUDE केवल एक संभावित इंडेक्स ओनली स्कैन को सक्षम बनाता है; हॉट पेजों को अभी भी हीप विज़िट की आवश्यकता हो सकती है, इसलिए मैं Heap Fetches का निरीक्षण करूंगा।

सत्यापन बड़े, मध्यम और छोटे किरायेदारों, कोल्ड और वार्म कैश, और समवर्ती राइट्स को कवर करेगा। मैं p95 और p99, उम्मीदवार पंक्तियों, बफ़र्स, अस्थायी I/O, इंडेक्स आकार, WAL, राइट लेटेंसी और रेप्लिकेशन अंतराल की तुलना करूंगा। मैं प्रोडक्शन में समवर्ती रूप से निर्माण करूंगा, पुष्टि करूंगा कि वास्तविक टेम्प्लेट नए प्लान का उपयोग करता है, और एक पूर्ण पीक चक्र देखूंगा। यदि रीड लाभ कमजोर है या राइट पाथ बजट से अधिक है, तो मैं अधिक हार्डवेयर के पीछे एक अस्पष्ट प्लान को छिपाने के बजाय पाथ वापस ले लूंगा और नए इंडेक्स को ड्रॉप कर दूंगा।”

सामान्य गलतियाँ

• SQL के धीमे दिखते ही एक इंडेक्स जोड़ना → आवृत्ति, पैरामीटर और प्रतीक्षा प्रकार अज्ञात रहते हैं, इसलिए टीम कम प्राथमिकता वाली क्वेरी को ऑप्टिमाइज़ कर सकती है → पहले एप्लिकेशन पर्सेंटाइल, pg_stat_statements और वास्तविक मापदंडों को सहसंबंधित करें।
• प्रत्येक Seq Scan को एक दोष मानना → एक अनुक्रमिक स्कैन (sequential scan) एक छोटी टेबल या इसके एक बड़े हिस्से को वापस करने वाली क्वेरी के लिए सस्ता हो सकता है → विकल्पों के विरुद्ध वास्तविक पंक्तियों, बफ़र्स और कुल लागत की तुलना करें।
• केवल यह जाँचना कि क्या प्लान Index Scan कहता है → एक इंडेक्स स्कैन अभी भी सैकड़ों हजारों प्रविष्टियों को पढ़ सकता है और बार-बार हीप पर जा सकता है → actual rows × loops, Filter, Buffers, और Heap Fetches का निरीक्षण करें।
• अनुमानित बनाम वास्तविक पंक्ति अंतरालों की अनदेखी करना → गलत कार्डिनैलिटी खराब जॉइन्स, स्कैन और सॉर्ट का कारण बन सकती है → सांख्यिकी रीफ्रेश करें और स्थिर सहसंबद्ध कॉलमों के लिए विस्तारित सांख्यिकी का परीक्षण करें।
• यह मान लेना कि एकाधिक सिंगल-कॉलम इंडेक्स एक मल्टी-कॉलम इंडेक्स के बराबर हैं → बिटमैप संयोजन आमतौर पर आवश्यक क्रम खो देते हैं और कई हीप पेजों पर जा सकते हैं → समानता, सीमा, क्रमबद्धता और LIMIT से एक कुंजी प्राप्त करें।
• प्रत्येक लौटाए गए कॉलम को INCLUDE में रखना → इंडेक्स ब्लोट कैश दक्षता को कम करता है और राइट्स को बढ़ाता है → केवल उच्च-मूल्य वाली क्वेरी द्वारा आवश्यक संकीर्ण कॉलमों को कवर करें।
• क्वेरी टेम्प्लेट का परीक्षण किए बिना आंशिक इंडेक्स बनाना → एक पैरामीटरयुक्त प्रेडिकेट प्लानिंग के समय इंडेक्स प्रेडिकेट को लागू नहीं कर सकता है → प्रोडक्शन में उपयोग किए जाने वाले उसी तैयार रूप को EXPLAIN करें।
• मनमाने प्राथमिक-डेटाबेस SQL पर EXPLAIN ANALYZE चलाना → यह स्टेटमेंट को निष्पादित करता है; एक भारी रीड लोड बनाता है और एक राइट साइड इफेक्ट्स निष्पादित करता है → पहले सादे EXPLAIN का उपयोग करें और एक रेप्लिका पर या एक नियंत्रित ट्रांजेक्शन के अंदर वास्तविक साक्ष्य प्राप्त करें।
• एक ऐसे रन की रिपोर्ट करना जो 2.8 सेकंड से गिरकर कम संख्या पर आ गया → कैश, पैरामीटर और समवर्तीता एक आकस्मिक जीत पैदा कर सकते हैं → वितरण, प्लान कार्य और एक पूर्ण पीक चक्र की तुलना करें।

अनुवर्ती प्रश्न और उत्तर

अनुवर्ती 1: एक इंडेक्स स्कैन की तुलना में एक अनुक्रमिक स्कैन तेज़ क्यों हो सकता है?

जब कोई क्वेरी किसी टेबल के एक बड़े हिस्से को पढ़ती है, तो अनुक्रमिक पहुंच (sequential access) इंडेक्स को पार करने और बिखरे हुए हीप पेजों को लाने में शामिल अधिकांश यादृच्छिक पहुंच (random access) से बचती है। एक छोटी टेबल केवल कुछ पेजों पर आ सकती है, जिससे डायरेक्ट स्कैन भी सस्ता हो जाता है। नोड नाम द्वारा प्लान का स्कोर करने के बजाय वास्तविक डेटा पर बफ़र्स और कुल बीते समय की तुलना करें।

अनुवर्ती 2: अलग-अलग (tenant_id) और (created_at) इंडेक्स अपर्याप्त क्यों हैं?

प्लानर एक इंडेक्स चुन सकता है और बाद में फ़िल्टर कर सकता है या दोनों को BitmapAnd के साथ जोड़ सकता है। एक बिटमैप उम्मीदवार टपल स्थानों को एकत्र करता है और created_at के B-tree क्रम को सुरक्षित नहीं रखता है, इसलिए यह आमतौर पर कई हीप पेजों को पढ़ता है और फिर सॉर्ट करता है। मल्टी-कॉलम इंडेक्स एक व्यवस्थित एक्सेस पथ पर टेनेंट समानता, समय सीमा और क्रमबद्धता रखता है, जिससे LIMIT 50 जल्दी रुक सकता है।

अनुवर्ती 3: PostgreSQL कभी आंशिक इंडेक्स का उपयोग क्यों नहीं कर सकता है?

प्लानर को योजना बनाते समय यह पहचानना चाहिए कि क्वेरी स्थिति इंडेक्स प्रेडिकेट को लागू करती है। status='pending' सीधे मेल खाता है; status=$2 प्रत्येक पैरामीटर के लिए मैच का वादा नहीं कर सकता है। एक अलग तरीके से लिखा गया एक्सप्रेशन, बदला हुआ डेटा वितरण, या एक लागत अनुमान जो हीप एक्सेस को महंगा दिखाता है, वह भी एक अन्य पथ का चयन कर सकता है। वास्तविक तैयार टेम्प्लेट को EXPLAIN करें, और जब स्थिति सामान्य रहनी चाहिए तो पूर्ण मल्टी-कॉलम इंडेक्स का उपयोग करें।

अनुवर्ती 4: क्या होगा यदि ANALYZE के बाद भी अनुमान 50 गुना गलत रहे?

नमूनाकरण कवरेज (sampling coverage), प्रति-कॉलम सांख्यिकी लक्ष्य, और क्या डेटा अचानक बदल गया है, इसकी जाँच करें। यदि tenant_id और status दृढ़ता से सहसंबद्ध हैं, तो सिंगल-कॉलम सांख्यिकी प्रेडिकेट्स को स्वतंत्र मानती है। उस कॉलम समूह के लिए निर्भरता या MCV विस्तारित सांख्यिकी का परीक्षण करें। विस्तारित सांख्यिकी अनुमान में सुधार करती है; वे एक अनुपलब्ध एक्सेस पथ नहीं बनाते हैं, इसलिए इंडेक्स और क्वेरी आकार को अलग से सत्यापित करें।

अनुवर्ती 5: रीड्स में सुधार होता है, लेकिन राइट p95 बढ़ता है। आप कैसे निर्णय लेते हैं?

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