ProHoster > блог > адміністраванне > PostgreSQL Antipatterns: CTE x CTE

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);

Аказваецца, само чытанне дадзеных заняло менш за чвэрць усяго часу выканання запыту:

PostgreSQL Antipatterns: CTE x CTE[глядзець на explain.tensor.ru]

Разбіраны па костачках

Уважліва паглядзім на запыт, і збянтэжымся:

  1. Навошта тут WITH RECURSIVE, калі ніякіх рэкурсіўных CTE няма?
  2. Навошта групаваць min/max-значэнні ў асобнай CTE, калі потым яны ўсё роўна прывязваюцца да арыгінальнай выбаркі?
    +25% часу
  3. Навошта выкарыстоўваць у канцы паўторную начытку з папярэдняй CTE праз безумоўны 'SELECT * FROM'?
    +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!
Уласна, а што хацелі атрымаць у выніку? Ага, звычайна менавіта такое пытанне і наведвае дзесьці на 5-й хвіліне разбору «трохпавярховых» запытаў.

Мы хацелі для кожнай унікальнай ключавой пары вывесці 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 Antipatterns: CTE x CTE
[глядзець на explain.tensor.ru]

Паколькі чытанне дадзеных у абодвух варыянтах займае аднолькава прыкладна 4-5ms, то ўвесь наш выйгрыш па часе -32% - гэта ў чыстым выглядзе нагрузка, прыбраная з CPU базы, калі такі запыт выконваецца дастаткова часта.

Увогуле, не варта базу прымушаць «круглае - насіць, квадратнае - катаць».

Крыніца: habr.com

Дадаць каментар