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

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

दोहराई जा सकने वाली उम्मीदवार क्वेरी सेट करें

मौजूदा स्थानीय PostgreSQL इंस्टॉलेशन का उपयोग करें, जहाँ psql उपलब्ध हो। Terminal खोलें, अपनी सामान्य कनेक्शन सेटिंग के साथ psql चलाएँ और SQL उदाहरणों के दौरान वही सत्र खुला रखें। PostgreSQL, psql को क्वेरी सबमिट करने और परिणाम देखने के लिए अपना इंटरैक्टिव टर्मिनल बताता है। psql

पहले SHOW server_version; से सर्वर संस्करण जाँचें। इस आर्टिकल के लिए प्राप्त मौजूदा दस्तावेज़ PostgreSQL 18 को नवीनतम संस्करण बताते हैं। उदाहरणों में PostgreSQL 12 या उसके बाद का सिंटैक्स इस्तेमाल हुआ है; यह सुविधा की न्यूनतम सीमा है, किसी पुराने रिलीज़ को इस्तेमाल करने की सिफ़ारिश नहीं। PostgreSQL 12 में CTE इनलाइनिंग और MATERIALIZED तथा NOT MATERIALIZED नियंत्रण जोड़े गए थे। WITH Queries, Release 12

नए सत्र में यह सेटअप एक बार चलाएँ। यह 20,000 सिंथेटिक आर्टिकल और विषय तथा ID लुकअप के लिए एक इंडेक्स बनाता है। नीचे इस्तेमाल किया गया उम्मीदवार प्रेडिकेट पहले 2,000 आर्टिकल चुनता है।

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

डिफ़ॉल्ट रूप से ये अस्थायी तालिकाएँ सत्र तक रहती हैं। इन्हें लोड करने के बाद स्पष्ट रूप से ANALYZE चलाएँ: PostgreSQL की autovacuum प्रक्रिया अस्थायी तालिकाओं का विश्लेषण नहीं करती। CREATE TABLE

अब आधार-रेखा दर्ज करें। Lateral सबक्वेरी मौजूदा आर्टिकल के विषय के लिए सबसे बड़ी उम्मीदवार ID निकालती है। यहाँ “सबसे बड़ी ID” केवल परिभाषित कार्य है; इसका प्रकाशन तिथि से कोई आशय नहीं है।

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

स्पष्ट MATERIALIZED प्रयोग के पुनः उपयोग की सीमा को स्पष्ट करता है। Materialized CTE एक बार बनाया जा सकता है, जबकि उसके उपभोक्ता संग्रहीत पंक्तियों को बार-बार पढ़ते हैं। PostgreSQL का दस्तावेज़ दिखाता है कि कई बार संदर्भित materialized CTE, बीच के परिणाम में खोज करते समय अंतर्निहित तालिका के इंडेक्स का लाभ खो देता है। Common Table Expression Materialization

इस आर्टिकल के लिए SQL उदाहरणों की जाँच की गई है, लेकिन उन्हें PostgreSQL सर्वर पर चलाया नहीं गया। सिंथेटिक पंक्तियों की संख्या का गणित अलग से जाँचा गया; किसी निष्पादन समय या कैप्चर किए गए प्लान को माप के रूप में प्रस्तुत नहीं किया गया है।

दोहराए गए काम का स्थान जानने के लिए प्लान पढ़ें

उम्मीदवार बनाने वाली शाखा से शुरू करें, फिर हर उपभोक्ता का अनुसरण करें। Nested loop के अंतर्गत बाहरी इनपुट और इनर लुकअप पहचानें। PostgreSQL किसी नोड की निष्पादन संख्या को loops के रूप में रिपोर्ट करता है; वास्तविक पंक्तियाँ और नोड समय प्रति निष्पादन औसत होते हैं। Correlated scalar-subquery संस्करण में दोहराया गया काम इसके बजाय SubPlan के नीचे दिख सकता है, इसलिए निदान में किसी खास लेबल की अपेक्षा करने के बजाय निष्पादित नोड का अनुसरण करें। Using EXPLAIN

अपने वास्तविक प्लान से ये चार बातें दर्ज करें:

  1. बाहरी लुकअप में कितनी पंक्तियाँ आती हैं?
  2. अंतर्निहित इनर स्कैन कितनी बार निष्पादित होता है?
  3. क्या यह स्कैन उम्मीदवारों को पढ़कर फिर फ़िल्टर के ज़रिए उनमें से अधिकांश को हटा देता है?
  4. क्या बाहरी पंक्तियों और महँगे इनर काम के बीच Memoize नोड है?

अलग-अलग स्तरों को अलग रखें। कोई aggregate हर निष्पादन में एक अधिकतम मान लौटा सकता है, जबकि उसकी child स्कैन उसे निकालने के लिए कई पंक्तियाँ पढ़ती है। इसलिए उम्मीदवार बनाने वाली प्रक्रिया का एक बार चलना यह साबित नहीं करता कि उम्मीदवारों का उपयोग सस्ता है।

इस सटीक नमूने में 2,000 उम्मीदवार 50 विषयों में फैले हैं और हर विषय में 40 आर्टिकल हैं। नीचे की गणना संभावित काम के तरीकों का वर्णन करती है, किसी निश्चित प्लान आउटपुट या रनटाइम का वादा नहीं करती:

उपयोग का तरीका उदाहरण के तौर पर जाँची गई उम्मीदवार-पंक्तियाँ
हर बाहरी आर्टिकल के लिए पूरा उम्मीदवार स्कैन 2,000 × 2,000 = 4,000,000
कैश में बाद के सभी लुकअप होने पर हर विषय के लिए एक पूरा स्कैन 50 × 2,000 = 100,000
उम्मीदवारों को विषय के अनुसार एक बार समूहित करना समूह बनाने के चरण में आने वाली 2,000 पंक्तियाँ, साथ में बाद का जॉइन काम

यह अंतर PostgreSQL 14 और उसके बाद के संस्करणों में मायने रखता है, जिनमें इनर nested-loop परिणामों की memoization जोड़ी गई थी। मौजूदा दस्तावेज़ Memoize को पैरामीटरयुक्त स्कैन परिणामों को कैश करने वाला बताते हैं, जिससे कैश हिट पर अंतर्निहित स्कैन छोड़ा जा सकता है; प्रविष्टियाँ हटाई भी जा सकती हैं। बाहरी पंक्तियों की संख्या को हज़ारों पूरे स्कैन का कारण मानने से पहले कैश की कुंजी और रिपोर्ट किए गए हिट, मिस, इविक्शन तथा ओवरफ़्लो देखें। Release 14, Query Planning

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

दोहराए जा रहे काम के अनुरूप री-राइट चुनें

सही सवाल यह है कि हर उपभोक्ता को उम्मीदवार सेट से कितना काम चाहिए। पहला प्रयोग चुनने के लिए इस निर्णय-सहायिका का उपयोग करें:

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

प्रति-विषय परिणाम एक बार निकालें

इस काम के लिए समूहित सारांश पुनः उपयोग को सीधे व्यक्त करता है। सेटअप के बाद, उसी सत्र में यह चलाएँ:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

यह री-राइट हर विषय के लिए एक अधिकतम मान निकालता है और उसे हर उम्मीदवार आर्टिकल से जोड़ता है। PostgreSQL max को गैर-शून्य इनपुट मान का अधिकतम बताता है। नमूने में विषय और ID कॉलम गैर-शून्य हैं, इसलिए समतुल्यता सीधी है: हर उम्मीदवार ऐसे समूह का हिस्सा है जिसमें वह स्वयं शामिल है। Aggregate Functions

EXPLAIN के बिना क्वेरी चलाने पर 2,000 परिणाम पंक्तियों की अपेक्षा करें। उदाहरण के लिए, आर्टिकल 1 विषय 2 में आता है और उसे highest_id = 1951 मिलता है; विषय 1 को highest_id = 2000 मिलता है। ये नमूना जनरेटर से निकले निश्चित परिणाम हैं।

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

यह री-राइट अलग प्लान की अनुमति देता है, लेकिन hash join को अनिवार्य नहीं करता और गति बढ़ने की गारंटी नहीं देता। समूह बनाने तथा जॉइन के मापे गए काम की तुलना आधार-रेखा के वास्तविक इनर निष्पादनों से करें।

जाँचें कि क्या स्रोत इंडेक्स बीच के स्कैन की जगह ले सकते हैं

पात्र CTE के लिए, पूरी आधार-रेखा क्वेरी में AS MATERIALIZED की जगह AS NOT MATERIALIZED रखें और फिर से चलाएँ। इससे स्रोत रिलेशन के साथ संयुक्त ऑप्टिमाइज़ेशन संभव होता है, और इस प्रक्रिया में संग्रहीत उम्मीदवार स्कैन की जगह स्रोत लुकअप लिए जा सकते हैं। इससे उम्मीदवारों की गणना दोहराई भी जा सकती है। Common Table Expression Materialization

नमूने का (topic_id, id) इंडेक्स अग्रणी विषय कॉलम पर समानता और ID कॉलम पर प्रतिबंध से मेल खाता है। इसलिए यह जाँचने के लिए उचित इंडेक्स है; पुष्टि करें कि वास्तविक प्लान इसका उपयोग करता है या नहीं और कितना स्कैन करता है। PostgreSQL के अनुसार, बहु-कॉलम B-tree स्कैन को सीमित करने वाली स्थितियों में अग्रणी कॉलम पर समानता और अगले कॉलम पर असमानता शामिल हैं। Multicolumn Indexes

उम्मीदवार की परिभाषा के अंदर id <= 2000 बनाए रखें। सभी 20,000 स्रोत पंक्तियों में से अधिकतम मान खोजने से जवाब बदल जाएगा। दस्तावेज़ में बताई गई सीमा भी बनाए रखें: recursive CTE और ऐसे CTE पर NOT MATERIALIZED अनदेखा किया जाता है जो side-effect-free नहीं हैं। अपात्र CTE के लिए इसके बजाय समर्थित relational री-राइट इस्तेमाल करें या उसका परिणाम स्पष्ट रूप से अलग स्टेज में रखें। SELECT

कई स्टेटमेंट में पुनः उपयोग होने पर इंडेक्स वाला उम्मीदवार रिलेशन स्टेज करें

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

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

अस्थायी तालिकाओं के इंडेक्स भी अस्थायी होते हैं और रिलेशन सत्र तक सीमित रहता है। यह तय करते समय कि स्टेजिंग उपयोगी है या नहीं, तालिका बनाने, इंडेक्स बनाने और विश्लेषण की लागत शामिल करें। केवल अंतिम लुकअप की तुलना पूरे unstaged काम से न करें। CREATE TABLE

Mac पर node-postgres से वही प्रयोग दर्ज करें

एप्लिकेशन के क्वेरी पथ की जाँच के लिए, Node.js का वर्तमान समर्थित रिलीज़ इस्तेमाल करें और दस्तावेज़ित पैकेज npm install pg से इंस्टॉल करें। नीचे दिए गए पूरे प्रोग्राम को उस प्रोजेक्ट में diagnose.mjs नाम से सहेजें और node diagnose.mjs चलाएँ। यह अपना सिंथेटिक इनपुट बनाता है, इसलिए पहले के psql सत्र पर निर्भर नहीं है। node-postgres

यह प्रोग्राम आपके मौजूदा PostgreSQL कनेक्शन परिवेश का उपयोग करता है। node-postgres, psql में इस्तेमाल होने वाले समान कनेक्शन परिवेश वेरिएबलों का दस्तावेज़ देता है, जिनमें PGHOST, PGPORT और PGDATABASE शामिल हैं। Connecting

import pg from 'pg';
const { Client } = pg;
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_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

क्वेरी कॉन्फ़िगरेशन दस्तावेज़ित text और values फ़ील्डों का उपयोग करता है। name जोड़ने से हर कनेक्शन पर एक prepared statement बनता है; इससे उम्मीदवार पंक्तियाँ कैश नहीं होतीं और न ही इनर निष्पादन दोहरना बंद होता है। Queries

यदि मौजूदा एप्लिकेशन में pool इस्तेमाल होता है, तो अस्थायी तालिका बनाना और उसका उपयोग एक ही checkout किए गए client पर रखें, फिर finally में उसे रिलीज़ करें। अलग-अलग pool.query कॉल अलग clients का उपयोग कर सकती हैं। इसी तरह, node-postgres के अनुसार किसी transaction के सभी स्टेटमेंट में वही client instance इस्तेमाल होना चाहिए। Pooling, Transactions

जिन named statement का व्यवहार पैरामीटर मानों के साथ बदलता है, उनके लिए planning की अलग से जाँच करें। PostgreSQL generic या पैरामीटर-विशिष्ट custom plans चुन सकता है; दस्तावेज़ में बताया गया निरीक्षण तरीका prepared statement के लिए EXPLAIN EXECUTE है। इसलिए ताज़ा समझाई गई unnamed क्वेरी किसी स्थापित prepared execution से अलग हो सकती है। PREPARE

तय करें कि दोहराया गया काम वास्तव में कम हुआ है या नहीं

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

दूसरे निदान पास के लिए, EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) प्रति-नोड घड़ी माप से बचते हुए पंक्ति संख्याएँ और स्टेटमेंट निष्पादन समय रखता है। पैरेंट नोड पर बफ़र के कुल आँकड़ों में child गतिविधि शामिल होती है, इसलिए पूरे ट्री का योग न करें। EXPLAIN ANALYZE क्वेरी चलाता है, लेकिन उसके परिणाम की पंक्तियाँ Node.js तक पहुँचाने का समय नहीं मापता। EXPLAIN

Planner switches को प्रयोग के रूप में लें। enable_nestloop को off करने से nested loops कम पसंद किए जाते हैं, लेकिन वे पूरी तरह समाप्त नहीं होते; enable_material भी सभी materialization को रोक नहीं सकता। इनमें से कोई भी स्विच यह साबित नहीं करता कि उम्मीदवारों का उपयोग कुशल हो गया है। Query Planning

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

स्रोत