लेख का मुख्य भाग चुनने से केवल-इंडेक्स स्कैन संभव नहीं रह सकता: PostgreSQL को ऐसा कॉलम प्राप्त करना पड़ता है जो इंडेक्स में मौजूद नहीं है। यह गाइड दिखाती है कि Mac पर इस बदलाव का निदान कैसे करें और Node.js में इसका प्रभाव कैसे मापें। केवल-इंडेक्स स्कैन की आवश्यकताएँ।

चुने गए कॉलम बदलने से योजना क्यों बदलती है?

एक ऐसी लेख तालिका पर विचार करें जिसमें id, category और body हों, साथ ही (category, id) पर B-tree इंडेक्स हो। श्रेणी के आधार पर ID चुनने वाली क्वेरी अपना फ़िल्टर मान और परिणाम, दोनों उसी इंडेक्स से प्राप्त कर सकती है। body जोड़ने से केवल-इंडेक्स स्कैन के लिए वह इंडेक्स अपर्याप्त हो जाता है। PostgreSQL इंडेक्स तालिका के मुख्य स्टोरेज से अलग होते हैं, जिसे हीप कहा जाता है; सामान्य इंडेक्स स्कैन तालिका का डेटा भी प्राप्त करता है। केवल-इंडेक्स स्कैन और कवरिंग इंडेक्स।

अब प्लानर आवश्यक तालिका पंक्तियाँ प्राप्त करने के तरीकों की तुलना करता है। वह Index Scan बनाए रख सकता है, Bitmap Index Scan को Bitmap Heap Scan के साथ जोड़ सकता है, या Seq Scan चुन सकता है। बिटमैप स्कैन हीप तक पहुँच को भौतिक स्थान के अनुसार समूहित करते हैं। कई बिखरी हुई पंक्तियाँ प्राप्त करते समय, उन्हें अलग-अलग लाने की तुलना में पूरी तालिका स्कैन करना सस्ता हो सकता है। बिटमैप योजना फिर भी इंडेक्स का उपयोग करती है, भले ही उसका शीर्ष स्कैन नोड हीप का उल्लेख करे। EXPLAIN का उपयोग।

मूल योजना भी ध्यान से जाँचें। Index Only Scan में Heap Fetches की संख्या शून्य से अधिक हो सकती है: दृश्यता की पुष्टि के लिए PostgreSQL को कभी-कभी तालिका की पंक्तियाँ देखनी पड़ती हैं। इन पहुँचों से बचना तालिका के दृश्यता मानचित्र पर निर्भर करता है, साथ ही इस बात पर भी कि आवश्यक कॉलम इंडेक्स में मौजूद हैं या नहीं। केवल-इंडेक्स स्कैन के लिए दृश्यता जाँच।

अपने Mac से तुलना दोहराएँ

चलता हुआ स्थानीय PostgreSQL डेटाबेस और npm सहित, इंस्टॉल किया हुआ वर्तमान में समर्थित Node.js रिलीज़ इस्तेमाल करें। आधार उदाहरण PostgreSQL 14–18 में उपलब्ध सुविधाओं का उपयोग करता है; प्राप्त किए गए वर्तमान PostgreSQL मैनुअल पर संस्करण 18 लिखा है। नीचे दिया गया अतिरिक्त सीरियलाइज़ेशन निदान PostgreSQL 17 या उसके बाद के संस्करण के लिए है। node-postgres दस्तावेज़ में इसकी Node.js संगतता और इंस्टॉलेशन कमांड दी गई है। PostgreSQL दस्तावेज़, node-postgres इंस्टॉलेशन और संगतता।

Terminal खोलें और एक नए फ़ोल्डर में काम करें। ये कमांड प्रोजेक्ट बनाते हैं और डेटाबेस ड्राइवर इंस्टॉल करते हैं:

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

प्रोग्राम PGHOST, PGPORT, PGDATABASE और PGUSER के ज़रिए आपकी मौजूदा स्थानीय कनेक्शन सेटिंग्स का उपयोग करता है। इन्हें ऐसे डेटाबेस के लिए कॉन्फ़िगर करें जिससे आप पहले से कनेक्ट कर सकते हों। बिना स्पष्ट कनेक्शन विकल्पों के Client बनाते समय node-postgres इन पर्यावरण चरों को पढ़ता है। पर्यावरण चरों से कनेक्ट करना।

निम्नलिखित पूरा प्रोग्राम article-plan.cjs नाम से सहेजें। यह एक अस्थायी तालिका में कृत्रिम पंक्तियाँ बनाता है, इंडेक्स तैयार करता है, दोनों निष्पादन योजनाएँ दिखाता है और फिर दोनों सामान्य क्वेरियों का समय मापता है। दोनों तुलनाएँ एक ही कनेक्शन और श्रेणी मान का उपयोग करती हैं। क्वेरी कॉन्फ़िगरेशन में SQL text से और पैरामीटर values से दिया जाता है, जैसा कि node-postgres API में बताया गया है। क्वेरी कॉन्फ़िगरेशन।

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  const client = new Client();
  try {
    await client.connect();
    const version = await client.query('SHOW server_version');
    console.log(version.rows[0]);

    await client.query(`CREATE TEMP TABLE article_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

उसी फ़ोल्डर से इसे चलाएँ:

node article-plan.cjs

सेटअप VACUUM (ANALYZE) को अलग कमांड के रूप में भेजता है। यह पहले वैक्यूमिंग और फिर आँकड़े जुटाने का काम करता है और इसे ट्रांज़ैक्शन ब्लॉक के भीतर नहीं चलाया जा सकता। उदाहरण में जोड़ा गया कोई भी ट्रांज़ैक्शन रैपर हो, इस कमांड को उसके बाहर रखें। VACUUM।

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

योजनाएँ उपयोगी क्रम में पढ़ें

पहले स्कैन के नाम देखें, फिर अनुमानित और वास्तविक पंक्तियों की तुलना करें। इसके बाद तालिका तक पहुँच और बफ़र गतिविधि देखें। इस क्रम से उपलब्ध पहुँच तरीकों में बदलाव को गलत अनुमान या महँगे परिणाम से अलग पहचानने में मदद मिलती है। VERBOSE हर नोड के आउटपुट कॉलम भी दिखाता है। EXPLAIN का उपयोग, EXPLAIN विकल्प।

अवलोकन अर्थ अगली जाँच
ID के लिए Index Only Scan; मुख्य भागों के लिए Index Scan मुख्य भागों वाली क्वेरी को तालिका का डेटा चाहिए बफ़र और सामान्य क्वेरी के समय की तुलना करें
मुख्य भागों के लिए Bitmap Heap Scan इंडेक्स के उपयोग के लिए उसके चाइल्ड नोड को देखें हीप ब्लॉक और लौटाई गई पंक्तियों की तुलना करें
मुख्य भागों के लिए Seq Scan प्लानर पूरी तालिका स्कैन करना पसंद करता है अनुमानित और वास्तविक मेल खाती पंक्तियों की तुलना करें
ID के लिए पर्याप्त Heap Fetches दिखते हैं केवल-इंडेक्स निष्पादन में भी हीप तक पहुँचा जाता है जाँचें कि क्या यह तालिका में हुए हाल के बदलावों के कारण है
अनुमान वास्तविक पंक्तियों से काफ़ी अलग हैं आँकड़े शायद डेटा का सही वर्णन नहीं करते आँकड़े जुटाएँ और फिर तुलना करें

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

width को प्रति पंक्ति आउटपुट बाइट का अनुमान समझें, स्थानांतरित किए गए बाइट का माप नहीं। इसी तरह, प्लानर की लागत मिलीसेकंड के बजाय सापेक्ष इकाइयों में होती है। बफ़र हिट का मतलब है कि ब्लॉक PostgreSQL के कैश में मिले; इसका अर्थ यह नहीं कि कोई काम नहीं हुआ। योजना के अनुमान, बफ़र रिपोर्टिंग।

इस नमूने में दोनों क्वेरियों से 5,000 पंक्तियाँ लौटनी चाहिए। हर कृत्रिम मुख्य भाग में 1,160 ASCII बाइट हैं, इसलिए मुख्य भाग वाली क्वेरी 5,800,000 बाइट लौटाती है, यानी लगभग 5.53 MiB, जिसमें ID और प्रोटोकॉल ओवरहेड शामिल नहीं हैं। यह नमूना इनपुट से की गई गणना है, डेटाबेस प्रदर्शन का माप नहीं। प्रोग्राम का bodyBytes फ़ील्ड आपको इस अंतर की पुष्टि करने देता है।

सामान्य क्वेरी अपनी योजना का समय मापने के बाद चलती है, इसलिए उसका बीता हुआ समय कैश गर्म होने के बाद के निष्पादन को दर्शाता है। छोटे समयांतरों से निष्कर्ष निकालने से पहले तुलना दोहराएँ और क्रम बदल-बदलकर चलाएँ। निदान के लिए एक बार चलाना उपयोगी है, लेकिन इससे प्रदर्शन का कोई भरोसेमंद अनुपात स्थापित नहीं होता।

मुख्य भाग लाने में EXPLAIN के अनुमान से अधिक समय क्यों लग सकता है?

PostgreSQL के TOAST तंत्र के ज़रिए बड़े टेक्स्ट मान संपीड़ित किए जा सकते हैं या मुख्य तालिका के बाहर रखे जा सकते हैं। इसलिए पंक्ति की मुख्य एंट्री प्राप्त करने और उसका पूरा मुख्य भाग क्लाइंट के लिए तैयार करने में अलग-अलग मात्रा में काम लग सकता है। ऊपर दिया गया छोटा, दोहराव वाला नमूना कॉलम कवरेज दिखाने के लिए है; यह हर तरह के मुख्य भाग स्टोरेज का प्रतिनिधित्व नहीं करता। TOAST स्टोरेज।

डिफ़ॉल्ट रूप से, EXPLAIN ANALYZE क्वेरी के परिणामों को डिलीवर करने के लिए रूपांतरित किए बिना छोड़ देता है। PostgreSQL 17 और उसके बाद के संस्करणों में आउटपुट रूपांतरण की जाँच के लिए SERIALIZE TEXT जोड़ें; ज़रूरत पड़ने पर इसमें अलग से रखे मान लाना भी शामिल है। यह विकल्प PostgreSQL 17 में आया था। EXPLAIN का सीरियलाइज़ेशन व्यवहार, PostgreSQL 17 रिलीज़ नोट्स।

इसे आज़माने के लिए, प्रोग्राम की मौजूदा EXPLAIN टेम्पलेट लाइन को इस अंश से बदलें और पूरा प्रोग्राम फिर चलाएँ:

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

PostgreSQL 14–16 पर मूल लाइन रखें और परिणाम प्राप्त करने का समय मापने के लिए सामान्य क्वेरी का टाइमर इस्तेमाल करें। SERIALIZE फिर भी क्लाइंट को परिणाम डेटा नहीं भेजता, इसलिए यह ट्रांसमिशन या JavaScript प्रोसेसिंग नहीं माप सकता। EXPLAIN की सीमाएँ।

आम तौर पर node-postgres परिणाम पंक्तियों को JavaScript ऑब्जेक्ट में इकट्ठा करता है। इसका rowMode: 'array' विकल्प परिणाम का रूप बदलता है; यह SQL परिणाम से चुने गए मुख्य भाग के मान नहीं हटाता। इसलिए इस क्वेरी में body जोड़ने से खोया कॉलम कवरेज यह विकल्प बहाल नहीं कर सकता। रो मोड।

लेख से जुड़े काम के अनुरूप बदलाव चुनें

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

अगर स्क्रीन पर किसी श्रेणी के केवल पहले 50 लेख दिखाने हैं, तो अनुरोधित परिणाम सीमित करना एक सार्थक विकल्प है। प्रोग्राम की queries ऐरे में मुख्य भाग वाली क्वेरी स्ट्रिंग को इस SQL से बदलें:

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

इससे काम जानबूझकर हर मेल खाते लेख को प्राप्त करने से बदलकर क्रमबद्ध पृष्ठ प्राप्त करना हो जाता है। श्रेणी तय होने पर, मौजूदा (category, id) इंडेक्स ID क्रम में एक संभावित रास्ता उपलब्ध कराता है। मेल खाता B-tree इंडेक्स LIMIT के साथ ORDER BY को कुशल बना सकता है, क्योंकि पर्याप्त पंक्तियाँ मिलने पर निष्पादन रुक सकता है। चुनी गई योजना की जाँच फिर भी करनी चाहिए। इंडेक्स और ORDER BY।

INCLUDE (body) को अपने-आप होने वाला सुधार न मानें। शामिल किए गए कॉलम तालिका के डेटा की प्रतिलिपि बनाते हैं, इंडेक्स का आकार बढ़ाते हैं और इंडेक्स ट्यूपल अपनी आकार सीमा से बड़ा होने पर इंसर्शन विफल कर सकते हैं। छोटे पेलोड कॉलम को मापे गए प्रयोगों के लिए उपयोगी उम्मीदवार माना जा सकता है। INCLUDE PostgreSQL 11 में आया था; पुराने संस्करणों में यह उपलब्ध नहीं है। कवरिंग इंडेक्स से जुड़ी सावधानियाँ, PostgreSQL 11 रिलीज़ नोट्स।

जाँच के अंत में तीन ठोस निष्कर्ष दर्ज करें: क्या मुख्य भाग चुनने से केवल-इंडेक्स स्कैन की पात्रता समाप्त हुई, क्या नया स्कैन स्वीकार्य रूप से चलता है, और पूरा परिणाम Node.js तक पहुँचने में कितना समय लेता है। इन निष्कर्षों से पता चलेगा कि योजना बनाए रखनी है, कम लेख माँगने हैं या वास्तविक क्वेरी के लिए किसी दूसरे इंडेक्स का परीक्षण करना है।

स्रोत