PostgreSQL Antipatterns: JOINs و ORS الضارة

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

في بعض الإصدارات المستقبلية من PG ، قد يتغير الموقف مع "حكمة" المجدول ، لكن بالنسبة إلى 9.4 / 9.6 ، يبدو الأمر كما هو ، على سبيل المثال هنا.

سأطلب طلبًا حقيقيًا للغاية:

SELECT TRUE FROM "" d INNER JOIN "" doc_ex USING("@") INNER JOIN "" t_doc ON t_doc."@" = d."" WHERE (d."3" = 19091 or d."" = 19091) AND d."$" IS NULL AND d."" IS NOT TRUE AND doc_ex.""[1] IS TRUE AND t_doc."" = '' LIMIT 1; 

لنلقِ نظرة على الخطة الناتجة:

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

144ms و حوالي 53 كيلو بايت من المخازن المؤقتة - أي أكثر من 400 ميجابايت من البيانات! ونحن محظوظون إذا كانوا جميعًا في ذاكرة التخزين المؤقت بحلول وقت طلبنا ، وإلا فسوف يستغرق الأمر عدة مرات عند طرحه من القرص.

الخوارزمية هي الأهم!

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

لذلك الطلب هو:
- يتحقق من وجود بعض المستندات على الأقل
- في حالة نحتاج ونوع معين
- حيث المؤلف أو المنفذ هو الموظف الذي نحتاجه

انضم + الحد 1

في كثير من الأحيان ، يكون من السهل للمطور كتابة استعلام حيث ، في البداية ، يتم ضم عدد كبير من الجداول ، ثم من هذه المجموعة بأكملها لا يوجد سوى سجل واحد. لكن أسهل للمطور - لا يعني أكثر كفاءة لقاعدة البيانات.
في حالتنا ، كان هناك فقط 3 جداول - وما تأثير ...

أولاً ، دعنا نتخلص من الاتصال بجدول "TypeDocument" ، وفي الوقت نفسه ، أخبر قاعدة البيانات أن سجل أنواعنا فريد من نوعه (نحن نعرف ذلك ، لكن المجدول ليس لديه فكرة):

 WITH T AS ( SELECT "@" FROM "" WHERE "" = '' LIMIT 1 ) ... WHERE d."" = (TABLE T) ... 

نعم ، إذا كان الجدول / CTE يتكون من حقل واحد بسجل واحد ، فيمكنك في PG الكتابة على هذا النحو بدلاً من

 d."" = (SELECT "@" FROM T LIMIT 1) 

الحوسبة الكسولة في استفسارات PostgreSQL

صورة نقطية مقابل UNION

في بعض الحالات ، سيكلفنا Bitmap Heap Scan الكثير من المال - على سبيل المثال ، في وضعنا ، عندما تندرج سجلات كافية تحت الشرط المطلوب. لقد حصلنا عليها بسبب الشرط OR الذي تحول إلى عملية BitmapOr في الخطة.
دعنا نعود إلى المهمة الأصلية - تحتاج إلى العثور على سجل يطابق أي من الشروط - أي أنه لا توجد حاجة للبحث في جميع سجلات 59K لكلتا الحالتين. هناك طريقة لحل شرط واحد ، وانتقل إلى الشرط الثاني فقط عندما لا يوجد شيء في الحالة الأولى . هذا التصميم سوف يساعدنا:

 ( SELECT ... LIMIT 1 ) UNION ALL ( SELECT ... LIMIT 1 ) LIMIT 1 

يضمن LIMIT 1 "خارجي" أن ينتهي البحث عند العثور على السجل الأول. وإذا كان موجودًا بالفعل في المجموعة الأولى ، فلن يتم تنفيذ المجموعة الثانية (لم يتم تنفيذها مطلقًا في الخطة).

"الاختباء تحت حالة" ظروف صعبة

هناك لحظة غير مريحة للغاية في الطلب الأولي - التحقق من الحالة باستخدام الجدول المرتبط "ملحق المستند". بغض النظر عن حقيقة الشروط المتبقية في التعبير (على سبيل المثال ، "المحذوفة" ليست صحيحة ) ، يتم تنفيذ هذا الاتصال دائمًا "ويستحق الموارد". سيتم إنفاق أكثر أو أقل منها - يعتمد على حجم هذا الجدول.
ولكن يمكنك تعديل الطلب بحيث يتم البحث عن السجل ذي الصلة فقط عندما يكون ذلك ضروريًا حقًا:

 SELECT ... FROM "" d WHERE ... /*index cond*/ AND CASE WHEN "$" IS NULL AND "" IS NOT TRUE THEN ( SELECT ""[1] IS TRUE FROM "" WHERE "@" = d."@" ) END 

نظرًا لأننا لا نحتاج إلى أي من الحقول للنتيجة من الجدول المرتبط ، يمكننا تحويل JOIN إلى شرط لاستعلام فرعي.
نترك الحقول المفهرسة "خارج الأقواس" في CASE ، نضيف شروطًا بسيطة من السجل إلى الكتلة WHEN - والآن يتم تنفيذ الاستعلام "الثقيل" فقط عند التبديل إلى THEN.

اسمي الأخير هو "الإجمالي"

نجمع الاستعلام الناتج مع كل الميكانيكا الموضحة أعلاه:

 WITH T AS ( SELECT "@" FROM "" WHERE "" = '' ) ( SELECT TRUE FROM "" d WHERE ("3", "") = (19091, (TABLE T)) AND CASE WHEN "$" IS NULL AND "" IS NOT TRUE THEN ( SELECT ""[1] IS TRUE FROM "" WHERE "@" = d."@" ) END LIMIT 1 ) UNION ALL ( SELECT TRUE FROM "" d WHERE ("", "") = ((TABLE T), 19091) AND CASE WHEN "$" IS NULL AND "" IS NOT TRUE THEN ( SELECT ""[1] IS TRUE FROM "" WHERE "@" = d."@" ) END LIMIT 1 ) LIMIT 1; 

تخصيص [تحت] المؤشرات

لاحظت العين المدربة أن الظروف المفهرسة في الوحدات الفرعية للاتحاد مختلفة قليلاً - وهذا لأن لدينا بالفعل الفهارس المناسبة على الطاولة. وإذا لم تكن هناك ، فسيكون الأمر يستحق الإنشاء: المستند (الشخص 3 ، نوع المستند) والمستند (نوع المستند ، الموظف) .

ما هي الخطة؟

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

لسوء الحظ ، لم نكن محظوظين ، ولم يتم العثور على أي شيء في المجموعة الأولى من الاتحاد ، لذلك تم تنفيذ المجموعة الثانية. ولكن على الرغم من ذلك - فقط 0.037ms و 11 المخازن المؤقتة !
سرّعنا الطلب وقللنا "ضخ" البيانات في الذاكرة بعدة آلاف من المرات ، باستخدام طرق بسيطة إلى حد ما - وهي نتيجة جيدة مع لصق نسخ صغير. :)