Antipatterns PostgreSQL: JOIN ها و OR های مضر

مراقب عملیاتی باشید که بافر می آورد...
با استفاده از یک پرس و جو کوچک به عنوان مثال، اجازه دهید به برخی از رویکردهای جهانی برای بهینه سازی پرس و جوها در 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;

در مورد نام جدول و فیلدمی توان با نام های "روسی" فیلدها و جداول متفاوت رفتار کرد، اما این یک موضوع سلیقه ای است. از آنجا که اینجا در تنسور هیچ توسعه‌دهنده خارجی وجود ندارد و PostgreSQL به ما اجازه می‌دهد تا نام‌هایی را حتی به صورت هیروگلیف بگذاریم، اگر آنها محصور در نقل قول، سپس ترجیح می دهیم اشیاء را بدون ابهام و واضح نام گذاری کنیم تا مغایرتی وجود نداشته باشد.
بیایید به طرح حاصل نگاه کنیم:

[به توضیح.tensor.ru نگاه کنید]

144 میلی‌ثانیه و تقریباً 53 هزار بافر - یعنی بیش از 400 مگابایت داده! و اگر همه آنها در زمان درخواست ما در حافظه پنهان باشند، خوش شانس خواهیم بود، در غیر این صورت هنگام خواندن از دیسک چندین برابر بیشتر طول می کشد.

الگوریتم از همه مهمتر است!

برای اینکه بتوانید هر درخواستی را به نحوی بهینه کنید، ابتدا باید بفهمید که چه کاری باید انجام دهد.
بیایید توسعه خود ساختار پایگاه داده را در حال حاضر خارج از محدوده این مقاله بگذاریم و توافق کنیم که می توانیم نسبتاً "ارزان" درخواست را دوباره بنویسید و/یا برخی از چیزهایی را که نیاز داریم روی پایه بغلتانیم شاخص.

پس درخواست:
- وجود حداقل یک سند را بررسی می کند
- در شرایطی که ما نیاز داریم و از نوع خاصی
- جایی که نویسنده یا مجری کارمند مورد نیاز ما است

JOIN + LIMIT 1

اغلب برای یک برنامه‌نویس ساده‌تر است که یک پرس‌وجو بنویسد که در آن ابتدا تعداد زیادی جدول به هم متصل شده‌اند و سپس تنها یک رکورد از کل مجموعه باقی می‌ماند. اما ساده تر برای توسعه دهنده به معنای کارآمدتر بودن پایگاه داده نیست.
در مورد ما فقط 3 جدول وجود داشت - و چه تأثیری دارد ...

بیایید ابتدا از ارتباط با جدول "نوع سند" خلاص شویم و در عین حال به پایگاه داده بگوییم که رکورد نوع ما منحصر به فرد است (ما این را می دانیم، اما زمان بندی هنوز ایده ای ندارد):

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

بله، اگر جدول/CTE از یک فیلد واحد از یک رکورد تشکیل شده باشد، در PG حتی می توانید به جای

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

ارزیابی تنبل در پرس و جوهای PostgreSQL

BitmapOr در مقابل UNION

در برخی موارد، Bitmap Heap Scan برای ما هزینه زیادی دارد - به عنوان مثال، در شرایط ما، زمانی که تعداد زیادی رکورد شرایط لازم را برآورده می کنند. گرفتیم چون شرط OR به BitmapOr تبدیل شد- عملیات در طرح
بیایید به مشکل اصلی برگردیم - ما باید یک رکورد مربوطه را پیدا کنیم به هر از شرایط - یعنی نیازی به جستجو برای همه رکوردهای 59K تحت هر دو شرایط نیست. راهی برای حل یک شرط وجود دارد، و فقط زمانی به سراغ دومی بروید که در اولی چیزی پیدا نشد. طراحی زیر به ما کمک خواهد کرد:

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

"خارجی" LIMIT 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از نظر برنامه ریز البته می توانید بنویسید (A, B) = (constA, constB)و (B، A) = (constB، constA). اما هنگام ضبط به ترتیب فیلدهای ایندکس، چنین درخواستی برای اشکال زدایی بعداً راحت تر است.
چه چیزی در این طرح وجود دارد؟

[به توضیح.tensor.ru نگاه کنید]

متأسفانه ما بدشانس بودیم و در بلوک اول UNION چیزی پیدا نشد، بنابراین دومی همچنان اجرا شد. اما با این وجود - فقط 0.037ms و 11 بافر!
ما درخواست را سرعت بخشیده ایم و پمپاژ داده ها را در حافظه کاهش داده ایم چندین هزار بار، با استفاده از تکنیک های نسبتاً ساده - یک نتیجه خوب با کمی کپی پیست. 🙂

منبع: www.habr.com