ProHoster > وبلاگ > اداره > آنتی الگوهای PostgreSQL: CTE x CTE

آنتی الگوهای PostgreSQL: 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);

معلوم می شود که خواندن داده ها کمتر از یک چهارم زمان طول کشید اجرای پرس و جو:

آنتی الگوهای PostgreSQL: CTE x CTE[به توضیح.tensor.ru نگاه کنید]

شکستن استخوان ها

بیایید نگاه دقیق تری به درخواست بیندازیم و متحیر باشیم:

  1. اگر CTEهای بازگشتی وجود ندارد، چرا WITH بازگشتی است؟
  2. چرا مقادیر حداقل/حداکثر را در یک CTE جداگانه گروه بندی کنیم، اگر به هر حال به نمونه اصلی گره بخورند؟
    +25 درصد زمان
  3. چرا از یک «SELECT * FROM» بدون قید و شرط در پایان برای تکرار CTE قبلی استفاده کنید؟
    +14 درصد زمان

در این مورد، ما بسیار خوش شانس بودیم که Hash Join برای اتصال انتخاب شد و نه Nested Loop، زیرا در این صورت نه تنها یک پاس CTE Scan، بلکه 10K دریافت می کردیم!

کمی در مورد CTE Scanدر اینجا باید آن را به خاطر بسپاریم CTE Scan مشابه Seq Scan است - یعنی بدون نمایه سازی، بلکه فقط یک جستجوی کامل است که نیاز دارد 10K x 0.3ms = 3000ms برای چرخه های cte_max یا 1K x 1.5ms = 1500ms هنگام حلقه زدن توسط cte_bind!
در واقع، در نتیجه چه چیزی می خواستید به دست آورید؟ بله، معمولاً این سؤالی است که در دقیقه پنجم تجزیه و تحلیل سؤالات "سه داستان" مطرح می شود.

ما می خواستیم برای هر جفت کلید منحصر به فرد خروجی بگیریم 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);

آنتی الگوهای PostgreSQL: CTE x CTE
[به توضیح.tensor.ru نگاه کنید]

از آنجایی که خواندن داده ها در هر دو گزینه تقریباً 4-5 میلی ثانیه طول می کشد، پس تمام وقت ما افزایش می یابد -32٪ - این در خالص ترین شکل خود است بار از CPU پایه حذف شد، اگر چنین درخواستی اغلب به اندازه کافی اجرا شود.

به طور کلی، شما نباید پایه را مجبور کنید "گردش را حمل کنید، مربع را بچرخانید."

منبع: www.habr.com

اضافه کردن نظر