👼 🤧 🙅🏿 PostgreSQL Antipatterns: भारी JOIN पर शब्दकोश हिट 🌯 👼 🤘🏽

हम "प्रतीत होता है सरल" PostgreSQL प्रश्नों के प्रदर्शन को बेहतर बनाने के लिए अल्पज्ञात तरीकों के अध्ययन के लिए समर्पित लेखों की एक श्रृंखला जारी रखते हैं:

ऐसा मत सोचो कि मुझे जोइन पसंद नहीं है ... :)

लेकिन अक्सर इसके बिना, अनुरोध उसके साथ काफी अधिक उत्पादक है। इसलिए, आज हम एक शब्दकोश की मदद से संसाधन-गहन JOIN - से पूरी तरह छुटकारा पाने की कोशिश करेंगे।

PostgreSQL 12 के साथ शुरू, नीचे वर्णित कुछ स्थितियों को डिफ़ॉल्ट रूप से सीटीई के गैर-भौतिककरण के कारण थोड़ा अलग तरीके से खेला जा सकता है। इस व्यवहार को MATERIALIZED कुंजी का उपयोग करके वापस किया जा सकता है।

एक सीमित शब्दावली पर बहुत सारे "तथ्य"

आइए एक बहुत ही वास्तविक एप्लिकेशन लें - आपको प्रेषकों के साथ आने वाले संदेशों या सक्रिय कार्यों को सूचीबद्ध करना होगा:

 25.01 |  .. |    . 22.01 |  .. |    :   JOIN. 20.01 |  .. |   . 18.01 |  .. |    : JOIN    . 16.01 |  .. |   .

अमूर्त दुनिया में, कार्य लेखकों को समान रूप से हमारे संगठन के सभी कर्मचारियों के बीच वितरित किया जाना चाहिए, लेकिन वास्तव में, कार्य, एक नियम के रूप में, काफी हद तक सीमित लोगों से - "पदानुक्रम" से "पदानुक्रम" या पड़ोसी विभागों से "सहयोगी", डिजाइनर विपणन ...)।

मान लेते हैं कि 1000 लोगों के हमारे संगठन में, केवल 20 लेखक (आमतौर पर इससे भी कम) प्रत्येक विशिष्ट कलाकार के लिए कार्य निर्धारित करते हैं और "पारंपरिक" अनुरोध को तेज करने के लिए इस विषय ज्ञान का उपयोग करते हैं।

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

 --  CREATE TABLE person AS SELECT id , repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name" , '2000-01-01'::date - (random() * 1e4)::integer birth_date FROM generate_series(1, 1000) id; ALTER TABLE person ADD PRIMARY KEY(id); --     CREATE TABLE task AS WITH aid AS ( SELECT id , array_agg((random() * 999)::integer + 1) aids FROM generate_series(1, 1000) id , generate_series(1, 20) GROUP BY 1 ) SELECT * FROM ( SELECT id , '2020-01-01'::date - (random() * 1e3)::integer task_date , (random() * 999)::integer + 1 owner_id FROM generate_series(1, 100000) id ) T , LATERAL( SELECT aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id FROM aid WHERE id = T.owner_id LIMIT 1 ) a; ALTER TABLE task ADD PRIMARY KEY(id); CREATE INDEX ON task(owner_id, task_date); CREATE INDEX ON task(author_id);

हम एक विशिष्ट कलाकार के लिए अंतिम 100 कार्य दिखाते हैं:

 SELECT task.* , person.name FROM task LEFT JOIN person ON person.id = task.author_id WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100;

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

यह पता चलता है कि पूरे समय के 1/3 और डेटा पृष्ठों की 3/4 रीडिंग केवल 100 बार - प्रत्येक प्रदर्शित कार्य के लिए लेखक को खोजने के लिए बनाई गई थीं। लेकिन हम जानते हैं कि इस सौ में से केवल 20 अलग - अलग हैं - क्या इस ज्ञान का उपयोग करना संभव है?

hstore शब्दकोश

हम कुंजी-मूल्य "शब्दकोश" उत्पन्न करने के लिए hstore प्रकार का उपयोग करते हैं :

 CREATE EXTENSION hstore

हमारे लिए लेखक की आईडी और उसके नाम को डिक्शनरी में रखना पर्याप्त है, ताकि बाद में हम इस कुंजी का उपयोग करके निकाल सकें:

 --    WITH T AS ( SELECT * FROM task WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100 ) --      , dict AS ( SELECT hstore( -- hstore(keys::text[], values::text[]) array_agg(id)::text[] , array_agg(name)::text[] ) FROM person WHERE id = ANY(ARRAY( SELECT DISTINCT author_id FROM T )) ) --     SELECT * , (TABLE dict) -> author_id::text -- hstore -> key FROM T;

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

व्यक्तियों के बारे में जानकारी प्राप्त करने में 2 गुना कम समय लगा और 7 बार कम डेटा पढ़ा गया ! "धोखे" के अलावा, इन परिणामों ने भी हमें = ANY(ARRAY(...)) का उपयोग करके एक एकल पास में तालिका से रिकॉर्ड के बड़े पैमाने पर निष्कर्षण प्राप्त करने में मदद की।

तालिका प्रविष्टियाँ: क्रमांकन और डीराइज़ेशन

लेकिन क्या होगा अगर हमें शब्दकोश में एक पाठ क्षेत्र में नहीं, बल्कि पूरे रिकॉर्ड को सहेजने की आवश्यकता है? इस मामले में, PostgreSQL की एक एकल मान के रूप में तालिका लिखने के साथ काम करने की क्षमता हमारी मदद करेगी:

 ... , dict AS ( SELECT hstore( array_agg(id)::text[] , array_agg(p)::text[] --  #1 ) FROM person p WHERE ... ) SELECT * , (((TABLE dict) -> author_id::text)::person).* --  #2 FROM T;

आइए देखें कि यहां क्या हुआ:

हमने व्यक्ति तालिका के पूर्ण रिकॉर्ड के लिए एक उपनाम के रूप में पी लिया और उनसे एक सरणी इकट्ठी की।
प्रविष्टियों की यह सरणी पाठ स्ट्रिंग (व्यक्ति [] :: पाठ []] की एक सरणी में इसे मूल्यों की एक सरणी के रूप में hstore शब्दकोश में रखने के लिए फिर से तैयार किया गया था।
लिंक किए गए रिकॉर्ड को प्राप्त करने पर, हमने इसे टेक्स्ट स्ट्रिंग के रूप में कुंजी द्वारा शब्दकोश से बाहर निकाला ।
हमें पाठ को व्यक्ति तालिका के प्रकार (प्रत्येक तालिका के लिए, उसी नाम का प्रकार स्वचालित रूप से बनाया जाता है) के मूल्य में बदलना होगा ।
" (...).* का उपयोग करके कॉलम में एक टाइप किए गए रिकॉर्ड को" नियोजित " (...).* ।

json शब्दकोश

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

इस मामले में, json के साथ काम करने के लिए कार्य हमारी मदद करेंगे:

 ... , p AS ( --   CTE SELECT * FROM person WHERE ... ) , dict AS ( SELECT json_object( --    json array_agg(id)::text[] , array_agg(row_to_json(p))::text[] --   json    ) FROM p ) SELECT * FROM T , LATERAL( SELECT * FROM json_to_record( ((TABLE dict) ->> author_id::text)::json --     json ) AS j(name text, birth_date date) --     ) j;

यह ध्यान दिया जाना चाहिए कि लक्ष्य संरचना का वर्णन करते समय, हम स्रोत स्ट्रिंग के सभी क्षेत्रों को सूचीबद्ध नहीं कर सकते हैं, लेकिन केवल उन जिनकी हमें वास्तव में आवश्यकता है। यदि हमारे पास "मूल" तालिका है, तो json_populate_record फ़ंक्शन का उपयोग करना बेहतर है।

हमारे पास अभी भी एक बार शब्दकोश की पहुंच है, लेकिन json- [de] क्रमांकन की लागत काफी अधिक है , इसलिए इस विधि का उपयोग केवल कुछ मामलों में करना उचित है जब "ईमानदार" सीटीई स्कैन खुद को बदतर दिखाता है।

परीक्षण प्रदर्शन

इसलिए, हमें डेटा को डिक्शनरी में क्रमबद्ध करने के दो तरीके मिले - hstore / json_object । इसके अलावा, कुंजियों और मूल्यों की सरणियों को दो तरह से भी उत्पन्न किया जा सकता है: पाठ में आंतरिक या बाह्य रूपांतरण के साथ: array_agg (i :: text) / array_agg (i) :: पाठ [] ।

चलो विशुद्ध रूप से सिंथेटिक उदाहरण का उपयोग करके विभिन्न प्रकार के क्रमांकन की प्रभावशीलता की जांच करते हैं - हम विभिन्न प्रकार की कुंजियों को क्रमबद्ध करते हैं :

 WITH dict AS ( SELECT hstore( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, ...) i ) TABLE dict;

मूल्यांकन स्क्रिप्ट: क्रमांकन

 WITH T AS ( SELECT * , ( SELECT regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (\d+\.\d+) ms$', '\1')::real et FROM ( SELECT array_agg(el) ea FROM dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$ explain analyze WITH dict AS ( SELECT hstore( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, $$ || (1 << v) || $$) i ) TABLE dict $$) T(el text) ) T ) et FROM generate_series(0, 19) v , LATERAL generate_series(1, 7) i ORDER BY 1, 2 ) SELECT v , avg(et)::numeric(32,3) FROM T GROUP BY 1 ORDER BY 1;

PostgreSQL 11 पर, 2 ^ 12 कुंजी के शब्दकोश आकार के बारे में, जोसन में क्रमबद्ध होने में कम समय लगता है । Json_object का संयोजन और array_agg(i::text) का "आंतरिक" प्रकार रूपांतरण सबसे कुशल है।

अब चलो प्रत्येक कुंजी के मूल्य को 8 बार पढ़ने का प्रयास करें - क्योंकि यदि आप शब्दकोश तक नहीं पहुंचते हैं, तो इसकी आवश्यकता क्यों है?

मूल्यांकन स्क्रिप्ट: एक शब्दकोश से पढ़ना

 WITH T AS ( SELECT * , ( SELECT regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (\d+\.\d+) ms$', '\1')::real et FROM ( SELECT array_agg(el) ea FROM dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$ explain analyze WITH dict AS ( SELECT json_object( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, $$ || (1 << v) || $$) i ) SELECT (TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text FROM generate_series(1, $$ || (1 << (v + 3)) || $$) i $$) T(el text) ) T ) et FROM generate_series(0, 19) v , LATERAL generate_series(1, 7) i ORDER BY 1, 2 ) SELECT v , avg(et)::numeric(32,3) FROM T GROUP BY 1 ORDER BY 1;

और ... पहले से ही लगभग 2 ^ 6 कीज़ पर, json-डिक्शनरी से पढ़ना कई बार hstore से पढ़ने के लिए खोना शुरू कर देता है , jsonb के लिए वही चीज़ 2 ^ 9 पर होती है।

अंतिम निष्कर्ष:

यदि आपको बार-बार दोहराए जाने वाले रिकॉर्ड के साथ एक JOIN करने की आवश्यकता है - "टेबल मिलान" का उपयोग करना बेहतर है
यदि आपका शब्दकोश अपेक्षित रूप से छोटा है और आप इसे थोड़ा पढ़ेंगे - तो आप json [b] का उपयोग कर सकते हैं
अन्य सभी मामलों में, hstore + array_agg (i :: text) अधिक कुशल होगा

PostgreSQL Antipatterns: भारी JOIN पर शब्दकोश हिट

एक सीमित शब्दावली पर बहुत सारे "तथ्य"

hstore शब्दकोश

तालिका प्रविष्टियाँ: क्रमांकन और डीराइज़ेशन

json शब्दकोश

परीक्षण प्रदर्शन

More articles: