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. Зошто овде е WITH РЕКУРЗИВНО ако нема рекурзивни CTE?
  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!
Всушност, што сакавте да добиете како резултат? Да, обично ова е прашањето што се поставува некаде во 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 антишеми: CTE x CTE
[погледнете на објаснување.tensor.ru]

Бидејќи читањето податоци во двете опции трае приближно 4-5 ms, тогаш целото време добиваме -32% - ова е во најчиста форма оптоварувањето е отстрането од основниот процесор, доколку таквото барање се извршува доволно често.

Во принцип, не треба да ја присилувате основата „да го носи кружниот, да го тркалаат квадратниот“.

Извор: www.habr.com

Додадете коментар