Antipatterns PostgreSQL: المداخلات والعوامل المؤذية

احذر من العمليات التي تجلب المخازن المؤقتة...
باستخدام استعلام صغير كمثال، دعونا نلقي نظرة على بعض الأساليب العامة لتحسين الاستعلامات في 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 لا يوجد مطورين أجانب، ويتيح لنا PostgreSQL إعطاء أسماء حتى باللغة الهيروغليفية، إذا كانوا المغلقة في الاقتباسات، فنحن نفضل تسمية الكائنات بشكل لا لبس فيه وواضح حتى لا تكون هناك تناقضات.
دعونا نلقي نظرة على الخطة الناتجة:

[انظر إلىشرح.tensor.ru]

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

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

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

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

الانضمام + الحد 1

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

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

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

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

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

تقييم كسول في استعلامات PostgreSQL

BitmapOr vs UNION

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

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

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

"إخفاء الظروف الصعبة في ظل القضية"

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

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;

ضبط [إلى] الفهارس

لاحظت العين المدربة أن الشروط المفهرسة في الكتل الفرعية لـ UNION مختلفة قليلاً - وذلك لأن لدينا بالفعل فهارس مناسبة على الطاولة. وإذا لم تكن موجودة، فسيكون من المفيد إنشاءها: المستند (Person3، DocumentType) и المستند (نوع المستند، الموظف).
حول ترتيب الحقول في شروط ROWمن وجهة نظر المخطط، بالطبع، يمكنك الكتابة (أ، ب) = (كونستا، كونستب)و (ب، أ) = (كونستب، كونستا). ولكن عند التسجيل بترتيب الحقول في الفهرس، مثل هذا الطلب هو ببساطة أكثر ملاءمة لتصحيح الأخطاء لاحقًا.
ماذا يوجد في الخطة؟

[انظر إلىشرح.tensor.ru]

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

المصدر: www.habr.com