PostgreSQL एंटीपैटर्न: "केवल एक ही होना चाहिए!"

एसक्यूएल में, आप यह वर्णन करते हैं कि आप "क्या" हासिल करना चाहते हैं, न कि "कैसे" इसे निष्पादित किया जाना चाहिए। इसलिए, "जैसा सुना जाता है, वैसा लिखा जाता है" की शैली में SQL क्वेरीज़ को विकसित करने की समस्या, इसके साथ-साथ सम्मान का स्थान लेती है SQL में शर्तों की गणना की विशेषताएं.

आज, अत्यंत सरल उदाहरणों का उपयोग करते हुए, आइए देखें कि उपयोग के संदर्भ में इसका क्या परिणाम हो सकता है GROUP/DISTINCT и LIMIT उनके साथ।

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

और कभी-कभी आप भाग्यशाली होते हैं और यह "बस काम करता है", कभी-कभी इसका प्रदर्शन पर अप्रिय प्रभाव पड़ता है, और कभी-कभी यह ऐसे प्रभाव देता है जो डेवलपर के दृष्टिकोण से पूरी तरह से अप्रत्याशित होते हैं।

ख़ैर, शायद इतना शानदार नहीं, लेकिन...

"प्यारी जोड़ी": जुड़ें + अलग

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

इससे साफ हो जाएगा कि वे क्या चाहते हैं रिकॉर्ड X का चयन करें जिसके लिए Y में ऐसे रिकॉर्ड हैं जो पूरी की गई शर्त से संबंधित हैं. के माध्यम से एक अनुरोध लिखा JOIN - कुछ पीके मान कई बार प्राप्त हुए (वास्तव में वाई में कितनी उपयुक्त प्रविष्टियाँ दिखाई दीं)। कैसे हटाएं? निश्चित रूप से DISTINCT!

यह विशेष रूप से "संतुष्टिदायक" होता है जब प्रत्येक एक्स-रिकॉर्ड के लिए कई सौ संबंधित वाई-रिकॉर्ड होते हैं, और फिर डुप्लिकेट को वीरतापूर्वक हटा दिया जाता है...

कैसे ठीक करें? आरंभ करने के लिए, यह समझें कि समस्या को संशोधित किया जा सकता है "रिकॉर्ड X का चयन करें जिसके लिए Y में कम से कम एक पूरी शर्त के साथ जुड़ा हुआ है" - आख़िरकार, हमें Y-रिकॉर्ड से कुछ भी नहीं चाहिए।

नेस्टेड मौजूद है

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

PostgreSQL के कुछ संस्करण समझते हैं कि EXISTS में जो पहली प्रविष्टि आती है उसे ढूंढना पर्याप्त है, पुराने संस्करण ऐसा नहीं करते हैं। इसलिए मैं हमेशा संकेत देना पसंद करता हूं LIMIT 1 अंदर EXISTS.

पार्श्विक जोड़

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

वही विकल्प, यदि आवश्यक हो, पाए गए संबद्ध वाई-रिकॉर्ड से कुछ डेटा तुरंत वापस करने की अनुमति देता है। लेख में एक समान विकल्प पर चर्चा की गई है "पोस्टग्रेएसक्यूएल एंटीपैटर्न: एक दुर्लभ रिकॉर्ड जॉइन के मध्य तक पहुंच जाएगा".

"अधिक भुगतान क्यों करें": DISTINCT [ON] + LIMIT 1

ऐसे क्वेरी परिवर्तनों का एक अतिरिक्त लाभ रिकॉर्ड की खोज को आसानी से सीमित करने की क्षमता है यदि उनमें से केवल एक या कुछ की आवश्यकता होती है, जैसा कि निम्नलिखित मामले में है:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

अब हम अनुरोध पढ़ते हैं और यह समझने का प्रयास करते हैं कि DBMS क्या करने का प्रस्ताव रखता है:

• संकेतों को जोड़ना
• X.pk द्वारा अद्वितीय
• शेष प्रविष्टियों में से एक का चयन करें

तो तुम्हें क्या मिला? "सिर्फ एक प्रविष्टि" अनूठे लोगों में से - और अगर हम इसे गैर-अनूठे लोगों में से एक लेते हैं, तो क्या परिणाम किसी तरह बदल जाएगा?.. "और अगर कोई अंतर नहीं है, तो अधिक भुगतान क्यों करें?"

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

और बिल्कुल वैसा ही विषय GROUP BY + LIMIT 1.

"मुझे बस पूछना है": अंतर्निहित समूह + सीमा

समान चीजें अलग-अलग जगहों पर होती हैं गैर-खालीपन की जाँच अनुरोध बढ़ने पर संकेत या सीटीई:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

समग्र कार्य (count/min/max/sum/...) स्पष्ट निर्देशों के बिना भी, पूरे सेट पर सफलतापूर्वक निष्पादित किए जाते हैं GROUP BY. सिर्फ साथ LIMIT वे बहुत मिलनसार नहीं हैं.

डेवलपर सोच सकता है "अगर वहाँ रिकॉर्ड हैं, तो मुझे LIMIT से अधिक की आवश्यकता नहीं है". लेकिन ऐसा मत करो! क्योंकि आधार के लिए यह है:

• गिनें कि वे क्या चाहते हैं सभी अभिलेखों के अनुसार
• वे जितनी पंक्तियाँ पूछें, उतनी पंक्तियाँ दीजिए

लक्ष्य स्थितियों के आधार पर, निम्नलिखित में से एक प्रतिस्थापन करना उचित है:

• (count + LIMIT 1) = 0 पर NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 पर EXISTS(LIMIT 1)
• count >= N पर (SELECT count(*) FROM (... LIMIT N))

"ग्राम में कितना लटकाना है": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

एक अनुभवहीन डेवलपर ईमानदारी से विश्वास कर सकता है कि अनुरोध निष्पादित होना बंद हो जाएगा। जैसे ही हमें सामने आने वाले पहले विभिन्न मूल्यों में से $1 मिल जाता है.

भविष्य में किसी समय यह एक नए नोड के कारण काम कर सकता है और काम करेगा इंडेक्स स्किप स्कैन, जिसके कार्यान्वयन पर वर्तमान में काम किया जा रहा है, लेकिन अभी तक नहीं।

अभी के लिए पहले सारे रिकार्ड बरामद किये जायेंगे, अद्वितीय हैं, और केवल उन्हीं से अनुरोधित राशि वापस की जाएगी। अगर हम ऐसा कुछ चाहते हैं तो यह विशेष रूप से दुखद है $ 1 = 4, और तालिका में सैकड़ों-हजारों रिकॉर्ड हैं...

व्यर्थ में दुखी न होने के लिए, आइए एक पुनरावर्ती क्वेरी का उपयोग करें PostgreSQL विकी से "DISTINCT गरीबों के लिए है"।:

स्रोत: www.habr.com