LIMIT 10 परिणाम को सीमित करता है, लेकिन दस अलग-अलग मान खोजने के लिए हज़ारों इनपुट पंक्तियों की ज़रूरत पड़ सकती है। यह गाइड दिखाती है कि इस काम का निदान कैसे करें और अपने Mac के Terminal से कृत्रिम लेख-विषयों का उपयोग करके दो तरीकों की तुलना कैसे करें।

दस परिणामों के लिए हज़ारों पंक्तियों की ज़रूरत क्यों पड़ सकती है

मान लें कि किसी लेख तालिका में 100 विषयों के 100,000 लेख हैं, यानी हर विषय के 1,000 लेख। आपका काम वर्णमाला के क्रम में पहले दस अलग-अलग विषय-नाम प्राप्त करना है। क्वेरी को दोहराए गए नाम हटाने, विषयों का क्रम बनाए रखने और दस परिणाम लौटाने होंगे।

PostgreSQL, DISTINCT को डुप्लिकेट आउटपुट पंक्तियाँ हटाने और LIMIT को लौटाए गए परिणाम को सीमित करने के रूप में परिभाषित करता है। इन अर्थों से अंतर्निहित स्कैन पर दस पंक्तियों की सीमा नहीं लगती। योजना चुनते समय प्लानर सीमा पर विचार करता है, इसलिए वास्तविक काम चुनी गई निष्पादन रणनीति पर निर्भर करता है। SELECT

ऐसी क्रमबद्ध स्ट्रीम पर विचार करें जिसमें topic-001 की 1,000 आवृत्तियाँ हों, फिर topic-002 की 1,000 आवृत्तियाँ, और इसी तरह आगे। स्ट्रीमिंग डुप्लिकेट-हटाने वाला ऑपरेशन दसवाँ अलग विषय तभी देखता है, जब वह उससे पहले के नौ समूहों की सभी पंक्तियाँ और दसवें समूह की पहली पंक्ति पढ़ चुका हो:

ज़रूरी इनपुट पंक्तियाँ = (9 × 1,000) + 1 = 9,001
लौटाए गए अलग-अलग परिणाम = 10

यह उस विशेष स्ट्रीमिंग रणनीति के लिए एक उदाहरणात्मक गणना है, मापी गई PostgreSQL योजना नहीं। समूहों के असमान आकार से गणना बदल जाती है: पहले विषय का बहुत बड़ा समूह काम का अधिकांश हिस्सा बन सकता है, भले ही बाद के समूह छोटे हों।

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

यह जानने के लिए योजना पढ़ें कि पंक्तियाँ कहाँ खर्च हो रही हैं

वास्तविक क्वेरी और पैरामीटर मानों पर EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) का उपयोग करें। ANALYZE स्टेटमेंट चलाकर वास्तविक पंक्ति-गणना बताता है; BUFFERS ब्लॉक गतिविधि बताता है। नीचे दिया गया पूरा उदाहरण कृत्रिम डेटा पर यह निदान करता है। EXPLAIN

नीचे योजना के दो उपयोगी रूप दिखाए गए हैं। ये योजनाओं के सांकेतिक रूप हैं, किसी रिकॉर्ड किए गए आउटपुट या इस बात की गारंटी नहीं कि आपका इंस्टॉलेशन यही योजना चुनेगा।

क्रमबद्ध स्ट्रीमिंग पथ:
Limit
  Unique
    Index Only Scan या Index Scan

एग्रीगेशन पथ:
Limit
  Sort
    HashAggregate
      Seq Scan

क्रमबद्ध पथ में स्कैन के आउटपुट की तुलना Unique और Limit के आउटपुट से करें। बड़ा अंतर बताता है कि इनपुट में बहुत सारे डुप्लिकेट हैं। एग्रीगेशन पथ में, अंतिम सॉर्ट और सीमा पूरी होने से पहले एग्रीगेट अपने इनपुट को समूह निर्धारित करने के लिए पढ़ता है। PostgreSQL के योजना दस्तावेज़ बताते हैं कि स्कैन सॉर्ट और एग्रीगेशन नोड्स को डेटा कैसे देते हैं। Using EXPLAIN

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

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

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

Node.js का मौजूदा LTS रिलीज़, npm और पहले से मौजूद लोकल PostgreSQL डेटाबेस इस्तेमाल करें। यह निर्देशिका PostgreSQL 18 को दस्तावेज़ीकरण का आधार मानती है। प्रोग्राम आपके सर्वर का संस्करण दिखाता है, ताकि आप परिणामों को उस संस्करण के दस्तावेज़ों के संदर्भ में समझ सकें। node-postgres, npm install pg से इंस्टॉलेशन और मौजूदा तथा LTS Node.js रिलीज़ के समर्थन का दस्तावेज़ देता है। node-postgres

Terminal में एक वर्किंग डायरेक्टरी बनाएँ और ड्राइवर इंस्टॉल करें:

mkdir distinct-row-demo
cd distinct-row-demo
npm init -y
npm install pg

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

कनेक्शन की सेटिंग PostgreSQL के सामान्य एनवायरनमेंट वेरिएबल से आती हैं। प्रोग्राम चलाने से पहले अपने मौजूदा लोकल डेटाबेस के लिए इन्हें कॉन्फ़िगर करें। node-postgres एनवायरनमेंट-आधारित कनेक्शन और SQL टेक्स्ट से अलग क्वेरी मान देने, दोनों का दस्तावेज़ देता है। Connecting, Queries

import pg from 'pg';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client({ connectionTimeoutMillis: 5000 });
const limit = 10;

const distinctSql = `
  SELECT DISTINCT topic FROM demo_articles
  ORDER BY topic LIMIT $1::integer
`;

const seekSql = `
  WITH RECURSIVE topics(topic, step) AS (
    (SELECT topic, 1 FROM demo_articles ORDER BY topic LIMIT 1)
    UNION ALL
    SELECT n.topic, t.step + 1
    FROM topics AS t
    CROSS JOIN LATERAL (
      SELECT a.topic FROM demo_articles AS a
      WHERE a.topic > t.topic ORDER BY a.topic LIMIT 1
    ) AS n
    WHERE t.step < $1::integer
  )
  SELECT topic FROM topics ORDER BY topic
`;

async function showPlan(label, sql) {
  const plan = await client.query(
    `EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) ${sql}`, [limit]
  );
  console.log(`\n${label}`);
  console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));
}

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log('PostgreSQL', version.rows[0].server_version);

  await client.query(`
    CREATE TEMP TABLE demo_articles (
      article_id integer NOT NULL,
      topic text COLLATE "C" NOT NULL,
      title text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO demo_articles (article_id, topic, title)
    SELECT n,
           'topic-' || lpad((((n - 1) / 1000) + 1)::text, 3, '0'),
           'Sample article ' || n::text
    FROM generate_series(1, 100000) AS s(n)
  `);

  await client.query('ANALYZE demo_articles');
  await showPlan('Before index', distinctSql);

  await client.query(
    'CREATE INDEX demo_articles_topic_idx ON demo_articles (topic)'
  );
  await client.query('ANALYZE demo_articles');
  await showPlan('After index', distinctSql);
  await showPlan('Successor seeks', seekSql);

  const ordinary = await client.query(distinctSql, [limit]);
  const seeking = await client.query(seekSql, [limit]);
  assert.deepEqual(seeking.rows, ordinary.rows);
  assert.equal(ordinary.rows.length, limit);
  console.log(ordinary.rows.map(row => row.topic));
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

डेटा जनरेटर PostgreSQL के दस्तावेज़ीकृत generate_series फ़ंक्शन का उपयोग करके 1 से 100,000 तक के पूर्णांक बनाता है। अंकगणित से लगातार 1,000 लेखों के हर समूह को एक ही विषय मिलता है। ANALYZE तालिका के वे आँकड़े देता है जिनका उपयोग प्लानर योजना चुनते समय करता है। Set Returning Functions, ANALYZE

प्रोग्राम चलाएँ और अपने मौजूदा लोकल डेटाबेस का नाम दें। उदाहरण के लिए, अगर उस डेटाबेस का नाम postgres है:

PGHOST=localhost PGPORT=5432 PGDATABASE=postgres node distinct-demo.mjs

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

Index-only scan में भी काफी काम क्यों हो सकता है

Index Only Scan का अर्थ है कि क्वेरी माँगे गए कॉलम मान इंडेक्स से प्राप्त कर सकती है। इसका अर्थ यह नहीं कि हर अलग परिणाम के लिए केवल एक इंडेक्स प्रविष्टि पढ़ी जाती है। तालिका तक शायद ही पहुँचा जाए, फिर भी इसकी वास्तविक पंक्ति-गणना देखें।

हीप तक पहुँचना एक अलग बात है। PostgreSQL visibility map की जाँच करता है और जब संबंधित पेज all-visible चिह्नित नहीं होते, तब भी हीप टपल देख सकता है। इसलिए ताज़ा डाली गई उदाहरण की पंक्तियों से index-only योजना में भी heap fetches हो सकते हैं। स्कैन पंक्तियों के साथ Heap Fetches भी देखें। Index-Only Scans and Covering Indexes

INCLUDE से अतिरिक्त payload कॉलम जोड़ने पर index-only scan को और मान उपलब्ध हो सकते हैं, लेकिन माँगे गए अलग-अलग विषयों की संख्या नहीं बदलती। इंडेक्स में कवरेज का चुनाव प्रमाणित हीप-एक्सेस काम को घटाने के लिए करें; डुप्लिकेट खपने की गणना अलग से करें। Index-Only Scans and Covering Indexes

क्या PostgreSQL 18 का skip scan यह काम अपने आप हल करता है?

PostgreSQL 18 में B-tree skip scan का समर्थन जोड़ा गया। दस्तावेज़ीकृत उद्देश्य है कि मल्टीकॉलम इंडेक्स पर कुछ खोजों को बेहतर बनाया जाए, जब बाद के कॉलमों की शर्तें पहले वाले कॉलमों के मानों पर बार-बार खोज करने में मदद कर सकती हों। यह सुविधा संस्करण 18 में जोड़ी गई थी; यह न मानें कि PostgreSQL 17 या उससे पुराने संस्करणों में भी यह मौजूद है। Release 18, Multicolumn Indexes

एक अलग-अलग शुरुआती की से अगले तक अपने आप जाना आम तौर पर loose index scan कहलाता है। PostgreSQL प्रोजेक्ट विकी इस ऑपरेशन को predicate-केंद्रित index skip scan से अलग बताती है और SQL रिकर्सन से इसका अनुकरण करने की विधि देती है। इसलिए PostgreSQL 18 की skip scan घोषणा इस एकल-कॉलम DISTINCT क्वेरी के लिए स्वचालित loose scanning होने की पुष्टि नहीं करती। Loose indexscan

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

यह उदाहरण जानबूझकर गैर-नल विषय, आरोही क्रम, घोषित C collation और सकारात्मक सीमा मानता है। शून्य सीमा पर भी रिकर्सिव anchor लौटेगा, इसलिए सामान्यीकृत कार्यान्वयन में शून्य को अलग से संभालना होगा। नल मानों को स्पष्ट रूप से संभालना ज़रूरी है, क्योंकि greater-than तुलना उन्हें सूचीबद्ध नहीं करेगी। अगर आप लेख फ़िल्टर जोड़ते हैं, तो वही शर्तें शुरुआती खोज और हर उत्तराधिकारी खोज पर लागू करें; वरना बदली हुई क्वेरी अलग परिणाम दे सकती है।

अगले बदलाव का चुनाव साक्ष्य के आधार पर करें

योजनाएँ इकट्ठा करने के बाद इस निर्णय-सहायिका का उपयोग करें। यह उस काम को अलग करती है जिसे क्रमबद्ध इंडेक्स घटा सकता है और उस काम को जो दोहराए गए विषय-मानों के कारण होता है।

देखा गया परिणाम आगे क्या जाँचें
स्कैन एग्रीगेशन और अंतिम सॉर्ट को डेटा देता है मौजूदा योजना की तुलना विषयों के क्रम से मेल खाते B-tree इंडेक्स से करें।
क्रमबद्ध स्कैन दस विषयों के लिए हज़ारों पंक्तियाँ निकालता है डुप्लिकेट समूहों के आकार मापें और स्पष्ट उत्तराधिकारी खोजों की तुलना करें।
Index-only scan में heap fetches की संख्या अधिक है डुप्लिकेट गणना से अलग, visibility और हीप-एक्सेस काम की जाँच करें।
अनुमान वास्तविक पंक्तियों से काफी अलग हैं ANALYZE से आँकड़े ताज़ा करें, फिर दोबारा मापें।
इंडेक्स की गई सामान्य क्वेरी पहले से किफ़ायती है सरल क्वेरी रखें, जब तक मापी गई कोई अन्य आवश्यकता बदलाव को उचित न ठहराए।

LIMIT 10 को ऐसी सबक्वेरी में डालने से बचें जो DISTINCT लागू करने से पहले दस कच्चे लेख चुनती है। नमूने के क्रम में वे दस लेख सभी topic-001 से संबंधित हैं, इसलिए बदली हुई क्वेरी दस के बजाय एक विषय लौटाती है। इससे काम ही बदल जाता है।

अलग पहचानने वाली key को भी सटीक रखें। चुने गए कॉलम में article_id जोड़ने से हर लेख अलग आउटपुट पंक्ति बन जाता है और विषयों के डुप्लिकेट हटाने का उद्देश्य विफल हो जाता है। अगर काम बदलकर हर विषय के लिए एक प्रतिनिधि लेख चुनना हो, तो PostgreSQL का DISTINCT ON उस ऑपरेशन का समर्थन करता है; इसमें उसके एक्सप्रेशन, ORDER BY के सबसे बाएँ एक्सप्रेशन से मेल खाने चाहिए। यह अपने आप कम स्कैन की गारंटी नहीं देता। SELECT

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

स्रोत