🥇PostgreSQL Antipatterns: CTE x CTE

N'ihi usoro ọrụ m, aghaghị m ịnagide ọnọdụ mgbe onye nrụpụta dere arịrịọ ma chee "Isi ihe dị mma, ọ nwere ike ijikwa ihe niile n'onwe ya!«

N'ọnọdụ ụfọdụ (nke sitere na amaghị ike nke nchekwa data, akụkụ site na njikarịcha oge), usoro a na-eduga na ọdịdị nke "Frankensteins".

Mbụ, m ga-enye ọmụmaatụ nke arịrịọ dị otú ahụ:

-- для каждой ключевой пары находим ассоциированные значения полей
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;

Iji nyochaa ịdị mma nke arịrịọ nke ọma, ka anyị mepụta ụfọdụ data data aka ike:

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

Ọ tụgharịrị na ịgụ data ahụ were ihe na-erughị otu ụzọ n'ụzọ anọ nke oge mmezu ajụjụ:

[lee nkọwa.tensor.ru]

Na-ewere ya iche n'otu akụkụ

Ka anyị lebakwuo anya na arịrịọ ahụ wee gbagwojuo anyị anya:

Kedu ihe kpatara ya na RECURSIVE ebe a ma ọ bụrụ na enweghị CTE na-emegharịghachi?
Kedu ihe kpatara otu ọnụọgụ min/max dị na CTE dị iche ma ọ bụrụ na ejikọtara ha na nlele mbụ?
+25% oge
Kedu ihe kpatara iji 'Họrọ * FROM' na-enweghị njedebe na njedebe iji kwugharịa CTE gara aga?
+14% oge

N'okwu a, anyị nwere chi ọma na ahọpụtara Hash Join maka njikọ ahụ, ọ bụghị Nested Loop, n'ihi na mgbe ahụ anyị gaara enweta ọ bụghị naanị otu CTE Scan pass, mana 10K!

ntakịrị gbasara CTE ScanN'ebe a, anyị ga-echeta nke ahụ Nyocha CTE yiri Seq Scan - ya bụ, ọ dịghị indexing, ma ọ bụ naanị a zuru ezu search, nke ga-achọ 10K x 0.3ms = 3000ms maka cycles site cte_max ma ọ bụ 1K x 1.5ms = 1500ms mgbe ị na-atụgharị site na cte_bind!
N'ezie, gịnị ka ị chọrọ inweta n'ihi ya? Ee, na-abụkarị nke a bụ ajụjụ na-abịa n'otu ebe na nkeji 5th nke nyochaa ajụjụ "akụkọ atọ".

Anyị chọrọ iwepụta maka ụzọ igodo pụrụ iche min/max site na otu site key_a.
Ya mere, ka anyị jiri ya mee nke a ọrụ windo:

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

[lee nkọwa.tensor.ru]

Ebe ọ bụ na ịgụ data na nhọrọ abụọ ahụ na-ewe ihe dịka 4-5ms, mgbe ahụ oge anyị niile na-enweta -32% - nke a bụ n'ụdị ya kachasị ewepụrụ ibu na isi CPU, ma ọ bụrụ na a na-emezu arịrịọ dị otú ahụ mgbe mgbe.

N'ozuzu, ịkwesighi ịmanye isi ka ọ "buru nke gburugburu, tụgharịa square ahụ."

isi: www.habr.com

PostgreSQL Antipatterns: CTE x CTE

Na-ewere ya iche n'otu akụkụ

Tinye a comment Отменить ответ