🎿 🙎 👨🏿‍⚖️ PostgreSQL Antipatterns: एक दुर्लभ रिकॉर्ड JOIN के मध्य तक पहुंच जाएगा 🤷🏿 🗑️ 👩🏻‍🌾

यदि आप एल्गोरिथ्म का विश्लेषण किए बिना SQL क्वेरी लिखते हैं जिसे उन्हें लागू करना होगा, तो यह आमतौर पर प्रदर्शन के मामले में कुछ भी अच्छा नहीं करता है।

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

अपने आप से, तालिकाओं को जोड़ना न तो हानिकारक है और न ही उपयोगी है - यह सिर्फ एक उपकरण है, लेकिन आपको इसका उपयोग करने में सक्षम होना चाहिए।

ओवरसाइट ग्रुपिंग

सबसे पहले, एक बहुत सरल उदाहरण लेते हैं।

100 प्रविष्टियों का एक "शब्दकोश" है (उदाहरण के लिए, ये रूसी संघ के क्षेत्र हैं):

CREATE TABLE tbl_dict AS SELECT generate_series(0, 100) k; ALTER TABLE tbl_dict ADD PRIMARY KEY(k);

... और इससे जुड़ी प्रति 100K प्रविष्टियों में संबंधित "तथ्यों" की एक तालिका है:

 CREATE TABLE tbl_fact AS SELECT (random() * 100)::integer k , (random() * 1000)::integer v FROM generate_series(1, 100000); CREATE INDEX ON tbl_fact(k);

अब चलो प्रत्येक "क्षेत्र" के लिए मानों की राशि की गणना करने का प्रयास करें।

जैसा सुना जाता है, वैसा ही लिखा जाता है

 SELECT dk , sum(fv) FROM tbl_fact f NATURAL JOIN tbl_dict d GROUP BY 1;

डेटा को पढ़ने में केवल 18% समय लगता था, बाकी की प्रोसेसिंग होती थी:

[स्पष्टीकरण पर देखें ।ensor.ru]

और सभी क्योंकि हैश जॉइन और हैश एग्रीगेट को लिंक टेबल के क्षेत्र द्वारा समूह की हमारी इच्छा के कारण प्रत्येक 100K रिकॉर्ड को संसाधित करना था।

हम सरलता का उपयोग करते हैं

लेकिन इस क्षेत्र का मान कुल तालिका में क्षेत्र के मूल्य के बराबर है! यही है, कोई भी हमें पहले "तथ्यों" को समूहीकृत करने के लिए परेशान करता है , और उसके बाद ही कोई संबंध बनाता है :

 SELECT dk , f.sum FROM ( SELECT k , sum(v) FROM tbl_fact GROUP BY 1 ) f NATURAL JOIN tbl_dict d;

[स्पष्टीकरण पर देखें ।ensor.ru]

बेशक, विधि सार्वभौमिक नहीं है, लेकिन "सामान्य जॉइन" के हमारे मामले के लिए , समय का लाभ अनुरोध के न्यूनतम संशोधन के साथ 2 गुना है - बस "nullified" हैश जॉइन के कारण, जिसे 100% प्रविष्टियों के बजाय केवल 100 प्रविष्टियां प्राप्त हुईं।

असमान स्थितियाँ

अब कार्य को जटिल करते हैं: हमारे पास एक पहचानकर्ता द्वारा जुड़ी 3 टेबल हैं - कुछ एप्लिकेशन डेटा के साथ मुख्य और दो सहायक टेबल, जिसके द्वारा हम फ़िल्टर करेंगे।

एक छोटी लेकिन बहुत महत्वपूर्ण टिप्पणी: भले ही लक्ष्य कार्य के "लागू" ज्ञान के आधार पर, हम पहले से ही जानते हैं कि शर्तों को पहले तालिका पर संतुष्ट किया जाएगा - लगभग हमेशा (निश्चितता के लिए - 3: 4), और दूसरे पर - बहुत कम (1: 8) )।

हम मुख्य और पहले सहायक तालिकाओं से पहचान के साथ 100 पहले रिकॉर्ड आईडी से भी चयन करना चाहते हैं, जिसके लिए सभी तालिकाओं पर स्थितियां संतुष्ट हैं । तालिकाओं में सभी रिकॉर्ड, हमें फिर से 100K पर होना चाहिए।

स्क्रिप्ट जनरेटर

 CREATE TABLE base( id integer PRIMARY KEY , val integer ); INSERT INTO base SELECT id , (random() * 1000)::integer FROM generate_series(1, 100000) id; CREATE TABLE ext1( id integer PRIMARY KEY , conda boolean ); INSERT INTO ext1 SELECT id , (random() * 4)::integer <> 0 -- 3:4 FROM generate_series(1, 100000) id; CREATE TABLE ext2( id integer PRIMARY KEY , condb boolean ); INSERT INTO ext2 SELECT id , (random() * 8)::integer = 0 -- 1:8 FROM generate_series(1, 100000) id;

जैसा सुना जाता है, वैसा ही लिखा जाता है

 SELECT base.* , ext1.* FROM base NATURAL JOIN ext1 NATURAL JOIN ext2 WHERE id % 2 = 0 AND conda AND condb ORDER BY base.id LIMIT 100;

[स्पष्टीकरण पर देखें ।ensor.ru]

शब्दों में नकारात्मक समय

इतने सारे चक्र कुछ नोड्स के माध्यम से चले गए हैं कि कुछ की गोलाई त्रुटियों को भी minuses में संचालित किया गया है। योजनाओं में इसी तरह की कलाकृतियों के बारे में मैं PGConf.Russia पर बात करूंगा ।

200ms और अधिक 2GB डेटा पंप - 100 रिकॉर्ड के लिए बहुत अच्छा नहीं है!

हम सरलता का उपयोग करते हैं

त्वरण प्राप्त करने के लिए हम निम्नलिखित तरीकों का उपयोग करते हैं:

शुरू करने के लिए, हम समझते हैं कि लिंक टेबल के लिए सभी शर्तों की जांच करने के लिए यह हमारे लिए समझ में आता है जब मुख्य तालिका के लिए शर्तें पूरी होती हैं (यहां तक कि आईडी के लिए)।
आउटपुट को आधार द्वारा हल किया जाना चाहिए। और इसके लिए, इस तालिका की प्राथमिक कुंजी हमारे लिए एकदम सही है!
हमें ext2 से डेटा की आवश्यकता नहीं है, और इसका उपयोग केवल स्थिति को सत्यापित करने के लिए किया जाता है। इसका मतलब यह है कि इस तालिका के साथ सभी काम को JOIN से WHERE के हिस्से में सुरक्षित रूप से हटाया जा सकता है। और जाँच के लिए EXISTS का उपयोग करें, अन्यथा क्या होगा अगर ऐसा कोई रिकॉर्ड नहीं है?
हमें ext1 से कम से कम कुछ डेटा पुनर्प्राप्त करने की आवश्यकता है , यदि आधार और ext2 पर शेष चेक सफलतापूर्वक पारित हो । यही है, ext1 के साथ कनेक्शन आधार / ext2 के साथ सभी कार्यों के बाद जाना चाहिए, जिसे LATERAL का उपयोग करके प्राप्त किया जा सकता है।
ताकि क्वेरी प्लानर ने N2 में निहित सत्यापन को NIN, "CASE के तहत छिपाएं" उपखण्ड में बदलने की कोशिश न की हो।

 SELECT base.* , ext1.* FROM base , LATERAL( --       base SELECT * FROM ext1 WHERE id = base.id AND conda --   LIMIT 1 ) ext1 WHERE CASE WHEN base.id % 2 = 0 THEN EXISTS( --        SELECT NULL FROM ext2 WHERE id = base.id AND condb --   LIMIT 1 ) END ORDER BY base.id --     PK,       LIMIT 100;

[स्पष्टीकरण पर देखें ।ensor.ru]

निवेदन, निस्संदेह, अधिक जटिल हो गया है, लेकिन समय में 13 बार जीतना और "लोलुपता" में 350 इसके लायक है!

मैं आपको फिर से याद दिलाता हूं कि सभी विधियों का उपयोग नहीं किया जाता है और हमेशा नहीं, लेकिन जानना अति-उपयोगी नहीं होगा।

यह भी दिलचस्प होगा:

PostgreSQL Antipatterns: एक दुर्लभ रिकॉर्ड JOIN के मध्य तक पहुंच जाएगा

ओवरसाइट ग्रुपिंग

जैसा सुना जाता है, वैसा ही लिखा जाता है

हम सरलता का उपयोग करते हैं

असमान स्थितियाँ

जैसा सुना जाता है, वैसा ही लिखा जाता है

हम सरलता का उपयोग करते हैं

More articles: