PostgreSQL में सम अनुकूलन

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

यह पहली जगह नहीं है जिसे अनुरोध को धीमा करने की आवश्यकता होती है यदि अनुरोध धीमा हो जाता है, बल्कि अंतिम। नीचे दिए गए विचारों को लागू करने के लिए सार्थक हैं जब कार्यान्वयन योजना (व्याख्या) पहले से ही एकदम सही है और इसमें मच्छर नाक को कलंकित नहीं करेगा, लेकिन मैं थोड़ा और "निचोड़" चाहता हूं।

हम एक परीक्षण तालिका बनाएंगे और उसमें 10 मिलियन रिकॉर्ड लिखेंगे:

create table s ( d date, browser_id int not null, banner_id int not null, views bigint, clicks bigint, primary key(d, browser_id, banner_id) ); insert into s select d, browser_id, banner_id, succ + insucc, succ from ( select d, browser_id, banner_id, (array[0,0,50,500])[ceil(random()*4)] succ, (array[0,0,400,400000])[ceil(random()*4)] insucc from generate_series(now() - interval '99 day', now(), '1 day') d cross join generate_series(0, 999) banner_id cross join generate_series(0, 99) browser_id )_; 

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

तो चलिए शुरू करते हैं। ठंडे कैश के प्रभाव को बाहर करने के लिए हम अनुरोध के बार-बार निष्पादन के दौरान हर समय माप करेंगे।
मैंने ईमानदार बेंचमार्क का संचालन नहीं किया, मैंने इसे कई बार चलाया, औसत लिया और इसे गोल किया। कार कमजोर है, आप तेज हो जाएगा!

विधि 1: माथे

 select sum(clicks) from s; 

9 सेकंड

विधि 2: प्रकार बदलें

हम अपनी तालिका को फिर से बनाते हैं, उसी समय हम टाइप न्यूमेरिक के काउंटर बनाएंगे:

 create table s ( d date, browser_id int not null, banner_id int not null, views numeric, clicks numeric, primary key(d, browser_id, banner_id) ); 

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

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

पहली टिप्पणी: यदि हमारे पास एक अंतर प्रकार के काउंटर के लिए पर्याप्त है (और यह ~ 2 बिलियन तक मान ले सकता है), तो इसके साथ काम करना और भी तेज होगा। सुम फिर लौटता है।

दूसरी टिप्पणी: साधारण अंकगणित संचालन (+, -, *, /) प्रकार के संख्यात्मक कार्य के साथ अधिक धीरे-धीरे बिगिन के साथ। और योग संख्यात्मक के साथ तेज है।

विधि 3: शून्य की गणना न करें

 select sum(clicks) from s where clicks <> 0; 

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

विधि 4: शून्य के साथ शून्य को बदलें

 select sum(nullif(licks, 0)) from s; 

वही 7 सेकंड, लेकिन कई कॉलमों को समेटने पर विधि पिछले वाले से बेहतर काम करती है।

इसका कारण है कि राशि, एक सख्त (सख्त) कुल कार्य के रूप में, आने वाले नल की उपेक्षा करती है।

जब कॉलम में शून्य की एक महत्वपूर्ण संख्या (कम से कम 10%, और अधिमानतः आधी) हो तो विधियाँ 3 और 4 उपयोगी होती हैं।

विधि 5: शून्य को सीधे तालिका में शून्य से बदलें

यह अनुकूलन विधि कुछ हद तक "अनैतिक" है: हम सुझाव देते हैं कि शून्य मान के शब्दार्थों को रौंद दिया जाए। लेकिन यह काम करता है।

 create table s2 (like s including all); insert into s2 select d, browser_id, banner_id, nullif(views, 0), nullif(clicks, 0) from s; 

 select sum(clicks) from s2; 

6 सेकंड जाहिरा तौर पर, इसका कारण इस तथ्य में निहित है कि बिटमैप में दुकानों को स्थगित करता है और इसलिए ट्यूपल का आकार कम हो जाता है।

शायद पाठक यह पूछने के लिए आश्चर्यचकित होगा: हमने नमूना लेते समय शून्य को कॉलम को तुरंत क्यों नहीं फेंक दिया, उदाहरण के लिए, एक आंशिक सूचकांक का उपयोग करके?
और हम समझाएंगे: असली तालिकाओं में कई स्तंभ हैं, और उनमें शून्य स्वतंत्र रूप से स्थित हैं।

दस्तावेज़ लिंक:

1. संख्यात्मक प्रकार
2. सम कुल समारोह
3. उपयोगकर्ता-परिभाषित कुल कार्य, कठोरता