PostgreSQL Antipatterns: CTE x CTE

حسب نوع النشاط ، يتعين على المرء التعامل مع المواقف عندما يكتب المطور طلبًا ويفكر "أن القاعدة ذكية ، يمكنها التعامل مع كل شيء! "

في بعض الحالات (جزئيًا بسبب الجهل بقدرات قاعدة البيانات ، جزئيًا من التحسينات المبكرة) ، يؤدي هذا النهج إلى ظهور "فرانكشتاين".

أولاً ، سأقدم مثالًا على هذا الاستعلام:

--         WITH RECURSIVE cte_bind AS ( SELECT DISTINCT ON (key_a, key_b) key_a a , key_b b , fld1 bind_fld1 , fld2 bind_fld2 FROM tbl ) --  min/max      , cte_max AS ( SELECT a , max(bind_fld1) bind_fld1 , min(bind_fld2) bind_fld2 FROM cte_bind GROUP BY a ) --        min/max- , cte_a_bind AS ( SELECT cte_bind.a , cte_bind.b , cte_max.bind_fld1 , cte_max.bind_fld2 FROM cte_bind INNER JOIN cte_max ON cte_max.a = cte_bind.a ) SELECT * FROM cte_a_bind; 

من أجل التقييم الموضوعي لجودة الطلب ، دعنا ننشئ مجموعة من البيانات التعسفية:

 CREATE TABLE tbl AS SELECT (random() * 1000)::integer key_a , (random() * 1000)::integer key_b , (random() * 10000)::integer fld1 , (random() * 10000)::integer fld2 FROM generate_series(1, 10000); CREATE INDEX ON tbl(key_a, key_b); 

اتضح أن قراءة البيانات نفسها استغرقت أقل من ربع وقت تنفيذ الاستعلام الكلي :

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

تفكيك بواسطة العظام

سننظر عن كثب في الطلب ، وسنكون في حيرة:

1. لماذا مع RECURSIVE هنا ، إذا لم يكن هناك CTEs العودية؟
2. لماذا تجميع قيم min / max في CTE منفصل إذا كانت لا تزال مرتبطة بالعينة الأصلية على أي حال؟
   + 25 ٪ من الوقت
3. لماذا في النهاية استخدام إعادة القراءات من CTE السابق من خلال "SELECT * FROM" غير المشروط؟
   + 14 ٪ من الوقت

في هذه الحالة ، كنا محظوظين جدًا باختيار Hash Join للاتصال ، وليس حلقة Nested Loop ، لأنه عندئذٍ لن نحصل على تصريح CTE Scan واحد ، ولكن 10K!


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

أردنا أن يزيل كل زوج مفاتيح فريد min / max من المجموعة بواسطة key_a .
لذلك سوف نستخدم وظائف النافذة لهذا:

 SELECT DISTINCT ON(key_a, key_b) key_a a , key_b b , max(fld1) OVER(w) bind_fld1 , min(fld2) OVER(w) bind_fld2 FROM tbl WINDOW w AS (PARTITION BY key_a); 

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

نظرًا لأن قراءة البيانات في كلا الإصدارين تستغرق حوالي 4 إلى 5 دقائق بالتساوي ، فإن مكسب وقتنا الكامل البالغ -32٪ هو تحميل خالٍ من وحدة المعالجة المركزية الأساسية ، إذا تم تنفيذ مثل هذا الطلب في كثير من الأحيان.

بشكل عام ، لا يجب عليك إجبار القاعدة على "التآكل المستدير ، مربع لفة".