Geekly articles weekly

PostgreSQL Antipatterns: ضرب القاموس على JOIN الثقيلة

نواصل سلسلة من المقالات المكرسة لدراسة الطرق غير المعروفة لتحسين أداء استفسارات PostgreSQL "التي تبدو بسيطة":


لا أعتقد أنني لا أحب الانضمام كثيرا ... :)

ولكن في كثير من الأحيان بدونه ، يكون الطلب أكثر إنتاجية منه معه. لذلك ، سنحاول اليوم التخلص تمامًا من JOIN كثيفة الاستخدام للموارد - بمساعدة قاموس.



بدءًا من PostgreSQL 12 ، يمكن تشغيل بعض المواقف الموضحة أدناه بشكل مختلف بعض الشيء بسبب عدم تجسيد CTE افتراضيًا . يمكن التراجع عن هذا السلوك باستخدام مفتاح 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;


[انظروا شرح.tensor.ru]

اتضح أن 1/3 من الوقت الكلي و 3/4 قراءات من صفحات البيانات تم إجراؤها فقط من أجل البحث عن المؤلف 100 مرة - لكل مهمة معروضة. لكننا نعلم أنه من بين هذه المئات هناك 20 مختلفة فقط - هل من الممكن استخدام هذه المعرفة؟

قاموس هستور


نستخدم نوع 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;


[انظروا شرح.tensor.ru]

استغرق الأمر وقتًا أقل مرتين للحصول على معلومات حول الأشخاص و 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;

لنلقِ نظرة على ما حدث هنا:

  1. أخذنا p كاسم مستعار للسجل الكامل لجدول الأشخاص وقمنا بتجميع مجموعة منهم.
  2. تم إعادة صياغة هذه المجموعة من الإدخالات في مجموعة من سلاسل النص (person [] :: text []) لوضعها في قاموس hstore كصفيف من القيم.
  3. عند استلام السجل المرتبط ، أخرجناه من القاموس حسب المفتاح كسلسلة نصية.
  4. نحتاج إلى تحويل النص إلى قيمة نوع جدول الأشخاص (لكل جدول ، يتم إنشاء نوع الاسم نفسه تلقائيًا).
  5. "نشر" سجل مكتوب في أعمدة باستخدام (...).* .

قاموس جسون


لكن مثل هذه الحيلة ، كما طبقنا أعلاه ، لن تنجح إذا لم يكن هناك نوع جدول مماثل لعمل "إلغاء التثبيت". بالضبط سوف تنشأ نفس الحالة ، وإذا كنا كمصدر بيانات للتسلسل نحاول استخدام صف CTE ، وليس الجدول "الحقيقي" .

في هذه الحالة ، ستساعدنا وظائف العمل مع 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] مرتفعة للغاية ، لذلك من المعقول استخدام هذه الطريقة فقط في بعض الحالات عندما يظهر CTE Scan "الصادق" نفسه أسوأ.

اختبار الأداء


لذلك ، حصلنا على طريقتين لتسلسل البيانات إلى قاموس - hstore / json_object . بالإضافة إلى ذلك ، يمكن أيضًا إنشاء صفيف المفاتيح والقيم بطريقتين ، مع تحويل داخلي أو خارجي إلى نص: array_agg (i :: text) / array_agg (i) :: text [] .

دعونا نتحقق من فعالية أنواع مختلفة من التسلسل باستخدام مثال اصطناعي بحت - نحن نقوم بإجراء تسلسل لعدد مختلف من المفاتيح :

 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 وقتًا أقل . مزيج 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) أكثر فعالية

Source: https://habr.com/ru/post/ar485398/

More articles:


All Articles