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

आपको किन चीज़ों को साथ में मापना चाहिए?

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

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON) का उपयोग करें। ANALYZE विकल्प क्वेरी को निष्पादित करता है; TIMING OFF पूरे स्टेटमेंट का निष्पादन समय बनाए रखते हुए अलग-अलग नोड के समय-मापन को बंद करता है। मशीन-पठनीय आउटपुट से बार-बार माप एकत्र करना आसान होता है। PostgreSQL: EXPLAIN

इन मापों को साथ पढ़ें:

माप इससे क्या पता चलता है
Execution Time सर्वर का निष्पादन समय, योजना बनाने का समय छोड़कर
Planning Time योजना बनाने और उसे अनुकूलित करने में लगा समय
Shared Hit Blocks PostgreSQL के साझा कैश से पूरे हुए बफ़र एक्सेस
Shared Read Blocks साझा बफ़र में लाए गए ब्लॉक
साझा हिट और रीड का योग साझा-बफ़र एक्सेस की मात्रा की उपयोगी तुलना
Temp Read Blocks और Temp Written Blocks अस्थायी वर्किंग फ़ाइल गतिविधि, जैसे मेमोरी से बाहर जाकर सॉर्ट होना

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

बफ़र काउंट अलग-अलग एक्सेस की संख्या बताते हैं: किसी ब्लॉक को दोबारा देखने पर उसकी गिनती फिर हो सकती है। साथ ही, प्लानर की लागतें मनमानी इकाइयाँ होती हैं, मिलीसेकंड नहीं। वास्तविक निष्पादन समय की तुलना वास्तविक बफ़र काउंट से करें; प्लानर की पसंद समझने के लिए अनुमानित लागतों का उपयोग करें। PostgreSQL: Using EXPLAIN

साझा रीड से यह साबित नहीं होता कि आपके Mac के स्टोरेज डिवाइस ने भौतिक रूप से रीड किया। PostgreSQL के I/O आँकड़े यह अंतर नहीं बता सकते कि डेटा स्टोरेज से लाया गया या ऑपरेटिंग सिस्टम के पेज कैश में पहले से उपलब्ध था। अलग स्टोरेज साक्ष्य न हो, तो माप को साझा-बफ़र रीड के रूप में बताएँ। PostgreSQL: The Cumulative Statistics System

Mac प्रयोग तैयार करें और संस्करण की सीमाएँ जाँचें

Terminal में चल रहे स्थानीय PostgreSQL डेटाबेस और उपलब्ध npm सहित Node.js से शुरुआत करें। यह प्रक्रिया PostgreSQL 18 के लिए है और Node.js 22 या 24 का उपयोग करती है। node-postgres दस्तावेज़ में ये Node.js संस्करण संगत बताए गए हैं और इंस्टॉलेशन के लिए npm install pg दर्ज है। node-postgres

तुलना वाला कमांड PostgreSQL 16 और 17 के दस्तावेज़ों में भी है। PostgreSQL 18 में EXPLAIN ANALYZE के साथ बफ़र आउटपुट अपने आप शामिल होता है; BUFFERS को स्पष्ट रूप से लिखने से इन संस्करणों में उद्देश्य साफ़ रहता है। यह न मानें कि नए वैकल्पिक सेटिंग पुराने सर्वर पर भी उपलब्ध हैं। PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, PostgreSQL 18 release notes

नई कार्यशील डायरेक्टरी में ड्राइवर इंस्टॉल करें:

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

मौजूदा स्थानीय अभ्यास डेटाबेस का उपयोग करें जिसे आपका सामान्य PostgreSQL कनेक्शन खोल सकता हो। स्क्रिप्ट new Client() का उपयोग करती है, जो psql जैसे ही कनेक्शन एनवायरनमेंट वेरिएबल पढ़ता है। उदाहरण के लिए, यदि उस डेटाबेस का नाम plan_lab है, तो स्क्रिप्ट को PGDATABASE=plan_lab के साथ चलाएँ। ज़रूरत हो तो अपनी मौजूदा PGHOST या PGPORT सेटिंग जोड़ें। node-postgres: Connecting

प्रोग्राम एक ट्रांज़ैक्शन के भीतर 100,000 सिंथेटिक लेख बनाता है, स्कीमा की दो स्थितियों की तुलना करता है और ट्रांज़ैक्शन को रोल बैक कर देता है। यह नियमित टेबल का उपयोग करता है, ताकि स्कैन साझा बफ़र के अंतर्गत दिखे। PostgreSQL के दस्तावेज़ बताते हैं कि ROLLBACK ट्रांज़ैक्शन के अपडेट छोड़ देता है। यदि प्रदर्शन वाली टेबल का नाम पहले से मौजूद है, तो उसे बदलने के बजाय निर्माण विफल हो जाएगा। PostgreSQL: ROLLBACK

पहले और बाद की पूरी तुलना चलाएँ

नीचे दिए गए पूरे प्रोग्राम को compare-plans.mjs के रूप में सेव करें। यह अपना इनपुट स्वयं बनाता है, हर चरण में एक ऐसा वार्म-अप रन करता है जिसे रिकॉर्ड नहीं किया जाता, सात मापे गए रन रखता है और हर चरण की पहली मापी गई योजना प्रिंट करता है। पंक्ति जनरेटर PostgreSQL के दस्तावेज़ में दिए गए generate_series फ़ंक्शन का उपयोग करता है। PostgreSQL: Set Returning Functions

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

उसी डायरेक्टरी से इसे चलाएँ और अभ्यास डेटाबेस का वास्तविक नाम दें:

PGDATABASE=plan_lab node compare-plans.mjs

प्रोग्राम SQL और पैरामीटर मानों को client.query(text, values) के ज़रिए अलग-अलग भेजता है। यह लौटाए गए QUERY PLAN नाम के कॉलम से योजना पढ़ता है; node-postgres परिणाम के कॉलम को ऑब्जेक्ट प्रॉपर्टी से मैप करता है और PostgreSQL JSON को JavaScript ऑब्जेक्ट में पार्स करता है। node-postgres: Queries, node-postgres: pg.Result, node-postgres: Data Types

अलग ANALYZE कमांड प्लानर के लिए टेबल के आँकड़े एकत्र करता है। यह EXPLAIN के भीतर निष्पादन मापने वाले ANALYZE विकल्प से अलग है। यहाँ सिंथेटिक पंक्तियाँ लोड करने के बाद आँकड़े एकत्र करने से दोनों चरणों को टेबल के बारे में एक जैसी शुरुआती जानकारी मिलती है। PostgreSQL: ANALYZE

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

दोनों योजनाओं में क्या बदलना चाहिए?

प्रस्तावित इंडेक्स में समानता से फ़िल्टर की गई श्रेणी पहले है, और उसके बाद अनुरोधित क्रम वाले कॉलम हैं। PostgreSQL के दस्तावेज़ बताते हैं कि अग्रणी B-tree कॉलम पर समानता की शर्तें स्कैन की जाने वाली इंडेक्स रेंज को सीमित करती हैं। इसलिए यह इंडेक्स इस क्वेरी के लिए एक तर्कसंगत उम्मीदवार है। PostgreSQL: Multicolumn Indexes

इंडेक्स जोड़ने से पहले स्कैन और सॉर्ट देखें, और उसके बाद सीमा के नीचे क्रमबद्ध इंडेक्स स्कैन देखें। ये आपके आउटपुट में जाँचने की अपेक्षाएँ हैं, योजना के निश्चित आकार का वादा नहीं। मेल खाता B-tree इंडेक्स क्रमबद्ध पंक्तियाँ सीधे दे सकता है, और सीमा के साथ क्रम तय करने से बाकी मेल खाने वाली पंक्तियों को संसाधित करने की ज़रूरत नहीं पड़ सकती। PostgreSQL: Indexes and ORDER BY

प्रिंट की गई योजनाओं में तीन चीज़ें देखें:

  1. पुष्टि करें कि दोनों चरणों में रूट 50 पंक्तियाँ लौटाता है और प्रोग्राम किसी परिणाम-असंगति की सूचना नहीं देता।
  2. स्कैन और सॉर्ट के लिए ज़िम्मेदार नोड खोजें। उनके पंक्ति-अनुमानों की वास्तविक पंक्तियों से तुलना करें और देखें कि बफ़र गतिविधि कहाँ होती है।
  3. रूट के साझा एक्सेस और बार-बार मापे गए निष्पादन समय की तुलना करें। केवल नोड के नाम बदलने से उपयोगी सुधार साबित नहीं होता।

यह चरणों में की गई, वार्म-कैश तुलना है। पंक्ति जोड़ना, सही होने की जाँच वाली क्वेरी, वार्म-अप और इंडेक्स बनाना—ये सभी कैश की स्थिति को प्रभावित करते हैं। दोनों चरणों के दौरान Mac पर समान कार्यभार और डेटाबेस कॉन्फ़िगरेशन रखें। सातों अलग-अलग माप सुरक्षित रखें, ताकि असामान्य रूप से धीमा रन दिखाई देता रहे; केवल माध्यिका से अंतर छिप सकता है।

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

कम काम और ज़्यादा गर्म बफ़र में अंतर कैसे करें?

नीचे दिए गए आँकड़े केवल उदाहरण के लिए इनपुट हैं, दर्ज किया गया आउटपुट नहीं। वे दिखाते हैं कि रीड, हिट और समय को साथ क्यों देखना चाहिए:

उदाहरण रन निष्पादन साझा हिट साझा रीड हिट + रीड
A, शुरुआती माप 12 ms 900 100 1,000
A, दोहराया गया माप 10 ms 1,000 0 1,000
B, प्रस्तावित योजना 3 ms 80 0 80

A के दोनों मापों के बीच रीड गायब हो जाते हैं, जबकि साझा एक्सेस का कुल योग 1,000 ही रहता है। यह कैश की स्थिति में बदलाव के अनुरूप है; इससे साझा-बफ़र एक्सेस की मात्रा कम होना साबित नहीं होता। B में कम समय के साथ कम एक्सेस भी हैं। यदि समतुल्य परिणामों के साथ यह ढाँचा बार-बार दिखे, तो इससे सीमित निष्कर्ष को समर्थन मिलता है कि इस काम के लिए B साझा बफ़र पर कम काम करता है।

अपने नतीजे अलग हों, तो इस निर्णय-सहायिका का उपयोग करें:

दिखाई दिया पैटर्न व्याख्या और अगला कदम
कम समय और कम साझा एक्सेस जाँचे गए कार्यभार में सुधार का साक्ष्य; एकरूपता जाँचने के लिए दोहराएँ
कम समय, कम रीड, लेकिन कुल एक्सेस लगभग समान कैश की स्थिति बदलाव समझा सकती है; वार्म्ड रन की तुलना करें
कम एक्सेस, लेकिन समय समान या अधिक चुनाव करने से पहले सॉर्ट, एक्सप्रेशन और समय के उतार-चढ़ाव जाँचें
शून्य रीड, लेकिन कई हिट कैश से निष्पादन में भी बफ़र एक्सेस होते हैं; ज़्यादा गतिविधि वाले नोड देखें
अस्थायी रीड या राइट दिखें सेटिंग बदलने से पहले वह ऑपरेशन देखें जिसने उन्हें उत्पन्न किया

कैश-हिट प्रतिशत को निर्णय का मुख्य पैमाना न बनाएँ। उदाहरण में, वार्म्ड A और B दोनों की साझा-बफ़र हिट दर 100% है, फिर भी उनके एक्सेस काउंट में बड़ा अंतर है। समय के साथ कच्चे काउंट और परिणाम का आकार भी रखें।

ये माप एप्लिकेशन की गति से कैसे जुड़े हैं?

सर्वर का Execution Time योजना बनाने का समय छोड़ता है, जबकि सामान्य EXPLAIN ANALYZE में परिणाम भेजने का समय और डिफ़ॉल्ट रूप से आउटपुट रूपांतरण भी शामिल नहीं होता। इंस्ट्रूमेंटेशन से स्वयं अतिरिक्त ओवरहेड जुड़ता है। इसलिए स्क्रिप्ट इंस्ट्रूमेंट किए गए डेटाबेस निष्पादन की तुलना करती है, लेख-सूची स्क्रीन पर दिखाने में लगने वाला पूरा समय नहीं। PostgreSQL: Using EXPLAIN

PostgreSQL 17 या 18 पर आउटपुट रूपांतरण को अलग से जाँचने के लिए उन्हीं व्याख्या विकल्पों में SERIALIZE TEXT जोड़ें। PostgreSQL 16 के विकल्पों में यह शामिल नहीं है। सीरियलाइज़ेशन से भी क्वेरी की परिणाम पंक्तियाँ भेजी नहीं जातीं, इसलिए इससे नेटवर्क डिलीवरी का समय नहीं मापा जा सकता। तुलना वाले सभी रन में सीरियलाइज़ेशन सेटिंग एक जैसी रखें। PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

एप्लिकेशन का माप लेने के लिए, कनेक्शन पहले से स्थापित होने पर सामान्य लेख SELECT को ड्राइवर की awaited कॉल पूरी होने तक टाइम करें। इसे अलग क्लाइंट बीते-समय के माप के रूप में दर्ज करें। एप्लिकेशन की वास्तविक क्वेरी कॉन्फ़िगरेशन बनाए रखें: node-postgres क्वेरी में name जोड़ने से प्रति-कनेक्शन तैयार स्टेटमेंट का व्यवहार सक्षम होता है और तुलना की परिस्थितियाँ बदल जाती हैं। node-postgres: Queries

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

स्रोत