🥇PostgreSQL Antimodèl: CTE x CTE

Akòz nati travay mwen an, mwen dwe fè fas ak sitiyasyon lè yon pwomotè ekri yon demann epi li panse "Baz la se entelijan, li ka okipe tout bagay poukont li!«

Nan kèk ka (an pati nan inyorans nan kapasite yo nan baz done a, an pati nan optimize twò bonè), apwòch sa a mennen nan aparans nan "Frankensteins".

Premyèman, mwen pral bay yon egzanp yon demann konsa:

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

Pou byen evalye kalite yon demann, ann kreye kèk seri done abitrè:

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

Li sanble ke lekti done yo te pran mwens pase yon ka nan tan an ekzekisyon demann:

[wè nan eksplike.tensor.ru]

Pran li apa moso pa moso

Ann pran yon gade pi pre nan demann lan epi se pou nou sezi:

Poukisa WITH RECURSIVE isit la si pa gen CTE rekursif?
Poukisa gwoup valè min/max nan yon CTE separe si yo mare nan echantiyon orijinal la de tout fason?
+ 25% tan
Poukisa sèvi ak yon enkondisyonèl 'SELECT * FROM' nan fen pou repete CTE anvan an?
+ 14% tan

Nan ka sa a, nou te gen anpil chans ke Hash Join te chwazi pou koneksyon an, epi yo pa Enbridman Loop, paske Lè sa a, nou ta resevwa pa sèlman yon pas CTE Scan, men 10K!

yon ti kras sou CTE ScanIsit la nou dwe sonje sa CTE Scan sanble ak Seq Scan - se sa ki, pa gen Indexing, men se sèlman yon rechèch konplè, ki ta mande 10K x 0.3ms = 3000ms pou sik pa cte_max oswa 1K x 1.5ms = 1500ms lè boukle pa cte_bind!
Aktyèlman, ki sa ou te vle jwenn kòm yon rezilta? Yeah, anjeneral sa a se kesyon an ki vini yon kote nan minit nan 5yèm nan analize "twa-istwa" demann.

Nou te vle pwodiksyon pou chak pè kle inik min/max soti nan gwoup pa key_a.
Se konsa, kite a sèvi ak li pou sa a fonksyon fenèt yo:

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

[wè nan eksplike.tensor.ru]

Depi lekti done nan tou de opsyon pran menm apeprè 4-5ms, Lè sa a, tout tan nou genyen -32% - sa a se nan fòm pi bon kalite li yo chaj retire nan CPU de baz, si yon demann konsa egzekite ase souvan.

An jeneral, ou pa ta dwe fòse baz la "pote yon sèl la wonn, woule yon sèl la kare."

Sous: www.habr.com

PostgreSQL Antimodèl: CTE x CTE

Pran li apa moso pa moso

Add nouvo kòmantè Anile reply